SQL Server - Create a Copy of a Database Table and Place It in the Same Database

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

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;

Create Duplicate SQL Database for Testing

use copy database option in SQL server management studio

Duplicating a TABLE using Microsoft SQL Server Management

In SSMS open a new query window and then do something like

SELECT * INTO NewTable
FROM OldTable

change NewTable to the name that the new table should have, change OldTable to the name of the current table

this will copy over the basic table structure and all the data...it will NOT do any of the constraints, you need to script those out and change the names in those scripts

How can I clone an SQL Server database on the same server in SQL Server 2008 Express?

It turns out that I had attempted to restore from a backup incorrectly.

Initially I created a new database and then attempted to restore the backup here.
What I should have done, and what worked in the end, was to bring up the restore dialog and type the name of the new database in the destination field.

So, in short, restoring from a backup did the trick.

Thanks for all the feedback and suggestions guys

How to create a copy of database tables into another computer

Here is what I did to make a copy of all the tables along with the data present in my database of "computer A" into the database of "computer B".

1. Microsoft SQL Server Management Studio > Connect.
2. Right Click on The database which has the tables > Tasks > Generate Scripts.
3. Select either full database Specific table > Next
4. Advanced > Scroll down to "Types of Data to Script" and select "Schema and Data". > OK
5. Select location to save Script
6. Next > Next > Finish.

Now copy This Script File into "computer B"

1. Double click will open it into Microsoft SQL Server Management Studio
2. Click "Execute" button from top toolbar.

Done, Just Refresh the database and all your tables with the data are now copied.

SQL Server Copying tables from one database to another

Assuming that you have two databases, for example A and B:

• If target table not exists, the following script will create (I do not recommend this way):

  SELECT table_A.FIELD_1, table_A.FIELD_2,......, table_A.FIELD_N 
  INTO COPY_TABLE_HERE 
  FROM  A.dbo.table_from_A table_A
  

• If target table exists, then:

   INSERT INTO TABLE_TARGET 
   SELECT table_A.FIELD_1, table_A.FIELD_2,......, table_A.FIELD_N 
   FROM  A.dbo.table_from_A table_A
  

Note: if you want learn and practice this, you can use previous scripts, but if you want copy the complete structure and data from database to another, you should use, "Backup and restore Database" or, "Generate Script Database with data" and run this into another database.

Copy table from one database to another

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.

Then you can copy the table using a 4-part name, server.database.schema.table:

select  *
into    DbName.dbo.NewTable
from    LinkedServer.DbName.dbo.OldTable

This will both create the new table with the same structure as the original one and copy the data over.

Copy tables from one database to another in SQL Server

On SQL Server? and on the same database server? Use three part naming.

INSERT INTO bar..tblFoobar( *fieldlist* )
SELECT *fieldlist* FROM foo..tblFoobar

This just moves the data. If you want to move the table definition (and other attributes such as permissions and indexes), you'll have to do something else.

---

Related Topics