November 14, 2013 at 3:45 am
Comments posted to this topic are about the item Generate row constructors for existing data
November 14, 2013 at 8:42 am
Thank you for sharing this! Perhaps I misunderstand intent, but if you are trying to move small amounts of data, may I suggest my script to generate INSERT statements (with optional DELETE and/or IF EXISTS) from a table? It's much longer, but if one saves snippets like yours in some kind of "toolbox" folder as I do, the power might be worth it. I've also found it quite useful as a developer, when after some fiddling around I need to provide a populated table in a script.
This works best with "Results to Text."
declare @TableName sysname, @script_delete bit, @script_if_not_exists bit, @where_clause_filter nvarchar(max)
set @TableName = '<TableName, sysname, >'
set @script_delete = 0 -- set to 1 to script conditional delete of row if it already exists
set @script_if_not_exists = 1 -- set to 1 to script conditional "if not exists" for insertion
set @where_clause_filter = NULL -- leave this as NULL unless you want to filter the rows you script
--set @where_clause_filter = 'first_name=''John'' and is_active=1' -- sample filter
/***************************************************************************************************
SCRIPT_NAME=script table contents.sql
Desc: Script the contents of most tables as a series of INSERT statements. The output can be saved
as a SQL script and run to (re-)populate the given table. (Best used with "Results to Text")
IF YOUR RESULTS ARE BEING CUT OFF OR SEEM INCOMPLETE IN SSMS, check Tools > Options > Results
to Text > Maximum number of characters displayed..., set it to 8192, and close and reopen
this script.
If scripting a row needs more than 8192 characters, you are out of luck. If you need to
script blob data, you are out of luck.
We assume a table schema/owner of dbo. We assume you provide a proper table name (this will
try to script a view as though it were a table). We probably assume a lot of other things.
Updates:
Date Author Purpose
---------------------------------------------------------------------------------------------------
05/14/2008 ParkBJ Created
02/08/2011 ParkBJ script text fields, but break compat with SQL Server 2000 and earlier by
using varchar(max); some cleanup to fit more in each row
02/24/2011 ParkBJ optionally script conditional delete and/or if not exists and/or where
clause filter; better handle single quotes embedded in string data
11/14/2011 ParkBJ do not include timestamp; shared to SQLServerCentral.com
***************************************************************************************************/
SET NOCOUNT ON
declare @sql1 nvarchar(max), @sql_values nvarchar(max), @pkcolumn nvarchar(max)
-- Set @sql1 to a comma-delimited list of columns in the table.
select @sql1 = coalesce(@sql1+',', '') + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and DATA_TYPE not in ('timestamp') -- do not try to include timestamp columns in insert scripts; might want to leave out blobs too
order by ORDINAL_POSITION
-- Set @sql_values to a chunk of dynamic SQL that will pull data from each table column
-- and appropriately put it in quotes and use COALESCE to deal with NULL values.
-- We TRY to script text/ntext but it might not be a good idea.
select @sql_values = coalesce(@sql_values+',', '')
+ (case
when (DATA_TYPE in ('text')) then '''+coalesce(''''''''+replace(cast('+quotename(COLUMN_NAME)+' as varchar(max)),'''''''','''''''''''')+'''''''',''NULL'')+'''
when (DATA_TYPE in ('ntext')) then '''+coalesce(''''''''+replace(cast('+quotename(COLUMN_NAME)+' as nvarchar(max)),'''''''','''''''''''')+'''''''',''NULL'')+'''
when (DATETIME_PRECISION IS NOT NULL) then '''+coalesce(''''''''+convert(varchar(26),'+quotename(COLUMN_NAME)+',120)+'''''''',''NULL'')+'''
when (CHARACTER_MAXIMUM_LENGTH is not NULL) then '''+coalesce(''''''''+replace('+quotename(COLUMN_NAME)+','''''''','''''''''''')+'''''''',''NULL'')+'''
else '''+coalesce(ltrim(str('+quotename(COLUMN_NAME)+')),''NULL'')+'''
end)
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and COLUMNPROPERTY (object_id(@TableName), COLUMN_NAME, 'IsComputed') = 0
and COLUMN_NAME not in ('tstamp')
order by ORDINAL_POSITION
-- Generate the list of primary key columns and what they must equal. If there is no primary
-- key but there is a unique key, use that instead. This will be for our conditional delete
-- and/or "if not exists" check.
select @pkcolumn = coalesce(@pkcolumn+' and ','')
+ (case
when (c.DATETIME_PRECISION IS NOT NULL) then quotename(cu.COLUMN_NAME)+'=''+coalesce(''''''''+convert(varchar(26),'+quotename(cu.COLUMN_NAME)+',120)+'''''''',''NULL'')+'''
when (c.CHARACTER_MAXIMUM_LENGTH is not NULL) then quotename(cu.COLUMN_NAME)+'=''+coalesce(''''''''+'+quotename(cu.COLUMN_NAME)+'+'''''''',''NULL'')+'''
else quotename(cu.COLUMN_NAME)+'=''+coalesce(ltrim(str('+quotename(cu.COLUMN_NAME)+')),''NULL'')+'''
end)
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
join INFORMATION_SCHEMA.COLUMNS c ON c.COLUMN_NAME = cu.COLUMN_NAME and c.TABLE_NAME = cu.TABLE_NAME and c.TABLE_SCHEMA = cu.TABLE_SCHEMA
where cu.TABLE_NAME = @TableName
and cu.CONSTRAINT_NAME = (
select top 1 tc.CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
where tc.TABLE_NAME = cu.TABLE_NAME and tc.CONSTRAINT_TYPE in ('PRIMARY KEY', 'UNIQUE')
order by tc.CONSTRAINT_TYPE, tc.CONSTRAINT_NAME -- try PRIMARY KEY before UNIQUE
)
and cu.COLUMN_NAME not in ('tstamp')
order by cu.ORDINAL_POSITION
-- If there was no primary or unique key, use every column in the table.
if @pkcolumn is null begin
select @pkcolumn = coalesce(@pkcolumn+' and ','')
+ (case
when (c.DATETIME_PRECISION IS NOT NULL) then quotename(c.COLUMN_NAME)+'=''+coalesce(''''''''+convert(varchar(26),'+quotename(c.COLUMN_NAME)+',120)+'''''''',''NULL'')+'''
when (c.CHARACTER_MAXIMUM_LENGTH is not NULL) then quotename(c.COLUMN_NAME)+'=''+coalesce(''''''''+'+quotename(c.COLUMN_NAME)+'+'''''''',''NULL'')+'''
else quotename(c.COLUMN_NAME)+'=''+coalesce(ltrim(str('+quotename(c.COLUMN_NAME)+')),''NULL'')+'''
end)
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @TableName
and c.COLUMN_NAME not in ('tstamp')
order by c.ORDINAL_POSITION
end
-- Paste all of the above together and run it.
set @sql1 = 'SELECT '''
+(case when @script_delete=1 then 'delete from dbo.' + @TableName + ' where ' + @pkcolumn + ';'+char(13)+char(10) else '' end)
+(case when @script_if_not_exists=1 then 'if not exists (select * from dbo.' + @TableName + ' where ' + @pkcolumn + ')'+char(13)+char(10)+char(9) else '' end)
+'insert into dbo.' + @TableName + ' (' + @sql1 + ')
' + (case when @script_if_not_exists=1 then char(9) else '' end) + 'values (' + @sql_values + ');
''
FROM ' + @TableName + (CASE WHEN @where_clause_filter is not NULL THEN '
WHERE ' + @where_clause_filter ELSE '' END)
print '/* --RUN THE FOLLOWING TO GENERATE THE SCRIPT BELOW THE LINE:
' + @sql1
print '
*/
-- Source Server: ' + @@SERVERNAME + '
-- Source Database: ' + DB_NAME() + '
-- Table: dbo.' + quotename(@TableName) + '
-- Generated Time: ' + CONVERT(varchar(16), GetDate(), 120) + '
-- Generated By: ' + SUSER_SNAME()
exec sp_executesql @sql1
November 14, 2013 at 11:01 am
I found that I had to remove the literal quotename function call from the first select:
[font="Courier New"] select ''' + ''' + quotename(column_name) + ' = '' + /*quotename(*/isnull(cast(' + quotename(Column_name) + ' as varchar(max)), ''NULL'')/*, '''''''')*/ + '', '[/font]
without it certain concatenations restulted in a null string and the row was lost. I am not really sure why this is the case though as other records were coming out exactly as expected.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy