Technical Article

Generate row constructors for existing data

,

I built this script for when I needed to move small data sets around, typically between servers. The script uses the tables columns to generate select statements of the actual data in the table.

1) ctrl+shift+m

2) Enter databse table, table name (note, this assumes DBO schema) and optional top percent

3) Execute

NOTE: Dont use this for large data sets. As with any statement, doing a union on large data sets performs horribly.

declare @String varchar(max) = 
(
    select ''' + ''' + quotename(column_name) + ' = '' + quotename(isnull(cast(' + quotename(Column_name) + ' as varchar(255)), ''NULL''), '''''''') + '', '
    from <DatabaseName, sysname, >.information_schema.Columns
    where table_name = '<TableName, sysname, >'
    for xml path ('')
)    
select @String =  'select top <TopPercent, int, 100> percent ''union all select ' + left(@String, len(@String) -5)  + ' from <DatabaseName, sysname, >.dbo.<TableName, sysname, >'
exec (@String)

Rate

4 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (3)

You rated this post out of 5. Change rating