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.
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.
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_AIf 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.
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;
Copy data into another table
If both tables are truly the same schema:
INSERT INTO newTable
SELECT * FROM oldTable
Otherwise, you'll have to specify the column names (the column list for newTable
is optional if you are specifying a value for all columns and selecting columns in the same order as newTable
's schema):
INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable
copy tables with data to another database in SQL Server 2008
You can use the same way to copy the tables within one database, the SELECT INTO
but use a fully qualified tables names database.schema.object_name
instead like so:
USE TheOtherDB;
SELECT *
INTO NewTable
FROM TheFirstDB.Schemaname.OldTable
This will create a new table Newtable
in the database TheOtherDB
from the table OldTable
whih belongs to the databaseTheFirstDB
Related Topics
Count Values for Every Column in a Table
How to Combine These Two SQL Statements
Why SQL Server Ignores Vaules in String Concatenation When Order by Clause Specified
How to Write a Query to Extract Individual Changes from Snapshots of Data
Hive - How to Further Optimize a Hiveql Query
How to Pivot Data in Bigquery Standard SQL Without Manual Hardcoding
Cascade Copy a Row with All Child Rows and Their Child Rows, etc
Split String Oracle into a Single Column and Insert into a Table
Use Plink to Execute Command (Oracle SQL Query) on Remote Server Over Ssh
I Have a Delete-Insert Cte That Fails in a Strange Manner
Which Orm Frameworks Will Build and Execute the SQL Ddl for You
SQL Server Automatic Update Datetimestamp Field
Tsql Aggregate String for Group By
Merge Duplicate Temporal Records in Database
SQL Trigger After Insert Update Another Table with Conditions
Transpose a Row into Columns with MySQL Without Using Unions