Generate row constructors for existing data

  • Comments posted to this topic are about the item Generate row constructors for existing data

    Executive Junior Cowboy Developer, Esq.[/url]

  • 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

  • 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