January 14, 2008 at 5:44 am
Comments posted to this topic are about the item Create Insert for Existing Rows
January 21, 2008 at 12:43 pm
problems with identity columns
January 21, 2008 at 4:49 pm
If it is a problem inserting into an identity field try using...
SET IDENTITY_INSERT TABLENAME ON
Though you will need to turn it back off again.
If thats not the problem let me know.
January 21, 2008 at 5:26 pm
The problem is that when you issue this command:
SELECT @CMD='SELECT * INTO ##TEMP_TABLE FROM '+@TABLE_NAME+' ALTER TABLE ##TEMP_TABLE ADD TAB_ID_MARKER INT IDENTITY(1,1)'
where @TABLE_NAME references a table with an already existing Identity Column, you get this error:
Server: Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table '##TEMP_TABLE'. Only one identity column per table is allowed.
That is because the SELECT INTO actually duplicates the structure of the source table, including Identity columns.
I modified the script to not use a temp table so mine says SELECT * INTO TEMP_TABLE... and ran it only to include the line:
EXEC sp_executeSQL @CMD
In examining the structure of the output table, sure enough the source table's Identity column is an Identity column in the output table.
Jerry Boutot, MCAD MCP, MTA
Jerry Boutot Official
January 22, 2008 at 2:53 am
You can check for the existence of an identity column and then exclude it from the insert list as well as not altering the temp table definition
The insert statement into ##TMPTBL1 also needs to be changed
DECLARE @IDENTCOL as VARCHAR(50)
select @IDENTCOL = isnull(c.[name],'TAB_ID_MARKER ')
FROMsys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
where t.[name] = @TABLE_NAME
and c.is_identity = 1
DECLARE STRUCTURE SCROLL CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME=@TABLE_NAME
AND COLUMN_NAME <> @IDENTCOL
ORDER BY ORDINAL_POSITION ASC
if @IDENTCOL = 'TAB_ID_MARKER'
BEGIN
SELECT @CMD='SELECT * INTO ##TEMP_TABLE FROM '+@TABLE_NAME+'
ALTER TABLE ##TEMP_TABLE ADD TAB_ID_MARKER INT IDENTITY(1,1)'
END
ELSE
BEGIN
SELECT @CMD='SELECT * INTO ##TEMP_TABLE FROM '+@TABLE_NAME+''
END
SELECT @CMD= 'SELECT '+@FLD+' INTO ##TMPTBL1 FROM ##TEMP_TABLE WHERE ' + @IDENTCOL + '='+CONVERT(VARCHAR(10),@COUNTER)
January 22, 2008 at 7:33 am
LINE
SELECT @CMD = 'SELECT * INTO ##TEMP_TABLE FROM
FAILS when column type is money - --@FLD)
"IMPLICIT CONVERSION FROM DATA TYPE MONEY TO VARCHAR IS NOT ALLOWED. USE THE CONVERT FUNCTION TO RUN THIS QUERY."
No success either with this code:
SET @CMD = 'SELECT ' + @fld + ' AS [FldNam]
INTO ##TMPTBL1
FROM ##TEMP_TABLE
WHERE TAB_ID_MARKER = ' + CONVERT(VARCHAR(10), @COUNTER)
EXEC SP_EXECUTESQL @CMD OUTPUT
IF @TYPE = 'money'
SELECT CONVERT(varchar(10), FldNam) FROM ##TMPTBL1
ELSE
SELECT FldNam FROM ##TMPTBL1.
" Invalid column name 'FldNam' "
Suggestions ?
January 22, 2008 at 7:58 am
Have updated the script so that it works for identity and with money data type. It is awaiting approval.
But here is the new one.
Let me know if you have any problems.
--------------------------------------------------
DECLARE @TABLE_NAME varchar(254)
SELECT @TABLE_NAME='TABLE_NAME'
SET NOCOUNT ON
DECLARE @CMD NVARCHAR(4000)
DECLARE @INSCMD VARCHAR(8000)
DECLARE @INSVAL VARCHAR(8000)
DECLARE @fld VARCHAR(255)
DECLARE @TYPE VARCHAR(255)
DECLARE @COUNTER INT
DECLARE @val VARCHAR(8000)
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE_NAME AND DATA_TYPE IN ('text','ntext','image'))>1
BEGIN
PRINT 'Error: Cannot use TEXT,NTEXT or IMAGE Data Types'
RETURN
END
SELECT @INSCMD = 'INSERT INTO ['+@TABLE_NAME+'] ('
DECLARE STRUCTURE SCROLL CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME=@TABLE_NAME
ORDER BY ORDINAL_POSITION ASC
OPEN STRUCTURE
SELECT @CMD='SELECT * INTO ##TEMP_TABLE FROM '+@TABLE_NAME+'
ALTER TABLE ##TEMP_TABLE ADD TAB_ID_MARKER INT'
EXEC sp_executeSQL @CMD
SELECT @COUNTER=1
DECLARE DATA CURSOR FOR
SELECT TAB_ID_MARKER FROM ##TEMP_TABLE
FOR UPDATE OF TAB_ID_MARKER
OPEN DATA
FETCH NEXT FROM DATA INTO @CMD
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE ##TEMP_TABLE SET TAB_ID_MARKER=@COUNTER WHERE CURRENT OF DATA
SELECT @COUNTER=@COUNTER+1
FETCH NEXT FROM DATA INTO @CMD
END
CLOSE DATA
DEALLOCATE DATA
SELECT @COUNTER=1
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
BEGIN
SELECT @INSCMD = 'INSERT INTO ['+@TABLE_NAME+'] ('
SELECT @INSVAL= 'VALUES('
FETCH FIRST FROM STRUCTURE INTO @fld,@TYPE
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD+'['+@FLD+']'
SELECT @CMD= 'SELECT '+@FLD+' INTO ##TMPTBL1 FROM ##TEMP_TABLE WHERE TAB_ID_MARKER='+CONVERT(VARCHAR(10),@COUNTER)
EXEC SP_EXECUTESQL @CMD OUTPUT
SELECT @val=CAST((SELECT TOP 1 * FROM ##TMPTBL1) AS VARCHAR(8000))
SELECT @val=ISNULL(@VAL,'NULL')
EXEC SP_EXECUTESQL N'DROP TABLE ##TMPTBL1'
SELECT @INSVAL=CASE
WHEN @TYPE IN ('int','binary','bit','decimal','float','money','numeric','real','smallint','smallmoney','tinyint') THEN @INSVAL+@VAL
ELSE @INSVAL+''''+@VAL+''''
END
FETCH NEXT FROM STRUCTURE INTO @fld,@TYPE
IF @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD+','
SELECT @INSVAL=@INSVAL+','
END
END
SELECT @INSVAL=REPLACE(@INSVAL,'''NULL''','NULL')
SELECT @INSCMD=@INSCMD+')'
SELECT @INSVAL=@INSVAL+')'
print @INSCMD+@INSVAL
SELECT @COUNTER=@COUNTER+1
END
DROP TABLE ##TEMP_TABLE
CLOSE STRUCTURE
DEALLOCATE STRUCTURE
January 22, 2008 at 8:09 am
Your new version is now running just fine.
I ** LOVE ** it!
Thanks a million.
January 22, 2008 at 8:35 am
Nice script. Added support for schema's in case anyone is interested 😀
DECLARE @TABLE_NAME sysname, @TABLE_SCHEMA sysname
SELECT @TABLE_SCHEMA='UTILITY'
SELECT @TABLE_NAME='SYMMETRICS_JOB_CTRL'
SET NOCOUNT ON
DECLARE @CMD nvarchar(max)
DECLARE @INSCMD nvarchar(max)
DECLARE @INSVAL varchar(max)
DECLARE @fld varchar(max)
DECLARE @TYPE varchar(max)
DECLARE @COUNTER int
DECLARE @val varchar(max)
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA AND DATA_TYPE IN ('text','ntext','image'))>1
BEGIN
PRINT 'Error: Cannot use TEXT,NTEXT or IMAGE Data Types'
RETURN
END
SELECT @INSCMD = 'INSERT INTO ['+@TABLE_SCHEMA+ '.' + @TABLE_NAME + '] ('
DECLARE STRUCTURE SCROLL CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @TABLE_NAME
AND TABLE_SCHEMA = @TABLE_SCHEMA
ORDER BY ORDINAL_POSITION ASC
OPEN STRUCTURE
SELECT @CMD='SELECT * INTO ##TEMP_TABLE FROM '+@TABLE_SCHEMA+ '.' + @TABLE_NAME + '
ALTER TABLE ##TEMP_TABLE ADD TAB_ID_MARKER INT'
EXEC sp_executeSQL @CMD
SELECT @COUNTER=1
DECLARE DATA CURSOR FOR
SELECT TAB_ID_MARKER FROM ##TEMP_TABLE
FOR UPDATE OF TAB_ID_MARKER
OPEN DATA
FETCH NEXT FROM DATA INTO @CMD
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE ##TEMP_TABLE SET TAB_ID_MARKER=@COUNTER WHERE CURRENT OF DATA
SELECT @COUNTER=@COUNTER+1
FETCH NEXT FROM DATA INTO @CMD
END
CLOSE DATA
DEALLOCATE DATA
SELECT @COUNTER=1
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
BEGIN
SELECT @INSCMD = 'INSERT INTO ['+@TABLE_SCHEMA+ '.' + @TABLE_NAME + '] ('
SELECT @INSVAL= 'VALUES('
FETCH FIRST FROM STRUCTURE INTO @fld,@TYPE
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD+'['+@FLD+']'
SELECT @CMD= 'SELECT '+@FLD+' INTO ##TMPTBL1 FROM ##TEMP_TABLE WHERE TAB_ID_MARKER='+CONVERT(VARCHAR(10),@COUNTER)
EXEC SP_EXECUTESQL @CMD OUTPUT
SELECT @val=CAST((SELECT TOP 1 * FROM ##TMPTBL1) AS VARCHAR(8000))
SELECT @val=ISNULL(@VAL,'NULL')
EXEC SP_EXECUTESQL N'DROP TABLE ##TMPTBL1'
SELECT @INSVAL=CASE
WHEN @TYPE IN ('int','binary','bit','decimal','float','money','numeric','real','smallint','smallmoney','tinyint') THEN @INSVAL+@VAL
ELSE @INSVAL+''''+@VAL+''''
END
FETCH NEXT FROM STRUCTURE INTO @fld,@TYPE
IF @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD+','
SELECT @INSVAL=@INSVAL+','
END
END
SELECT @INSVAL=REPLACE(@INSVAL,'''NULL''','')
SELECT @INSCMD=@INSCMD+')'
SELECT @INSVAL=@INSVAL+')'
print @INSCMD+@INSVAL
SELECT @COUNTER=@COUNTER+1
END
DROP TABLE ##TEMP_TABLE
CLOSE STRUCTURE
DEALLOCATE STRUCTURE
Tommy
Follow @sqlscribeJanuary 22, 2008 at 11:49 am
As written, it doesn't create an INSERT statment for the last row in the table.
I changed
WHILE @COUNTER = (SELECT COUNT(*) FROM ##TEMP_TABLE)
to read
WHILE @COUNTER <= (SELECT COUNT(*) FROM ##TEMP_TABLE)
January 22, 2008 at 11:51 am
The before image should have been
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
January 22, 2008 at 11:53 am
The before image should have read
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
March 9, 2011 at 2:21 am
In order to resolve the above mentioned problem I have just made COUNTER=0 at the two places where it is COUNTER=1.
That resolved the issue of data not coming for whole of the table.
Thanks,
Ketan
October 6, 2011 at 5:35 am
Added some changes..fixed schema syntax, used quotename, changed code to use union all with SELECTs
DECLARE @TABLE_NAME sysname, @TABLE_SCHEMA sysname
SELECT @TABLE_SCHEMA='dbo'
SELECT @TABLE_NAME='Media'
SET NOCOUNT ON
DECLARE @CMD nvarchar(max)
DECLARE @INSCMD nvarchar(max)
DECLARE @INSVAL varchar(max)
DECLARE @fld varchar(max)
DECLARE @TYPE varchar(max)
DECLARE @COUNTER int
DECLARE @val varchar(max)
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA AND DATA_TYPE IN ('text','ntext','image'))>1
BEGIN
PRINT 'Error: Cannot use TEXT,NTEXT or IMAGE Data Types'
RETURN
END
SELECT @INSCMD = 'INSERT INTO ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + ' ('
DECLARE STRUCTURE SCROLL CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @TABLE_NAME
AND TABLE_SCHEMA = @TABLE_SCHEMA
ORDER BY ORDINAL_POSITION ASC
OPEN STRUCTURE
SELECT @CMD='SELECT * INTO ##TEMP_TABLE FROM ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + '
ALTER TABLE ##TEMP_TABLE ADD TAB_ID_MARKER INT'
EXEC sp_executeSQL @CMD
SELECT @COUNTER=1
DECLARE DATA CURSOR FOR
SELECT TAB_ID_MARKER FROM ##TEMP_TABLE
FOR UPDATE OF TAB_ID_MARKER
OPEN DATA
FETCH NEXT FROM DATA INTO @CMD
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE ##TEMP_TABLE SET TAB_ID_MARKER=@COUNTER WHERE CURRENT OF DATA
SELECT @COUNTER=@COUNTER+1
FETCH NEXT FROM DATA INTO @CMD
END
CLOSE DATA
DEALLOCATE DATA
SELECT @COUNTER=1
WHILE @COUNTER <= (SELECT COUNT(*) FROM ##TEMP_TABLE)
BEGIN
--SELECT @INSCMD = 'INSERT INTO ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + ' ('
--SELECT @INSVAL= 'VALUES('
IF @COUNTER = 1 SELECT @INSCMD = 'INSERT INTO ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + ' ('
SELECT @INSVAL= 'SELECT '
FETCH FIRST FROM STRUCTURE INTO @fld,@TYPE
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD + quotename(@FLD)
SELECT @CMD= 'SELECT '+@FLD+' INTO ##TMPTBL1 FROM ##TEMP_TABLE WHERE TAB_ID_MARKER='+CONVERT(VARCHAR(10),@COUNTER)
EXEC SP_EXECUTESQL @CMD OUTPUT
SELECT @val=CAST((SELECT TOP 1 * FROM ##TMPTBL1) AS VARCHAR(8000))
SELECT @val=ISNULL(@VAL,'NULL')
EXEC SP_EXECUTESQL N'DROP TABLE ##TMPTBL1'
SELECT @INSVAL=CASE
WHEN @TYPE IN ('int','binary','bit','decimal','float','money','numeric','real','smallint','smallmoney','tinyint') THEN @INSVAL+@VAL
ELSE @INSVAL+''''+@VAL+''''
END
FETCH NEXT FROM STRUCTURE INTO @fld,@TYPE
IF @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD+','
SELECT @INSVAL=@INSVAL+','
END
END
SELECT @INSVAL=REPLACE(@INSVAL,'''NULL''','')
--SELECT @INSCMD=@INSCMD+')'
--SELECT @INSVAL=@INSVAL+')'
IF @COUNTER = 1 SELECT @INSCMD=@INSCMD+')'
IF @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE) SELECT @INSVAL=@INSVAL+' UNION ALL '
--print @INSCMD+@INSVAL
IF @COUNTER = 1 print @INSCMD
print @INSVAL
SELECT @COUNTER=@COUNTER+1
END
DROP TABLE ##TEMP_TABLE
CLOSE STRUCTURE
DEALLOCATE STRUCTURE
October 6, 2011 at 7:59 am
Hello, everyone!!
I have fixed some minor details to make it work fine.
Thank you all for the script, it's really helpful.
DECLARE @TABLE_NAME sysname, @TABLE_SCHEMA sysname
SELECT @TABLE_SCHEMA='dbo'
SELECT @TABLE_NAME='Media'
SET NOCOUNT ON
DECLARE @CMD nvarchar(max)
DECLARE @INSCMD nvarchar(max)
DECLARE @INSVAL varchar(max)
DECLARE @fld varchar(max)
DECLARE @TYPE varchar(max)
DECLARE @COUNTER int
DECLARE @val varchar(max)
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA AND DATA_TYPE IN ('text','ntext','image'))>1
BEGIN
PRINT 'Error: Cannot use TEXT,NTEXT or IMAGE Data Types'
RETURN
END
SELECT @INSCMD = 'INSERT INTO ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + ' ('
DECLARE STRUCTURE SCROLL CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @TABLE_NAME
AND TABLE_SCHEMA = @TABLE_SCHEMA
ORDER BY ORDINAL_POSITION ASC
OPEN STRUCTURE
SELECT @CMD='SELECT * INTO ##TEMP_TABLE FROM ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + '
ALTER TABLE ##TEMP_TABLE ADD TAB_ID_MARKER INT'
EXEC sp_executeSQL @CMD
SELECT @COUNTER=0
DECLARE DATA CURSOR FOR
SELECT TAB_ID_MARKER FROM ##TEMP_TABLE
FOR UPDATE OF TAB_ID_MARKER
OPEN DATA
FETCH NEXT FROM DATA INTO @CMD
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE ##TEMP_TABLE SET TAB_ID_MARKER=@COUNTER WHERE CURRENT OF DATA
SELECT @COUNTER=@COUNTER+1
FETCH NEXT FROM DATA INTO @CMD
END
CLOSE DATA
DEALLOCATE DATA
SELECT @COUNTER=0
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
BEGIN
--SELECT @INSCMD = 'INSERT INTO ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + ' ('
--SELECT @INSVAL= 'VALUES('
IF @COUNTER = 0 SELECT @INSCMD = 'INSERT INTO ' + quotename(@TABLE_SCHEMA) + '.' + quotename(@TABLE_NAME) + ' ('
SELECT @INSVAL= 'SELECT '
FETCH FIRST FROM STRUCTURE INTO @fld,@TYPE
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD + quotename(@FLD)
SELECT @CMD= 'SELECT '+@FLD+' INTO ##TMPTBL1 FROM ##TEMP_TABLE WHERE TAB_ID_MARKER='+CONVERT(VARCHAR(10),@COUNTER)
EXEC SP_EXECUTESQL @CMD OUTPUT
SELECT @val=CAST((SELECT TOP 1 * FROM ##TMPTBL1) AS VARCHAR(8000))
SELECT @val=ISNULL(@VAL,'NULL')
EXEC SP_EXECUTESQL N'DROP TABLE ##TMPTBL1'
SELECT @INSVAL=CASE
WHEN @TYPE IN ('int','binary','bit','decimal','float','money','numeric','real','smallint','smallmoney','tinyint') THEN @INSVAL+@VAL
ELSE @INSVAL+''''+@VAL+''''
END
FETCH NEXT FROM STRUCTURE INTO @fld,@TYPE
IF @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD=@INSCMD+','
SELECT @INSVAL=@INSVAL+','
END
END
SELECT @INSVAL=REPLACE(@INSVAL,'''NULL''','NULL')
--SELECT @INSCMD=@INSCMD+')'
--SELECT @INSVAL=@INSVAL+')'
IF @COUNTER = 0 SELECT @INSCMD=@INSCMD+')'
IF @COUNTER < (SELECT COUNT(*) - 1 FROM ##TEMP_TABLE) SELECT @INSVAL=@INSVAL+' UNION ALL '
--print @INSCMD+@INSVAL
IF @COUNTER = 0 print @INSCMD
print @INSVAL
SELECT @COUNTER=@COUNTER+1
END
DROP TABLE ##TEMP_TABLE
CLOSE STRUCTURE
DEALLOCATE STRUCTURE
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply