Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Create Insert for Existing Rows Expand / Collapse
Author
Message
Posted Monday, January 14, 2008 5:44 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:01 AM
Points: 734, Visits: 645
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...
Post #442410
Posted Monday, January 21, 2008 12:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 16, 2010 7:24 AM
Points: 1, Visits: 26
problems with identity columns
Post #445623
Posted Monday, January 21, 2008 4:49 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:01 AM
Points: 734, Visits: 645
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...
Post #445681
Posted Monday, January 21, 2008 5:26 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:35 AM
Points: 8, 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
Post #445685
Posted Tuesday, January 22, 2008 2:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 8:02 AM
Points: 330, Visits: 158
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)
Post #445789
Posted Tuesday, January 22, 2008 7:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:31 PM
Points: 441, Visits: 932
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 ?





Post #445867
Posted Tuesday, January 22, 2008 7:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:01 AM
Points: 734, Visits: 645
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...
Post #445874
Posted Tuesday, January 22, 2008 8:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:31 PM
Points: 441, Visits: 932
Your new version is now running just fine.

I ** LOVE ** it!

Thanks a million.
Post #445881
Posted Tuesday, January 22, 2008 8:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:39 AM
Points: 1,157, Visits: 3,259
Nice script. Added support for schema's in case anyone is interested :D

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

Post #445897
Posted Tuesday, January 22, 2008 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 9, 2011 8:10 AM
Points: 8, 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)
Post #446039
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse