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

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;

/*									*/
/*	 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