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