Copy a table with structure and data into a temporary table

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 10613

Why?
Hmm... I was writing a stored procedure that will scramble data given a table as a parameter. Because I only want to update a temporary table and not the original (source) table, I needed the following stored procedure (or part of).

What?
This will copy a given table into a temporary table all the while maintaining the structure and data.

Thinking inside of the box
I think everyone suggests the following (or at least the idea of):

copy raw

SELECT *
INTO #MyTempTable
FROM @GivenTable

SELECT *
INTO #MyTempTable
FROM @GivenTable

If @GivenTable is a parameter then the above will simply return an error. Also, if it was this easy, I wouldn't need to post this note on my website.

Thinking outside of the box
Hooray for you non-sheep! This is what I've come up with:

copy raw

-- Drop Stored Procedure if already exists
IF OBJECT_ID ( 'dbo.usp_MakeTableTemp', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.usp_MakeTableTemp;
GO

-- Create Stored Procedure
CREATE PROCEDURE dbo.usp_MakeTableTemp
    @SchemaTableName nvarchar(100) 
AS 
BEGIN
 
	-- Variables used
	DECLARE @ColName varchar(50);
	DECLARE @TableName varchar(50);
	DECLARE @TableDeclaration nvarchar(4000);
	DECLARE @SqlToExecute nvarchar(4000);


	-- Set variable default values
	SET @TableName = SUBSTRING(@SchemaTableName, CHARINDEX('.', @SchemaTableName)+1, LEN(@SchemaTableName));
	SET @SqlToExecute = '';


	-- Declare temporary table to use
	CREATE TABLE #myScrambledTable (dummyIndex INT);

	
	-- Alter temporary table structure to match given table
	SET @TableDeclaration='ALTER TABLE #myScrambledTable ADD ' + STUFF(
		( 
		-- Code to concatenate column names and data types into one string
		SELECT 
			CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ',' + COLUMN_NAME + ' ' + DATA_TYPE
			ELSE ',' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' END
		FROM 
			INFORMATION_SCHEMA.COLUMNS 
		WHERE
			TABLE_NAME = @TableName
		ORDER BY ORDINAL_POSITION  
		FOR XML PATH ('')
		),1,1,''
	) + ';';	
	EXEC(@TableDeclaration);
	
	
	-- Get First Column Name
	SET @ColName=( 
		SELECT 
			COLUMN_NAME
		FROM 
			INFORMATION_SCHEMA.COLUMNS 
		WHERE
			TABLE_NAME = @TableName
		AND 
			ORDINAL_POSITION = 1
	);
	

	-- Generate Select Query to extract data from given table and populate temporary table
	SET @SqlToExecute='SELECT ROW_NUMBER() OVER(ORDER BY ' + @ColName + ') AS dummyIndex, ' + STUFF(
		( 
		-- Code to concatenate column names and data types into one string
		SELECT 
			',' + COLUMN_NAME
		FROM 
			INFORMATION_SCHEMA.COLUMNS 
		WHERE
			TABLE_NAME = @TableName
		ORDER BY ORDINAL_POSITION  
		FOR XML PATH ('')
		),1,1,''
	) + ' FROM ' + @SchemaTableName;	


	-- Copy given table data into temporary table
	INSERT INTO #myScrambledTable 
		EXEC(@SqlToExecute);


	-- Drop dummy column from table declaration
	ALTER TABLE #myScrambledTable DROP COLUMN dummyIndex;

	-- Show me the results
	SELECT * FROM #myScrambledTable;

END	
GO

-- Usage:
--        EXECUTE dbo.usp_MakeTableTemp 'mySchema.myTable';

-- Drop Stored Procedure if already exists
IF OBJECT_ID ( 'dbo.usp_MakeTableTemp', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.usp_MakeTableTemp;
GO
-- Create Stored Procedure
CREATE PROCEDURE dbo.usp_MakeTableTemp
@SchemaTableName nvarchar(100)
AS
BEGIN
-- Variables used
DECLARE @ColName varchar(50);
DECLARE @TableName varchar(50);
DECLARE @TableDeclaration nvarchar(4000);
DECLARE @SqlToExecute nvarchar(4000);
-- Set variable default values
SET @TableName = SUBSTRING(@SchemaTableName, CHARINDEX('.', @SchemaTableName)+1, LEN(@SchemaTableName));
SET @SqlToExecute = '';
-- Declare temporary table to use
CREATE TABLE #myScrambledTable (dummyIndex INT);
-- Alter temporary table structure to match given table
SET @TableDeclaration='ALTER TABLE #myScrambledTable ADD ' + STUFF(
(
-- Code to concatenate column names and data types into one string
SELECT
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ',' + COLUMN_NAME + ' ' + DATA_TYPE
ELSE ',' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' END
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION
FOR XML PATH ('')
),1,1,''
) + ';';
EXEC(@TableDeclaration);
-- Get First Column Name
SET @ColName=(
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @TableName
AND
ORDINAL_POSITION = 1
);
-- Generate Select Query to extract data from given table and populate temporary table
SET @SqlToExecute='SELECT ROW_NUMBER() OVER(ORDER BY ' + @ColName + ') AS dummyIndex, ' + STUFF(
(
-- Code to concatenate column names and data types into one string
SELECT
',' + COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @TableName
ORDER BY ORDINAL_POSITION
FOR XML PATH ('')
),1,1,''
) + ' FROM ' + @SchemaTableName;
-- Copy given table data into temporary table
INSERT INTO #myScrambledTable
EXEC(@SqlToExecute);
-- Drop dummy column from table declaration
ALTER TABLE #myScrambledTable DROP COLUMN dummyIndex;
-- Show me the results
SELECT * FROM #myScrambledTable;
END
GO
-- Usage:
-- EXECUTE dbo.usp_MakeTableTemp 'mySchema.myTable';