162

I have a table ABC in a database DB. I want to create copies of ABC with names ABC_1, ABC_2, ABC_3 in the same DB. How can I do that using either Management Studio (preferably) or SQL queries ?

This is for SQL Server 2008 R2.

sequel.learner
  • 3,421
  • 7
  • 22
  • 24
  • 1
    another post not directly related, but can be related to the above use-case - http://stackoverflow.com/questions/6810425/saving-changes-is-not-permitted-in-sql-server this can be useful when you want to edit the copies of the table (eg. allow nulls, change data type etc.) without having to recreate the copies. – sequel.learner Mar 15 '13 at 22:55

8 Answers8

352

Use SELECT ... INTO:

SELECT *
INTO ABC_1
FROM ABC;

This will create a new table ABC_1 that has the same column structure as ABC and contains the same data. Constraints (e.g. keys, default values), however, are -not- copied.

You can run this query multiple times with a different table name each time.

If you don't need to copy the data, only to create a new empty table with the same column structure, add a WHERE clause with a falsy expression:

SELECT *
INTO ABC_1
FROM ABC
WHERE 1 <> 1;
jaek
  • 3
  • 3
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • 5
    Perfect answer for my problem. – sequel.learner Mar 15 '13 at 09:16
  • 12
    Doesn't work completely.... calculated columns in the original aren't copied as the same in the target. Also, PK and Default constraints aren't copied either – Simon Green Oct 06 '15 at 19:04
  • @SimonGreen - You can post a new question for this problem with your code. – Mahmoud Gamal Oct 07 '15 at 08:32
  • 6
    Of course it doesn't work completely. He warned constraints don't get copied, nor do primary keys, or default values. The command he offers creates a new table with the same column structure (just like he said) and inserts all data into the new table for you. – user5855178 Apr 05 '17 at 14:14
  • 5
    I find using `SELECT TOP(0) *` cleaner than the always-false `WHERE` statement method – Thymine Nov 29 '18 at 21:29
32

Copy Schema (Generate DDL) through SSMS UI

In SSMS expand your database in Object Explorer, go to Tables, right click on the table you're interested in and select Script Table As, Create To, New Query Editor Window. Do a find and replace (CTRL + H) to change the table name (i.e. put ABC in the Find What field and ABC_1 in the Replace With then click OK).

Copy Schema through T-SQL

The other answers showing how to do this by SQL also work well, but the difference with this method is you'll also get any indexes, constraints and triggers.

Copy Data

If you want to include data, after creating this table run the below script to copy all data from ABC (keeping the same ID values if you have an identity field):

set identity_insert ABC_1 on
insert into ABC_1 (column1, column2) select column1, column2 from ABC
set identity_insert ABC_1 off
Community
  • 1
  • 1
JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • 1
    Are you sure this method copies the indices as well? I tried it and it didn't. – BornToCode Dec 18 '18 at 17:21
  • 4
    You must set `IDENTITY_INSERT` to **ON** to permit set the identity column "manually", you mixed the order in your example. Also, you must EXPLICIT list your columns – jean Jun 04 '19 at 16:50
  • 3
    Thanks @jean; well spotted after 6 years unnoticed! Fixed. – JohnLBevan Jun 04 '19 at 16:57
  • 1
    This almost solved the problem for me. The last trick is you can drag the "Columns" folder in the tree in SSMS into the editor to get a list of columns. I have a timestamp column on every table, so I had to get the list of columns, and then remove the timestamp. – Wade Hatler Feb 18 '20 at 15:53
24

If you want to duplicate the table with all its constraints & keys follows this below steps:

  1. Open the database in SQL Management Studio.
  2. Right-click on the table that you want to duplicate.
  3. Select Script Table as -> Create to -> New Query Editor Window. This will generate a script to recreate the table in a new query window.
  4. Change the table name and relative keys & constraints in the script.
  5. Execute the script.

Then for copying the data run this below script:

SET IDENTITY_INSERT DuplicateTable ON

INSERT Into DuplicateTable ([Column1], [Column2], [Column3], [Column4],... ) 
SELECT [Column1], [Column2], [Column3], [Column4],... FROM MainTable

SET IDENTITY_INSERT DuplicateTable OFF
Rousonur Jaman
  • 1,183
  • 14
  • 19
5

1st option

select *
  into ABC_1
  from ABC;

2nd option: use SSIS, that is right click on database in object explorer > all tasks > export data

  • source and target: your DB
  • source table: ABC
  • target table: ABC_1 (table will be created)
Claude
  • 1,724
  • 3
  • 17
  • 46
3

This is another option:

select top 0 * into <new_table> from <original_table>
Keni
  • 57
  • 3
1

You need to write SSIS to copy the table and its data, constraints and triggers. We have in our organization a software called Kal Admin by kalrom Systems that has a free version for downloading (I think that the copy tables feature is optional)

Chris D
  • 27
  • 2
0

In Case you want to do it N times (may be not practical in real world), you might try this:

declare @counter int
set @counter = 2
declare @tname NVARCHAR(100)
DECLARE @SQString NVARCHAR(MAX)
while(@counter <= 20) -- duplicate 20 times
begin
SET @tname= concat('Table_',@counter)
SET @SQString = 'select * into ' + @tname + ' from Table_1'
EXEC (@SQString)
set @counter = @counter + 1
End
kkid
  • 71
  • 1
  • 2
-1

use sql server manegement studio or netcat and that will be easier to manipulate sql

gasroot
  • 515
  • 3
  • 15