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.
This question is related to
sql
sql-server-2008
tsql
sql-server-2008-r2
This is another option:
select top 0 * into <new_table> from <original_table>
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
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).
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.
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
If you want to duplicate the table with all its constraints & keys follows this below steps:
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
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)
use sql server manegement studio or netcat and that will be easier to manipulate sql
Source: Stackoverflow.com