September 25, 2007 at 9:33 am
Comments posted to this topic are about the item Generate Insert Statements Easily
April 14, 2008 at 8:07 am
SELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES (' + "
line fails to compile in SQL Server Management Studio
April 14, 2008 at 4:55 pm
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]
May 27, 2008 at 3:30 am
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
May 27, 2008 at 6:55 am
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]
June 5, 2008 at 2:10 pm
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.
June 5, 2008 at 7:04 pm
louis315 (6/5/2008)
Didn't execute for me with eitherSET 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]
June 5, 2008 at 8:04 pm
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.
June 5, 2008 at 8:19 pm
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]
December 29, 2008 at 11:54 am
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
December 29, 2008 at 12:26 pm
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
November 24, 2009 at 9:10 am
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.
J Pratt
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply