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
Thomas Jones-454312
Thomas Jones-454312
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 271
The before image should have been
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
Thomas Jones-454312
Thomas Jones-454312
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 271
The before image should have read
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
ketankalia
ketankalia
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
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
Steve Rajkovich
Steve Rajkovich
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 292
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
CGZ
CGZ
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 325
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


b3yond
b3yond
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 770
You can accomplish the same task using a set based approach which should perform considerably quicker.

It does work with text and ntext data types, but not image. I have no idea what will happen if geography, geometry or hierarchyid data types are used, you have been warned!


DECLARE @SchemaName NVARCHAR(128)
, @TableName NVARCHAR(128);

SELECT @SchemaName = 'dbo'
, @TableName = 'MyTable';

IF ((
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @SchemaName
AND DATA_TYPE IN ('image')
) > 0)
BEGIN
RAISERROR('Error: The table contains an IMAGE data type, this is unsupported', 18, 1);
RETURN;
END

DECLARE @ColumnNames NVARCHAR(MAX)
, @ColumnValues NVARCHAR(MAX)
, @Cmd NVARCHAR(MAX);

SELECT @ColumnNames =
STUFF((
SELECT ', ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @SchemaName
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
),1,1,'');

SELECT @ColumnValues =
STUFF((
SELECT ', '' + COALESCE(' +
CASE WHEN DATA_TYPE IN ('tinyint','smallint','int','real','money','float','bit','decimal','numeric','smallmoney','bigint') THEN '''''' ELSE '''''''''' END +
' + CAST(' + COLUMN_NAME + ' AS NVARCHAR(MAX)) + ' +
CASE WHEN DATA_TYPE IN ('tinyint','smallint','int','real','money','float','bit','decimal','numeric','smallmoney','bigint') THEN '''''' ELSE '''''''''' END +
', ''NULL'') + '''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @SchemaName
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
),1,1,'');

SET @Cmd =
'SELECT
''INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' (' + @ColumnNames + ') VALUES (' + @ColumnValues + ')''
FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);

PRINT 'Column Names:
' + @ColumnNames;
PRINT '
Column Values:
' + @ColumnValues;
PRINT '
Command:
' + @Cmd;

EXEC sp_executesql @Cmd;


EvilPostIT
EvilPostIT
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 648
Hi b3yond,

This script was posted on Jan 14th 2008... 7 months before SQL Server 2008 was released and therefore before those data types existed.



Nuke the site from orbit, its the only way to be sure... w00t
b3yond
b3yond
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 770
This will still work in SQL 2005, not in 2000 as I've used FOR XML.

I should probably check the dates a little better next time though, I only looked at it because it was in yesterdays SQLServerCentral email as the featured script. I assumed only new scripts were featured, and we all know what happens when you assume.....!
EvilPostIT
EvilPostIT
SSC Eights!
SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)SSC Eights! (818 reputation)

Group: General Forum Members
Points: 818 Visits: 648
Yep, it makes an A S S out of U but fortunately this time not ME :-P

Do you see what i did there ;-) A SS-U-ME



Nuke the site from orbit, its the only way to be sure... w00t
ron.mcdowell
ron.mcdowell
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 706
b3yond:

I agree set-based is best, but it doesn't work for large number of columns and/or rows, due to @CMD length limitation.

I would suggest speeding up cursor method by eliminating the row-numbering step as follows:


/* */
/* Create one INSERT statement for all existing table rows. */
/* */

DECLARE @TABLE_SCHEMA sysname = 'dbo' -- CHOOSE YOUR SCHEMA
,@TABLE_NAME sysname = 'ClientRouting' -- CHOOSE YOUR TABLE
,@PREDICATE nvarchar(4000) = '' -- Source table option, or '' if unused
,@ORDER_BY varchar(100) = '' -- Source table option, or '' if unused

SET NOCOUNT ON

DECLARE @COUNTER int
,@CMD nvarchar(4000)
,@INSCMD nvarchar(4000)
,@INSVAL varchar(8000)
,@FLD varchar(8000)
,@TYPE varchar(8000)
,@VAL varchar(8000)

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

-- Build ##TEMP_TABLE of Source table data

SELECT @CMD= N' SELECT *, ROW_NUMBER() OVER(ORDER BY @NULL) AS TAB_ID_MARKER INTO ##TEMP_TABLE '
+N' FROM ' + quotename(@TABLE_SCHEMA) + N'.' + quotename(@TABLE_NAME) + N' '
+ @PREDICATE + N' '
+ @ORDER_BY + N' '

EXEC sp_executeSQL @stmt = @CMD
,@params = N'@NULL CHAR(1)'
,@NULL = NULL

SELECT @COUNTER=1

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

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 += quotename(@FLD)
,@CMD = 'SELECT ' + @FLD + ' INTO ##TMPTBL FROM ##TEMP_TABLE WHERE TAB_ID_MARKER=' + CONVERT(VARCHAR(10),@COUNTER)

EXEC SP_EXECUTESQL @CMD OUTPUT

SELECT @VAL = ISNULL(CAST((SELECT TOP 1 * FROM ##TMPTBL) AS VARCHAR(8000)), 'NULL')

EXEC SP_EXECUTESQL N'DROP TABLE ##TMPTBL'

SELECT @INSVAL= CASE
WHEN @TYPE IN ('int','binary','bit','decimal','float','money','numeric','real','smallint','smallmoney','tinyint') THEN @INSVAL + @VAL
ELSE @INSVAL + '''' + @VAL + ''''
END -- CASE

FETCH NEXT FROM STRUCTURE INTO @FLD,@TYPE

IF @@FETCH_STATUS=0
BEGIN
SELECT @INSCMD += ','
SELECT @INSVAL += ','
END
END

SELECT @INSVAL = REPLACE(@INSVAL,'''NULL''','')

IF @COUNTER = 1
SELECT @INSCMD += ')'

IF @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
SELECT @INSVAL += ' UNION ALL '

IF @COUNTER = 1
print @INSCMD

print @INSVAL

SELECT @COUNTER += 1

END -- WHILE



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