October 7, 2011 at 2:37 am
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;
October 7, 2011 at 2:47 am
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.
October 7, 2011 at 2:52 am
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.....!
October 7, 2011 at 3:17 am
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
October 7, 2011 at 1:10 pm
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_SCHEMAsysname= 'dbo'--CHOOSE YOUR SCHEMA
,@TABLE_NAMEsysname= 'ClientRouting'--CHOOSE YOUR TABLE
,@PREDICATEnvarchar(4000)= ''--Source table option, or '' if unused
,@ORDER_BY varchar(100)= ''--Source table option, or '' if unused
SET NOCOUNT ON
DECLARE @COUNTERint
,@CMDnvarchar(4000)
,@INSCMDnvarchar(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
October 8, 2011 at 12:04 pm
It would be possible to do this as a set based operations if you built an sp on the fly and then executed it.
October 10, 2011 at 2:53 am
ron.mcdowell,
I've set @Cmd to NVARCHAR(MAX), the limitations of sp_executesql are only that of server memory or 2GB.
The number of rows is irrelevant as the @Cmd value only has the column list and makeup for extracting the column values. E.g.
SELECT 'INSERT INTO dbo.MyTable (Col1, Col2, Col3) VALUES (' + Col1+ ', ' + Col2 + ', ' + Col3 + ')'
FROM dbo.MyTable
Providing this fits into @Cmd there is no limitation, the actual values are no set in @Cmd.
Hopefully that makes sense.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply