Create SQL Scripts for Data in a table

On occasion I want to get data from a table and into a script – particularly when I want to create a deployment script for standing data that needs to be migrated between servers. Rather than hand-craft it this last time I’ve created the script below which, when given the name of a table, generates a union’d list of select statements that represent the data. With a slight modification this list can then be selected into a temporary table then joined back to the original in an update/insert to create a script that automatically maintains the data.

There are some caveats to this script though :

You have to delete the first ‘union’ in the resulting SQL.
It doesn’t handle dates correctly and potentially some other data types – I did write this in a hurry!

 1 declare @tablename nvarchar(max)
 2 set @tablename = 'testtable'
 3 
 4 if not exists ( select [TABLE_NAME] from INFORMATION_SCHEMA.TABLES where [TABLE_NAME] = @tablename )
 5 begin
 6     declare @message nvarchar(max)
 7     set @message = 'The table ' + @tablename + ' does not exist'
 8     raiserror ( @message, 18, 0 );
 9     return;
10 end
11 
12 declare @sql nvarchar(max)
13 set @sql = ''
14 
15 declare @newline nvarchar(2)
16 set @newline = char(13) + char(10)
17 
18 select @sql = @sql 
19     + case when ORDINAL_POSITION > 1 then ' + '', '' + ' else '' end 
20     + case when IS_NULLABLE = 'YES' then 'isnull(' else '' end
21     + case when NUMERIC_SCALE is not null or DATA_TYPE = 'bit' then 'convert(nvarchar, ' else '' end
22     + case when CHARACTER_MAXIMUM_LENGTH is not null then ' '''''''' + ' else '' end
23     + '[' + isc.COLUMN_NAME + ']'
24     + case when CHARACTER_MAXIMUM_LENGTH is not null then ' + ''''''''' else '' end
25     + case when NUMERIC_SCALE is not null or DATA_TYPE = 'bit' then ')' else '' end
26     + case when IS_NULLABLE = 'YES' then ', ''null'')' else '' end
27     + ' + '' as [' + COLUMN_NAME + ']' + ''''
28     + @newline
29 from INFORMATION_SCHEMA.COLUMNS isc
30 where
31     isc.TABLE_NAME = @tablename
32 order by
33     ORDINAL_POSITION
34 
35 set @sql = 'select ''union select '' + ' + @sql + ' from [' + @tablename + ']'
36 exec sp_executesql @sql