Copy tables from one database to another in SQL Server
I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do this?
9 Solutions collect form web for “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.
SQL Server Management Studio’s “Import Data” task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into.
If the tables don’t exist it will create them for you, but you’ll probably have to recreate any indexes and such. If the tables do exist, it will append the new data by default but you can adjust that (edit mappings) so it will delete all existing data.
I use this all the time and it works fairly well.
This should work:
SELECT * INTO DestinationDB..MyDestinationTable FROM SourceDB..MySourceTable
It will not copy constaints, defaults or indexes. The table created will not have a clustered index.
Alternatively you could:
INSERT INTO DestinationDB..MyDestinationTable SELECT * FROM SourceDB..MySourceTable
If your destination table exists and is empty.
If it’s one table only then all you need to do is
- Script table definition
- Create new table in another database
- Update rules, indexes, permissions and such
- Import data (several insert into examples are already shown above)
One thing you’ll have to consider is other updates such as migrating other objects in the future. Note that your source and destination tables do not have the same name. This means that you’ll also have to make changes if you dependent objects such as views, stored procedures and other.
Whit one or several objects you can go manually w/o any issues. However, when there are more than just a few updates 3rd party comparison tools come in very handy. Right now I’m using ApexSQL Diff for schema migrations but you can’t go wrong with any other tool out there.
create tablein management studio, run that script in bar to create the table. (Right click table in object explorer, script table as, create to…)
INSERT bar.[schema].table SELECT * FROM foo.[schema].table
You can also use the Generate SQL Server Scripts Wizard to help guide the creation of SQL script’s that can do the following:
- copy the table schema
- any constraints (identity, default values, etc)
- data within the table
- and many other options if needed
Good example workflow for SQL Server 2008 with screen shots shown here.
You may go with this way: ( a general example )
insert into QualityAssuranceDB.dbo.Customers (columnA, ColumnB) Select columnA, columnB from DeveloperDB.dbo.Customers
Also if you need to generate the column names as well to put in insert clause, use:
select (name + ',') as TableColumns from sys.columns where object_id = object_id('YourTableName')
Copy the result and paste into query window to represent your table column names and even this will exclude the identity column as well:
select (name + ',') as TableColumns from sys.columns where object_id = object_id('YourTableName') and is_identity = 0
Remember the script to copy rows will work iff the databases belongs to the same location.
You can Try This.
select * into <Destination_table> from <Servername>.<DatabaseName>.dbo.<sourceTable>
Server name is optional if both DB is in same server.
If there is existing table and we wants to copy only data,
we can try this query.
insert into Destination_Existing_Tbl select col1,col2 FROM Source_Tbl
INSERT INTO Alfestonline..url_details(url,[status],recycle) SELECT url,status,recycle FROM AlfestonlineOld..url_details