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 Tuesday, January 22, 2008 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 09, 2011 8:10 AM
Points: 8, Visits: 271
The before image should have been
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
Post #446043
Posted Tuesday, January 22, 2008 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 09, 2011 8:10 AM
Points: 8, Visits: 271
The before image should have read
WHILE @COUNTER < (SELECT COUNT(*) FROM ##TEMP_TABLE)
Post #446046
Posted Wednesday, March 09, 2011 2:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 09, 2011 2:19 AM
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
Post #1075387
Posted Thursday, October 06, 2011 5:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 12:14 PM
Points: 3, Visits: 246
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
Post #1186422
Posted Thursday, October 06, 2011 7:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 205, Visits: 155
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

Post #1186498
Posted Friday, October 07, 2011 2:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:15 AM
Points: 203, Visits: 628
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;

Post #1186965
Posted Friday, October 07, 2011 2:47 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 01, 2014 4:01 AM
Points: 734, Visits: 645
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...
Post #1186971
Posted Friday, October 07, 2011 2:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:15 AM
Points: 203, Visits: 628
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.....!
Post #1186972
Posted Friday, October 07, 2011 3:17 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 01, 2014 4:01 AM
Points: 734, Visits: 645
Yep, it makes an A S S out of U but fortunately this time not ME

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




Nuke the site from orbit, its the only way to be sure...
Post #1186980
Posted Friday, October 07, 2011 1:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:19 PM
Points: 14, Visits: 582
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


Post #1187354
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse