SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create Insert for Existing Rows


Create Insert for Existing Rows

Author
Message
EvilPostIT
EvilPostIT
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 648
Comments posted to this topic are about the item Create Insert for Existing Rows



Nuke the site from orbit, its the only way to be sure... w00t
Jeff-319055
Jeff-319055
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 26
problems with identity columns
EvilPostIT
EvilPostIT
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 648
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.



Nuke the site from orbit, its the only way to be sure... w00t
Jerry_Boutot
Jerry_Boutot
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 133
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
Owner, AppDataWorks, LLC
AppDataWorks, LLC
Jerry Boutot Music
Fiacre Lenehan
Fiacre Lenehan
SSC-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 Visits: 160
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 ')
FROM sys.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)
J-440512
J-440512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1711 Visits: 949
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 ?
EvilPostIT
EvilPostIT
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 648
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



Nuke the site from orbit, its the only way to be sure... w00t
J-440512
J-440512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1711 Visits: 949
Your new version is now running just fine.

I ** LOVE ** it!

Thanks a million.
Tommy Bollhofer
Tommy Bollhofer
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4022 Visits: 3359
Nice script. Added support for schema's in case anyone is interested BigGrin


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 @sqlscribe
Thomas Jones-454312
Thomas Jones-454312
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 271
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search