Friday, August 01, 2014
   
Text Size
Login

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

  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:

  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(@SchemaTableNameCHARINDEX('.'@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)
Add Comment

Name:

Email:

Website:

Message:


Latest Posts