Copy a table with structure and data into a temporary table

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):

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:

-- 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';

Post-Notes

• Added a dummy index to track row numbers.
• Copies structure (column_name, data_type, character_maximum_length)
• Copies data (should be exact copy)