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