SQL SMO to script INSERT with row constructors

Question

I have written the following PowerShell code to export data from a table as INSERT statements:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$srv = new-object Microsoft.SqlServer.Management.SMO.Server
$db = $srv.Databases["MYTESTDB"]

$scripter = new-object Microsoft.SqlServer.Management.SMO.Scripter $srv
$scripter.Options.FileName = "C:\tmp\data.sql"
$scripter.Options.ToFileOnly = $TRUE
$scripter.Options.ScriptSchema  = $FALSE
$scripter.Options.ScriptData  = $TRUE
$scripter.EnumScript($db.tables['MYTABLE'])

This will export in the following form:

INSERT [dbo].[MYTABLE] ([MYCOLUMN1], [MYCOLUMN2], [MYCOLUMN3]) VALUES ('something1', 'something1', 'something1')
INSERT [dbo].[MYTABLE] ([MYCOLUMN1], [MYCOLUMN2], [MYCOLUMN3]) VALUES ('something2', 'something2', 'something2')
INSERT [dbo].[MYTABLE] ([MYCOLUMN1], [MYCOLUMN2], [MYCOLUMN3]) VALUES ('something3', 'something3', 'something3')

But what I am looking for is the following form, which I believe is called "row constructors":

INSERT [dbo].[MYTABLE] ([MYCOLUMN1], [MYCOLUMN2], [MYCOLUMN3]) VALUES
('something1', 'something1', 'something1'),
('something2', 'something2', 'something2'),
('something3', 'something3', 'something3')

Is it possible to get SMO to generate this form?

This syntax is absolutely supported started in 2008 http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx — Eli Ekstein, Mar 11 '13 at 16:58
@Eli Ekstein: I know it is, but the question is how do I get SMO to generate scripts using this syntax. Thanks! — Allanrbo, Mar 11 '13 at 16:59
sorry for that... now i see i didn't understand your question well... — Eli Ekstein, Mar 11 '13 at 17:01

score 0 · Accepted Answer · answered Mar 12 '13 at 00:50

0

I don't think so. SMO supports SQL versions going back to before that syntax was supported. Aside from making smaller DML scripts, they have very little to gain by adding that functionality to SMO and it costs them maintainability in the code base.

answered Mar 12 '13 at 00:50

Ben Thul

31,080
4
45
68

SQL SMO to script INSERT with row constructors

1 Answers1