Copy Data from a table in one Database to another separate database

Question

Basically I have a two databases on SQL Server 2005.

I want to take the table data from one database and copy it to another database's table.

I tried this:

SELECT * INTO dbo.DB1.TempTable FROM dbo.DB2.TempTable

This didn't work.

I don't want to use a restore to avoid data loss...

Any ideas?

score 73 · Accepted Answer · answered Jun 02 '09 at 17:50

73

SELECT ... INTO creates a new table. You'll need to use INSERT. Also, you have the database and owner names reversed.

INSERT INTO DB1.dbo.TempTable
SELECT * FROM DB2.dbo.TempTable

answered Jun 02 '09 at 17:50

Tom H

46,766
14
87
128

I thought I had it but I got this error. Any work around for that ? An explicit value for the identity column in table 'DB1.dbo.TempTable ' can only be specified when a column list is used and IDENTITY_INSERT is ON. – Gabe Jun 02 '09 at 18:15
I tried this: SET IDENTITY_INSERT DB1.dbo.TempTable ON GO INSERT INTO DB1.dbo.TempTable SELECT * FROM DB2.dbo.TempTable -- Disable IDENTITY_INSERT. SET IDENTITY_INSERT DB1.dbo.TempTable OFF GO – Gabe Jun 02 '09 at 18:17
Let's say TempTable are equal in both databases, but TempTable from DB2 has an extra column (with constraint not null). How would you copy data from db1.TempTable to db2.TempTable, adding a default value to it's extracolumn (i.e., "True") ? – Twimnox Sep 27 '18 at 14:08
You should create a new question for this (after searching to make sure that it's not answered elsewhere). In this way other people with the same problem can more easily find an answer in the future. They wouldn't be able to find it as easily if it's buried in comments in response to another answer. – Tom H Sep 28 '18 at 15:44
I found this real useful as a complement when Ids need to match: https://stackoverflow.com/questions/2005437/an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a – RKrogh Jan 16 '19 at 12:43
What if I only need 10 rows? – Shravya Mutyapu Sep 20 '21 at 14:26
Do you have some reason to suspect that this wouldn't work with only 10 rows? Have you tried using it? – Tom H Sep 22 '21 at 14:39

score 20 · Answer 2 · answered Jun 02 '09 at 17:51

20

SELECT * INTO requires that the destination table not exist.

Try this.

INSERT INTO db1.dbo.TempTable
 (List of columns here)
SELECT (Same list of columns here)
FROM db2.dbo.TempTable

answered Jun 02 '09 at 17:51

Mitchel Sellers

62,228
14
110
173

score 9 · Answer 3 · answered Jun 02 '09 at 17:49

9

It's db1.dbo.TempTable and db2.dbo.TempTable

The four-part naming scheme goes:

ServerName.DatabaseName.Schema.Object

answered Jun 02 '09 at 17:49

Aaron Alton

22,728
6
34
32

score 5 · Answer 4 · answered Jun 04 '14 at 11:39

We can three part naming like database_name..object_name

The below query will create the table into our database(with out constraints)

SELECT * 
INTO DestinationDB..MyDestinationTable 
FROM SourceDB..MySourceTable

Alternatively you could:

INSERT INTO DestinationDB..MyDestinationTable 
SELECT * FROM SourceDB..MySourceTable

If your destination table exists and is empty.

score 5 · Answer 5 · edited Jul 08 '10 at 19:59

Hard to say without any idea what you mean by "it didn't work." There are a whole lot of things that can go wrong and any advice we give in troubleshooting one of those paths may lead you further and further from finding a solution, which may be really simple.

Here's a something I would look for though,

Identity Insert must be on on the table you are importing into if that table contains an identity field and you are manually supplying it. Identity Insert can also only be enabled for 1 table at a time in a database, so you must remember to enable it for the table, then disable it immediately after you are done importing.

Also, try listing out all your fields

INSERT INTO db1.user.MyTable (Col1, Col2, Col3)
SELECT Col1, COl2, Col3 FROM db2.user.MyTable

score 3 · Answer 6 · answered Oct 14 '16 at 13:00

Don't forget to insert SET IDENTITY_INSERT MobileApplication1 ON to the top, else you will get an error. This is for SQL Server

SET IDENTITY_INSERT MOB.MobileApplication1 ON
INSERT INTO [SERVER1].DB.MOB.MobileApplication1 m
      (m.MobileApplicationDetailId,
       m.MobilePlatformId)
SELECT ma.MobileApplicationId,
       ma.MobilePlatformId 
FROM [SERVER2].DB.MOB.MobileApplication2 ma

score 2 · Answer 7 · edited Feb 15 '17 at 09:59

2

Im prefer this one.

INSERT INTO 'DB_NAME' 
(SELECT * from 'DB_NAME@DB_LINK')
MINUS 
(SELECT * FROM 'DB_NAME');

Which means will insert whatsoever that not included on DB_NAME but included at DB_NAME@DB_LINK. Hope this help.

edited Feb 15 '17 at 09:59

Paul

4,160
3
30
56

answered Oct 24 '16 at 07:27

Leon

21
1

score 1 · Answer 8 · edited Apr 21 '16 at 06:22

1

INSERT INTO DB1.dbo.TempTable
SELECT * FROM DB2.dbo.TempTable

If we use this query it will return Primary key error.... So better to choose which columns need to be moved, like

INSERT INTO db1.dbo.TempTable // (List of columns here)
SELECT (Same list of columns here)
FROM db2.dbo.TempTable

edited Apr 21 '16 at 06:22

yennsarah

5,467
2
27
48

answered Apr 21 '16 at 05:25

Sundeep Kumar Chowdary

11
1

score 0 · Answer 9 · answered Apr 28 '17 at 13:41

0

This works successfully.

INSERT INTO DestinationDB.dbo.DestinationTable (col1,col1)
 SELECT Src-col1,Src-col2 FROM SourceDB.dbo.SourceTable

answered Apr 28 '17 at 13:41

Ghebrehiywet

884
3
12
20

score 0 · Answer 10 · answered Jul 28 '21 at 11:43

You can copy one table to other db table even with some additional columns.

insert into [SchoolDb1].[dbo].Student(Col1, Col2,Col3, CreationTime, IsDeleted) 
select Col1, Col2,Col3,,getdate(),0 from [SchoolDb2].[dbo].Student

These are additional columns: (CreationTime is datatime and IsDeleted is boolean)

score 0 · Answer 11 · edited Dec 08 '22 at 08:50

0

select * from DBA1.TABLENAMEA;

create table TABLENAMEA as (select * from DBA1.TABLENAMEA);

edited Dec 08 '22 at 08:50

Julian

6,586
2
9
33

answered Dec 01 '22 at 11:42

Abhishek Dubey

29
3

score 0 · Answer 12 · answered Feb 03 '23 at 10:55

These manual way provides more flexibility, but at the same time, works for table whose size is smaller to few thousands.

Do select * from <table name> from DB, once whole table is displayed, scroll till it's bottom.

Right click and do Export table as Insert statement, provide the name of the destination table and export the table as .sql file.

Use any text editor to further do regular find and replace operation to include more column names etc.

Use the INSERT statement in destination DB.

score 0 · Answer 13 · answered Jun 02 '09 at 17:50

0

Try this

INSERT INTO dbo.DB1.TempTable
    (COLUMNS)
    SELECT COLUMNS_IN_SAME_ORDER FROM dbo.DB2.TempTable

This will only fail if an item in dbo.DB2.TempTable is in already in dbo.DB1.TempTable.

answered Jun 02 '09 at 17:50

David Basarab

72,212
42
129
156

Copy Data from a table in one Database to another separate database

13 Answers13

Linked