Search code examples
sql-serversmo

SQL SMO to script INSERT with row constructors

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?

Solution

  • 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.