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