Cloning a table definition to a table variable in SQL Server

Question

Is there a way to clone the table definition from an existing table and recreate as a table variable?

DECLARE @TempTable1 TABLE (ID INT, Description VARCHAR(256))

I need to recreate a set of tables with same number of columns and definitions without repeating the DECLARE TABLE statement.

This process is available on MySQL as below.

CREATE TABLE TempTable1 LIKE TempTableMain;

Is it possible to do this is Microsoft SQL Server?

Please note that the actual scenario contains more that 60 columns in the @TempTable and need to create more than 10 instances from the original table.

I am not talking about data insertion or SELECT ion from another table as below. I need to create the table definition.

DECLARE @TempTable TABLE(ID INT, Description VARCHAR(100))

INSERT INTO @TempTable 
VALUES (1, 'Test1'), (1, 'Test1');

SELECT *
INTO @TempTable2
FROM @TempTable1

SELECT * FROM @TempTable2

`Select * into new_table from old_table`? – Ilyes Dec 14 '18 at 08:57 — Ilyes, Dec 14 '18 at 08:57
@Sami, I need to create the `new_table` dynamcally. – Harsha W Dec 14 '18 at 08:58 — Harsha W, Dec 14 '18 at 08:58

score 7 · Accepted Answer · answered Dec 14 '18 at 09:05

Create a user defined type with the columns of your table, lets say like that:

CREATE TYPE MyTableType AS TABLE (ID INT, Description VARCHAR(256));

And then declare your table variables using this type:

DECLARE @Table1 MyTableType;
DECLARE @Table2 MyTableType;
DECLARE @Table3 MyTableType;

score 0 · Answer 2 · answered Dec 14 '18 at 09:04

SQL Server management studio gives you the option to create a sql script to create an already existing table.

Right click your table -> script table as -> CREATE To -> New Query Editor window

This way you dont have to write out the whole query every single time.

You could even create a stored procedure which takes as argument the name of your to be created table and run this from a while loop.

score 0 · Answer 3 · answered Dec 14 '18 at 09:08

You can perform the following command:

SELECT * INTO #MyTable_tmp FROM MyTable

Then modify your MyTable, and copy your data back in. Other approaches I've seen is to create a new table calling it Mytable_Tmp (Not a temp table), which will be your new table.

Then copy your data doing any migrations you need. Then you will drop the original table and do a rename on Mytable.

When you run SELECT * INTO #MyTable FROM MyTable, SQL Server creates a new temporary table called #MyTable that matches each column and data type from your select clause. In this case we are selecting * so it will match MyTable. This only creates the columns it doesn't copy defaults, constraints indexes or anything else.

score 0 · Answer 4 · answered Dec 14 '18 at 09:09

If you are using table variables, it means that you don't want to use them in long period of time, as they will be "forgotten" after every script completion.

So, easiest in my opinion is to use such construct:

IF OBJECT_ID('tempdb.dbo.#tmpTable', 'U') IS NOT NULL 
  DROP TABLE #tmpTable;
SELECT * INTO #tmpTable FROM MyPrimaryTable

It creates temporary table exactly like yours, if you want empty table, you can just use:

SELECT * INTO #tmpTable FROM MyPrimaryTable WHERE 1 = 0

Then, temporary table will have exact same schema as your primary table.

You can apply as many times as you need (create as many temporary tables as you need).

You could use regular tables instead of temporary tables as well.

score -1 · Answer 5 · answered Dec 14 '18 at 09:17

If you want to re-create table after dropping the existing table then you can use the below query.

/*
    Create brands table 
*/

-- Old block of code
IF  EXISTS (SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[TOY].[BRANDS]') AND type in (N'U'))
DROP TABLE [TOY].[BRANDS]
GO

-- New block of code
DROP TABLE IF EXISTS [TOY].[BRANDS]
GO

-- Add new table
CREATE TABLE TOY.BRANDS
(
ID INT NOT NULL,
NAME VARCHAR(20) NULL
)
GO

-- Load the table with data
INSERT INTO TOY.BRANDS (ID, NAME) VALUES
(1, 'Ford'),
(2, 'Chevy'),
(3, 'Dodge'),
(4, 'Plymouth'),
(5, 'Oldsmobile'),
(6, 'Lincoln'),
(7, 'Mercury');
GO

Cloning a table definition to a table variable in SQL Server

5 Answers5