Create Insert for Existing Rows

,

To select a the table to run this script against change the TABLE NAME variable.

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

Rate

3.75 (4)

Share

Share

Rate

3.75 (4)