Copy a table with structure and data into a temporary table @ Joel Lipman .Com

Latest:

Copy a table with structure and data into a temporary table

Last Updated on Friday, 03 August 2012

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)

Add Comment

JComments

Copy a table with structure and data into a temporary table

Main Menu

Latest Posts

Joes Revolver Map (JRM)

Joes Word Cloud

Recently Updated

Most Popular