SQL Server - Create a copy of a database table and place it in the same database

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This is another option   select top 0   into  lt new table gt  from  lt original table gt

User · Answer

If you want to duplicate the table with all its constraints  amp  keys follows this below steps    Open the database in SQL Management Studio   Right-click on the table that you want to duplicate   Select Script Table as -  Create to -  New Query Editor Window   This will generate a script to recreate the table in a new query window   Change the table name and relative keys  amp  constraints in the script  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

User · Answer

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

User · Answer

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  lt  gt  1

[sql] SQL Server - Create a copy of a database table and place it in the same database?

Examples related to sql

Examples related to sql-server-2008

Examples related to tsql

Examples related to sql-server-2008-r2