Script table contents as INSERT statements (SQL Server)

SQL Server Management Studio has nice ability to generate SQL Script for database schema, but has nothing for data. So if I want to preserve the entire table (schema and data) as SQL Script, in addition to generating “CREATE…” script, I’d need a set of INSERT statements to generate the static data in a table.
The best solution I’ve seen to date can be found as a comment to some other solution. The script goes like this:

declare @table_name nvarchar(100)
declare @columns    nvarchar(max)
declare @values     nvarchar(max)
declare @identity   bit
declare @sql        nvarchar(max)

set @table_name = 'MyTable'
set @columns    =''
set @values     =''
set @identity   = 0

select
@identity = @identity | columnproperty(object_id(@table_name), column_name, 'IsIdentity'),
@columns  = @columns + ',' + '['+column_name+']',
@values   = @values + '+'',''+isnull(master.dbo.fn_varbintohexstr(cast(['+column_name+'] 
as varbinary(max))),''NULL'')'
from
information_schema.columns
where
table_name = @table_name and data_type != 'timestamp'

set @sql = 'select ''insert into [' + @table_name + '] (' + substring(@columns,2,len(@columns)) + ') 
values (''+' + substring(@values,6,len(@values)) + '+'')'' from ' + @table_name
if @identity=1 print 'set identity_insert [' + @table_name + '] on'
exec sp_executesql @sql
if @identity=1 print 'set identity_insert [' + @table_name + '] off'

Thanks to unknown hero who created it!

Advertisements
Script table contents as INSERT statements (SQL Server)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s