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):
copyraw
SELECT *
INTO #MyTempTable
FROM @GivenTable
  1.  SELECT * 
  2.  INTO #MyTempTable 
  3.  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:
copyraw
-- 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';
  1.  -- Drop Stored Procedure if already exists 
  2.  IF OBJECT_ID ( 'dbo.usp_MakeTableTemp', 'P' ) IS NOT NULL 
  3.      DROP PROCEDURE dbo.usp_MakeTableTemp; 
  4.  GO 
  5.   
  6.  -- Create Stored Procedure 
  7.  CREATE PROCEDURE dbo.usp_MakeTableTemp 
  8.      @SchemaTableName nvarchar(100) 
  9.  AS 
  10.  BEGIN 
  11.   
  12.      -- Variables used 
  13.      DECLARE @ColName varchar(50)
  14.      DECLARE @TableName varchar(50)
  15.      DECLARE @TableDeclaration nvarchar(4000)
  16.      DECLARE @SqlToExecute nvarchar(4000)
  17.   
  18.   
  19.      -- Set variable default values 
  20.      SET @TableName = SUBSTRING(@SchemaTableName, CHARINDEX('.', @SchemaTableName)+1, LEN(@SchemaTableName))
  21.      SET @SqlToExecute = ''
  22.   
  23.   
  24.      -- Declare temporary table to use 
  25.      CREATE TABLE #myScrambledTable (dummyIndex INT)
  26.   
  27.   
  28.      -- Alter temporary table structure to match given table 
  29.      SET @TableDeclaration='ALTER TABLE #myScrambledTable ADD ' + STUFF( 
  30.          ( 
  31.          -- Code to concatenate column names and data types into one string 
  32.          SELECT 
  33.              CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ',' + COLUMN_NAME + ' ' + DATA_TYPE 
  34.              ELSE ',' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')' END 
  35.          FROM 
  36.              INFORMATION_SCHEMA.COLUMNS 
  37.          WHERE 
  38.              TABLE_NAME = @TableName 
  39.          ORDER BY ORDINAL_POSITION 
  40.          FOR XML PATH ('') 
  41.          ),1,1,'' 
  42.      ) + ';'
  43.      EXEC(@TableDeclaration)
  44.   
  45.   
  46.      -- Get First Column Name 
  47.      SET @ColName=( 
  48.          SELECT 
  49.              COLUMN_NAME 
  50.          FROM 
  51.              INFORMATION_SCHEMA.COLUMNS 
  52.          WHERE 
  53.              TABLE_NAME = @TableName 
  54.          AND 
  55.              ORDINAL_POSITION = 1 
  56.      )
  57.   
  58.   
  59.      -- Generate Select Query to extract data from given table and populate temporary table 
  60.      SET @SqlToExecute='SELECT ROW_NUMBER() OVER(ORDER BY ' + @ColName + ') AS dummyIndex, ' + STUFF( 
  61.          ( 
  62.          -- Code to concatenate column names and data types into one string 
  63.          SELECT 
  64.              ',' + COLUMN_NAME 
  65.          FROM 
  66.              INFORMATION_SCHEMA.COLUMNS 
  67.          WHERE 
  68.              TABLE_NAME = @TableName 
  69.          ORDER BY ORDINAL_POSITION 
  70.          FOR XML PATH ('') 
  71.          ),1,1,'' 
  72.      ) + ' FROM ' + @SchemaTableName; 
  73.   
  74.   
  75.      -- Copy given table data into temporary table 
  76.      INSERT INTO #myScrambledTable 
  77.          EXEC(@SqlToExecute)
  78.   
  79.   
  80.      -- Drop dummy column from table declaration 
  81.      ALTER TABLE #myScrambledTable DROP COLUMN dummyIndex; 
  82.   
  83.      -- Show me the results 
  84.      SELECT * FROM #myScrambledTable; 
  85.   
  86.  END 
  87.  GO 
  88.   
  89.  -- Usage: 
  90.  --        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)
Category: Transact-SQL :: Article: 424

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.