Insert Script Generator

  • Comments posted to this topic are about the item Insert Script Generator

  • I use this one that is fast and do not use cursor. In SSMS I put it in hotkey ctrl+3. When I select the table I press ctrl+3 and I get the command to insert a table. In comments you'll find nullable and defaulted cols:

    CREATE PROCEDURE sp_insert(@table varchar(257),@alias varchar(128) = 'a')

    AS

    -- By Carlo Romagnano

    DECLARE

    @S varchar(8000)

    ,@i varchar(8000)

    ,@comma varchar(10)

    ,@iden INT

    SET @alias = ISNULL(@alias,'a')

    SET @S = 'SELECT ' + char(13) + char(10)

    SET @i = 'INSERT INTO ' + @table + ' (' + char(13) + char(10)

    SET @comma = char(9) + ' '

    SET @iden = 0

    IF LEFT(@TABLE,1) = '#'

    SELECT @S = @S + @comma + @alias + '.' + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

    ,@i = @i + @comma + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

    ,@comma = char(13) + char(10) + char(9) + ','

    ,@iden = @iden + COLUMNPROPERTY (c.id, c.name, 'IsIdentity')

    from tempdb.dbo.syscolumns c

    where c.id = object_id('tempdb.dbo.' + @table)

    AND c.name <> 'timestamp'

    AND c.iscomputed = 0

    order by c.colorder

    ELSE

    SELECT @S = @S + @comma + @alias + '.' + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

    ,@i = @i + @comma + c.name + CASE c.isnullable WHEN 1 THEN ' --NULLABLE' ELSE '' END + CASE WHEN c.cdefault <> 0 THEN ' --DEFAULTED' ELSE '' END

    ,@comma = char(13) + char(10) + char(9) + ','

    ,@iden = @iden + COLUMNPROPERTY (c.id, c.name, 'IsIdentity')

    from syscolumns c

    where c.id = object_id(@table)

    AND c.name <> 'timestamp'

    AND c.iscomputed = 0

    order by c.colorder

    if @iden > 0

    print 'SET IDENTITY_INSERT ' + @table + ' ON'

    print @i + char(13) + char(10) + ')'

    print @S + char(13) + char(10) + ' FROM ' + @table + ' ' + @alias

    if @iden > 0

    print 'SET IDENTITY_INSERT ' + @table + ' OFF'

  • Carlos,

    First of all, great SP there...but let me tell you why mine is quite different from yours.

    1. Here my aim was to script the table s data in insert script format. this way the generated insert script could be run on any other server /database.

    if you look at the output of your SP which is

    SET IDENTITY_INSERT DataLink ON

    INSERT INTO DataLink (

    LinkId

    ,LinkDisplay

    ,LinkAddress

    ,LinkStatus

    )

    SELECT

    a.LinkId

    ,a.LinkDisplay

    ,a.LinkAddress

    ,a.LinkStatus

    FROM DataLink a

    SET IDENTITY_INSERT DataLink OFF

    Now this is an insert script ..ofcourse but why is that the target table and the base table are the same.Hmm....did i run this wrong?;)

    Nope I dint...Anyways my point is

    The SP that i had written would literally give you the insert script like this

    INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of June 25,2008','http://www.google.ca',0)

    INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Test','http://www.google.ca',0)

    INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of October 29th','http://www.google.ca',1)

    By providing it in this manner , the user can make use of it whereever he requires.

    I m not sure if i have put clearly across to you the basic difference b/w the two scripts.:hehe:

  • I couldn't get it to work without a minor tweak.

    I changed the following snippets...

    SELECT @ConctColNme = CASE WHEN @IdentityColumn = @ColumnName

    THEN ''

    ELSE @ConctColNme + @ColumnName + ','

    END

    SELECT @DeclareCol = CASE WHEN @IdentityColumn = @ColumnName

    THEN @DeclareCol

    ELSE @DeclareCol + '@'+@ColumnName +' '+ @DataType +CASE WHEN @CharacterLen IS NOT NULL THEN '('+@CharacterLen+')' ELSE '' END + ','

    END

    SELECT @DeclareCol2 = CASE WHEN @IdentityColumn = @ColumnName

    THEN ''

    ELSE @DeclareCol2 + '@'+@ColumnName + ','

    END

    SELECT @DeclareCol3 = CASE WHEN @IdentityColumn = @ColumnName

    THEN ''

    to

    SELECT @ConctColNme = CASE WHEN @IdentityColumn = @ColumnName

    THEN @ConctColNme

    ELSE @ConctColNme + @ColumnName + ','

    END

    SELECT @DeclareCol = CASE WHEN @IdentityColumn = @ColumnName

    THEN @DeclareCol

    ELSE @DeclareCol + '@'+@ColumnName +' '+ @DataType +CASE WHEN @CharacterLen IS NOT NULL THEN '('+@CharacterLen+')' ELSE '' END + ','

    END

    SELECT @DeclareCol2 = CASE WHEN @IdentityColumn = @ColumnName

    THEN @DeclareCol2

    ELSE @DeclareCol2 + '@'+@ColumnName + ','

    END

    SELECT @DeclareCol3 = CASE WHEN @IdentityColumn = @ColumnName

    THEN @DeclareCol3

    Our tables are designed with an identity field at the end and since the variables are set with '' when it finds the identity field it doesn't work. The variable has to be reassigned to itself.

  • Linson.Daniel (1/12/2009)


    Carlos,

    First of all, great SP there...but let me tell you why mine is quite different from yours.

    1. Here my aim was to script the table s data in insert script format. this way the generated insert script could be run on any other server /database.

    if you look at the output of your SP which is

    SET IDENTITY_INSERT DataLink ON

    INSERT INTO DataLink (

    LinkId

    ,LinkDisplay

    ,LinkAddress

    ,LinkStatus

    )

    SELECT

    a.LinkId

    ,a.LinkDisplay

    ,a.LinkAddress

    ,a.LinkStatus

    FROM DataLink a

    SET IDENTITY_INSERT DataLink OFF

    Now this is an insert script ..ofcourse but why is that the target table and the base table are the same.Hmm....did i run this wrong?;)

    Nope I dint...Anyways my point is

    The SP that i had written would literally give you the insert script like this

    INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of June 25,2008','http://www.google.ca',0)

    INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Test','http://www.google.ca',0)

    INSERT INTO [dbo].[DataLink] (LinkDisplay,LinkAddress,LinkStatus) values ('Whats New as of October 29th','http://www.google.ca',1)

    By providing it in this manner , the user can make use of it whereever he requires.

    I m not sure if i have put clearly across to you the basic difference b/w the two scripts.:hehe:

    I use this script to transfer data between servers or database or similar table.

    e.i.:

    SET IDENTITY_INSERT DataLink ON

    INSERT INTO DataLink (

    LinkId

    ,LinkDisplay

    ,LinkAddress

    ,LinkStatus

    )

    SELECT

    a.LinkId

    ,a.LinkDisplay

    ,a.LinkAddress

    ,a.LinkStatus

    FROM remoteserver.db.dbo.DataLink a

    where a.LinkId >= 1000

    SET IDENTITY_INSERT DataLink OFF

    Or I use it to start insert values:

    INSERT INTO DataLink (

    LinkDisplay

    ,LinkAddress

    ,LinkStatus

    )

    SELECT

    'aaa' --a.LinkDisplay

    ,'bbb' --a.LinkAddress

    ,'ccc' --a.LinkStatus

    UNION ALL

    SELECT

    'aa1' --a.LinkDisplay

    ,'bb1' --a.LinkAddress

    ,'cc1' --a.LinkStatus

    UNION ALL

    SELECT

    'aa2' --a.LinkDisplay

    ,'bb2' --a.LinkAddress

    ,'cc2' --a.LinkStatus

  • Nice script Linson. I had to make a couple tweaks you might want to know about:

    1) The script that is generated does not declare varchar(max) columns correctly (it declares them as varchar(-1), which results in a parsing error when the generated script is executed). I resolved this using the following modification:

    ELSE @DeclareCol + '@'+@ColumnName +' '+ @DataType +CASE WHEN @CharacterLen IS NOT NULL THEN '('+ case when @CharacterLen < 1 then 'max' else @CharacterLen end +')' ELSE '' END + ','

    2) Source data that contains embedded single quotes (e.g., "Murphy's Law") are not handled correctly. I resolved this using the following modification:

    THEN 'CASE WHEN @'+@ColumnName+' IS NOT NULL THEN '+'''''''''+'+'replace(@'+@ColumnName + ', char(39), char(39)+char(39))' + '+'''''''''+' ELSE ''NULL'' END'+'+'',''+'

    Thanks for posting the script - it'll come in handy here.

  • I like it...except for the cursor 😉

    Carlton..

  • First of all thank You John and Keith, u guys have found some serious flaws there....

    Keith, i must say I completely forgot about testing my script for that one particular scenario ...(well to be truthfull i really havent done an extensive test on it):D

    John Brauer (1/12/2009)


    1) The script that is generated does not declare varchar(max) columns correctly (it declares them as varchar(-1), which results in a parsing error when the generated script is executed).

    And John, thanks for the solutions to those flaws. however I did mention that the script would be for execution in sql 2000.

    But anyways now that you have given a work around for that too...it would work in sql 2005 too !!:D

    Carlton, yes I know that these cursors are not very nice.

    i hate cursors too which is why i am working on a similar script that would completely or atleast partially avoid them....

    Anyways thank you again fellas....

    I would update the scripts and post again .......(provided i find the time though):)

  • I've also been doing a lot of scripting to generate procedures, tables, etc... I tweaked my process to accomplish the same task here. I really haven't tested it too much but is another direction. It is SQL2K5 though.

    IF OBJECT_ID('dbo.InsDataGenerator') IS NOT NULL

    DROP PROCEDURE dbo.InsDataGenerator

    GO

    CREATE PROCEDURE dbo.InsDataGenerator( @TableId INT )

    AS

    BEGIN

    IF NOT(EXISTS(SELECT * FROM sys.tables WHERE [object_id] = @TableId AND [type] = 'U'))

    BEGIN

    PRINT 'Table not found'

    RETURN

    END

    DECLARE

    @ColumnList VARCHAR(MAX),

    @ColumnDataVARCHAR(MAX),

    @SqlCmdNVARCHAR(MAX)

    SET @ColumnList = ''

    SET @ColumnData = ''

    SELECT

    @ColumnList = @ColumnList + LOWER(c.NAME) + ',',

    @ColumnData = @ColumnData +

    CASE WHEN typ.NAME IN ('datetime','smalldatetime','char','nchar','varchar','nvarchar') THEN ''''''''' + ' ELSE '' END +

    CASE WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','decimal','numeric','float') THEN 'CAST(' ELSE '' END +

    'RTRIM(' + LOWER(c.NAME) + ')' +

    CASE WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','decimal','numeric','float') THEN ' AS NVARCHAR(MAX))' ELSE '' END +

    CASE WHEN typ.NAME IN ('datetime','smalldatetime','char','nchar','varchar','nvarchar') THEN ' + ''''''''' ELSE '' END +

    ' + '','' + '

    FROM sys.columns c

    INNER JOIN sys.types typ ON typ.user_type_id = c.user_type_id

    WHERE c.[object_id] = @TableId

    AND c.is_identity = 0

    ORDER BY c.column_id

    SET @ColumnList = SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1)

    SET @ColumnData = SUBSTRING(@ColumnData,1,LEN(@ColumnData)-8)

    SET @SqlCmd = 'select ' + @ColumnData + ' from ' + LOWER(OBJECT_NAME(@TableId))

    CREATE TABLE #InsDataGenerator( SqlCmd NVARCHAR(MAX))

    INSERT INTO #InsDataGenerator

    EXEC sp_executesql @SqlCmd

    UPDATE #InsDataGenerator SET SqlCmd = 'INSERT INTO ' + OBJECT_NAME(@TableId) + '(' + @ColumnList + ') VALUES(' + SqlCmd + ');'

    SELECT * FROM #InsDataGenerator

    END

    GO

    BEGIN

    DECLARE @TableId INT

    SET @TableId = OBJECT_ID('tbl')

    EXEC dbo.InsDataGenerator @TableId

    END

    GO

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply