Generate Insert Statements Easily

  • achigrik

    SSCommitted

    Points: 1812

    Comments posted to this topic are about the item Generate Insert Statements Easily

  • john.j.cunningham

    SSC Rookie

    Points: 45

    SELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES (' + "

    line fails to compile in SQL Server Management Studio

  • RBarryYoung

    SSC Guru

    Points: 143327

    Try executing:

    SET QUOTED_IDENTIFIER ON

    GO

    first.

    Apparently, the author assumed this setting was already on, though it is not normally on and a bad practice in any event, IMHO.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Prasad Bhogadi

    SSCrazy Eights

    Points: 9235

    Even with SET QUOTED_IDENTIFIER ON, it doesnot compile. The query analyzer returns the following error message.

    Server: Msg 207, Level 16, State 3, Procedure sp_DataAsInsCommand, Line 27

    Invalid column name 'SELECT 'INSERT INTO '.

    Server: Msg 207, Level 16, State 1, Procedure sp_DataAsInsCommand, Line 27

    Invalid column name ' VALUES (' + '.

    Prasad Bhogadi
    www.inforaise.com

  • RBarryYoung

    SSC Guru

    Points: 143327

    My bad, I got it backwards. Try:

    SET QUOTED_IDENTIFIER OFF

    instead.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • louis315

    SSC Veteran

    Points: 250

    Didn't execute for me with either

    SET QUOTED_IDENTIFIER OFF

    or

    SET QUOTED_IDENTIFIER ON

    No good reason to use double quotes in this stored proc.

  • RBarryYoung

    SSC Guru

    Points: 143327

    louis315 (6/5/2008)


    Didn't execute for me with either

    SET QUOTED_IDENTIFIER OFF

    or

    SET QUOTED_IDENTIFIER ON

    No good reason to use double quotes in this stored proc.

    "SET QUOTED_IDENTIFIER OFF" works fine for me. What error do you get now?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • louis315

    SSC Veteran

    Points: 250

    Sorry, I moved on and banged out my own Insert-generating code using Information_Schema views instead of sysobjects and syscolumns.

    I needed code to 1) generate conventional Inserts, 2) create and populate a table variable w/ Inserts, or 3) create a resultset by generating one Select stmt per table row, then sticking "UNION" between each Select.

    I think the error I got from this guy's proc was "Invalid column name", or something like that. Might have been due to spaces in some of my table's column names...

    Thanks for offering to help. I shouldn't have complained a/b this guy's code when I didn't need it anyway.

  • RBarryYoung

    SSC Guru

    Points: 143327

    Okey dokey

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • bakk

    SSC Enthusiast

    Points: 128

    thats how it works for me

    alter PROC p_adm_gen_insert

    (@TableList varchar (8000)

    )

    AS

    SET NOCOUNT ON

    DECLARE @position int, @exec_str varchar (2000), @TableName varchar (50)

    DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity tinyint

    DECLARE @ColumnList varchar(8000)

    SET @ColumnList=''

    SELECT @TableList = @TableList + ','

    SELECT @IsIdentity = 0

    SELECT @position = PATINDEX('%,%', @TableList)

    WHILE (@position <> 0)

    BEGIN

    SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)

    SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList),'')

    SELECT @position = PATINDEX('%,%', @TableList)

    SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR SELECT a.name, a.xtype, a.status FROM syscolumns a, sysobjects b WHERE a.id = b.id and b.name = ''' + @TableName + ''''

    EXEC (@exec_str)

    --print @exec_str

    OPEN fetch_cursor

    FETCH fetch_cursor INTO @name, @xtype, @status

    IF (@status & 0x80) <> 0

    BEGIN

    SELECT @IsIdentity = 1

    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'

    SELECT 'GO'

    END

    SET @ColumnList=@ColumnList + @name +','

    --SELECT @exec_str = 'SELECT ''INSERT INTO ' + @TableName + ' VALUES ('' + '

    SET @exec_str = ''

    Select ' -- The table name is: ' + @TableName

    --text or ntext

    IF (@xtype = 35) OR (@xtype = 99)

    SELECT @exec_str = @exec_str + '''"None yet"'''

    ELSE

    --image

    IF (@xtype = 34)

    SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'

    ELSE

    --smalldatetime or datetime

    IF (@xtype = 58) OR (@xtype = 61)

    SELECT @exec_str = @exec_str + 'Coalesce( + '''''''' + CONVERT(varchar,' + @name + ',109)' + ' + ''''''''' + ',''null'')'

    ELSE

    --varchar or char or nvarchar or nchar

    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)

    SELECT @exec_str = @exec_str + 'Coalesce(' + ''''''''' + ' + @name + ' + ''''''''' + ',''null'')'

    ELSE

    --uniqueidentifier

    IF (@xtype = 36)

    SELECT @exec_str = @exec_str + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''''''''' + ',''null'')'

    ELSE

    --binary or varbinary

    IF (@xtype = 173) OR (@xtype = 165)

    SELECT @exec_str = @exec_str + '"' + '0x0' + '"'

    ELSE

    SELECT @exec_str = @exec_str + 'Coalesce(CONVERT(varchar,' + @name + '), ''null'')'

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    FETCH fetch_cursor INTO @name, @xtype, @status

    IF (@@FETCH_STATUS = -1) BREAK

    IF (@status & 0x80) <> 0

    BEGIN

    SELECT @IsIdentity = 1

    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'

    SELECT 'GO'

    END

    SET @ColumnList=@ColumnList + @name +','

    --text or ntext

    IF (@xtype = 35) OR (@xtype = 99)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"None yet"'''

    ELSE

    --image

    IF (@xtype = 34)

    SELECT @exec_str = @exec_str + ' + '','' + ' + '"' + '0xFFFFFFFF' + '"'

    ELSE

    --smalldatetime or datetime

    IF (@xtype = 58) OR (@xtype = 61)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar,' + @name + ',109)' + ' + ''''''''' + ',''null'')'

    ELSE

    --varchar or char or nvarchar or nchar

    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + replace(' + @name + ' COLLATE database_default,'''''''','''''''''''') + ''''''''' + ',''null'')'

    ELSE

    --uniqueidentifier

    IF (@xtype = 36)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''''''''' + ',''null'')'

    ELSE

    --binary or varbinary

    IF (@xtype = 173) OR (@xtype = 165)

    SELECT @exec_str = @exec_str + ' + '','' + ' + '"' + '0x0' + '"'

    ELSE

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce(CONVERT(varchar,' + @name + '), ''null'')'

    END

    CLOSE fetch_cursor

    DEALLOCATE fetch_cursor

    SELECT @exec_str = 'SELECT ''INSERT INTO ' + @TableName +'('+substring(@ColumnList,1,len(@ColumnList)-1) + ')

    VALUES ('' + ' + @exec_str + '+ '')'' FROM ' + @TableName

    EXEC(@exec_str)

    --print (@exec_str)

    SELECT 'GO'

    IF @IsIdentity = 1

    BEGIN

    SELECT @IsIdentity = 0

    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'

    SELECT 'GO'

    END

    END

    --print @ColumnList

  • bakk

    SSC Enthusiast

    Points: 128

    small modification for multiple tables to run at once

    alter PROC p_adm_gen_insert

    (@TableList varchar (8000)

    )

    AS

    SET NOCOUNT ON

    DECLARE @position int, @exec_str varchar (2000), @TableName varchar (50)

    DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity tinyint

    DECLARE @ColumnList varchar(8000)

    SET @ColumnList=''

    SELECT @TableList = @TableList + ','

    SELECT @IsIdentity = 0

    SELECT @position = PATINDEX('%,%', @TableList)

    WHILE (@position <> 0)

    BEGIN

    SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)

    SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList),'')

    SELECT @position = PATINDEX('%,%', @TableList)

    SET @ColumnList = ''

    SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR SELECT a.name, a.xtype, a.status FROM syscolumns a, sysobjects b WHERE a.id = b.id and b.name = ''' + @TableName + ''''

    EXEC (@exec_str)

    --print @exec_str

    OPEN fetch_cursor

    FETCH fetch_cursor INTO @name, @xtype, @status

    IF (@status & 0x80) <> 0

    BEGIN

    SELECT @IsIdentity = 1

    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'

    SELECT 'GO'

    END

    SET @ColumnList=@ColumnList + @name +','

    --SELECT @exec_str = 'SELECT ''INSERT INTO ' + @TableName + ' VALUES ('' + '

    SET @exec_str = ''

    Select ' -- The table name is: ' + @TableName

    --text or ntext

    IF (@xtype = 35) OR (@xtype = 99)

    SELECT @exec_str = @exec_str + '''"None yet"'''

    ELSE

    --image

    IF (@xtype = 34)

    SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'

    ELSE

    --smalldatetime or datetime

    IF (@xtype = 58) OR (@xtype = 61)

    SELECT @exec_str = @exec_str + 'Coalesce( + '''''''' + CONVERT(varchar,' + @name + ',109)' + ' + ''''''''' + ',''null'')'

    ELSE

    --varchar or char or nvarchar or nchar

    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)

    SELECT @exec_str = @exec_str + 'Coalesce(' + ''''''''' + ' + @name + ' + ''''''''' + ',''null'')'

    ELSE

    --uniqueidentifier

    IF (@xtype = 36)

    SELECT @exec_str = @exec_str + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''''''''' + ',''null'')'

    ELSE

    --binary or varbinary

    IF (@xtype = 173) OR (@xtype = 165)

    SELECT @exec_str = @exec_str + '"' + '0x0' + '"'

    ELSE

    SELECT @exec_str = @exec_str + 'Coalesce(CONVERT(varchar,' + @name + '), ''null'')'

    WHILE @@FETCH_STATUS <> -1

    BEGIN

    FETCH fetch_cursor INTO @name, @xtype, @status

    IF (@@FETCH_STATUS = -1) BREAK

    IF (@status & 0x80) <> 0

    BEGIN

    SELECT @IsIdentity = 1

    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'

    SELECT 'GO'

    END

    SET @ColumnList=@ColumnList + @name +','

    --text or ntext

    IF (@xtype = 35) OR (@xtype = 99)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"None yet"'''

    ELSE

    --image

    IF (@xtype = 34)

    SELECT @exec_str = @exec_str + ' + '','' + ' + '"' + '0xFFFFFFFF' + '"'

    ELSE

    --smalldatetime or datetime

    IF (@xtype = 58) OR (@xtype = 61)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar,' + @name + ',109)' + ' + ''''''''' + ',''null'')'

    ELSE

    --varchar or char or nvarchar or nchar

    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + replace(' + @name + ' COLLATE database_default,'''''''','''''''''''') + ''''''''' + ',''null'')'

    ELSE

    --uniqueidentifier

    IF (@xtype = 36)

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce('''''''' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''''''''' + ',''null'')'

    ELSE

    --binary or varbinary

    IF (@xtype = 173) OR (@xtype = 165)

    SELECT @exec_str = @exec_str + ' + '','' + ' + '"' + '0x0' + '"'

    ELSE

    SELECT @exec_str = @exec_str + ' + '',''' + ' + Coalesce(CONVERT(varchar,' + @name + '), ''null'')'

    END

    CLOSE fetch_cursor

    DEALLOCATE fetch_cursor

    SELECT @exec_str = 'SELECT ''INSERT INTO ' + @TableName +'('+substring(@ColumnList,1,len(@ColumnList)-1) + ')

    VALUES ('' + ' + @exec_str + '+ '')'' FROM ' + @TableName

    EXEC(@exec_str)

    --print (@exec_str)

    SELECT 'GO'

    IF @IsIdentity = 1

    BEGIN

    SELECT @IsIdentity = 0

    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'

    SELECT 'GO'

    END

    END

    --print @ColumnList

  • jpratt-797544

    SSC Eights!

    Points: 847

    I believe the problem with the original statement was how SQL Server handles single. vs. double quotes. In your code, SQL Server was getting confused between the single quote starting the data value and the double quote included within the data value.. The SET QUOTED IDENTIFIER setting would only affect that as intended if the double quotes were only around the outside of the whole command string. The solution (doubling up the single quotes) has always worked, but can be annoying in some situations and can make the code hard to read. Sometimes you even need three or more single quotes to achieve the desired results.

Viewing 12 posts - 1 through 12 (of 12 total)

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