Create Insert for Existing Rows

  • 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:

  • problems with identity columns

  • 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:

  • 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

  • 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)

  • 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 ?

  • 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:

  • Your new version is now running just fine.

    I ** LOVE ** it!

    Thanks a million.

  • 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

  • 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)

  • The before image should have been

    WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)

  • The before image should have read

    WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)

  • 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

  • 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

  • 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