How to Insert from one table to another table from difference Database

Question

Here is my table

Table Suppliers in Database A

ID(AUTONUMBER) | SupplierCode(Unique) | SupplierName
001              supp001                TestA
002              supp002                TestB
003              supp003                TestC

Table Suppliers in Database B

ID(AUTONUMBER) | SupplierCode(Unique) | SupplierName
001              supp001                TestA
003              supp003                TestC

In this case, i want to insert supp002 to table Suppliers in Database B And it will skip supp001 and supp003 because the SUPPLIERCODE exists

Can anyone help me with this condition

note: SQL Server query not MySQL

Why put a note saying not mysql, but tag the question mysql? Are the databases on the same server? Thinking your solution will be insert into databaseB..suppliers select * from databaseA..suppliers where id not in (select id from databaseB..suppliers) — Twelfth, Sep 04 '14 at 00:34
Query of SQL and MySQL is almost same but some scripts are difference, so i prefer any help in SQL query script — Shen Shen, Sep 04 '14 at 00:35
so did you tried @Twelfth's suggestion? it should work. If giving you identity insert exception, set it to on and off back later — ah_hau, Sep 04 '14 at 00:50
Then Twelfth suggestion in the first comment should do the job. You tryed it? — ForguesR, Sep 04 '14 at 00:50
almost but i got error nvalid object name 'databaseB.suppliers' — Shen Shen, Sep 04 '14 at 00:58

score 0 · Accepted Answer · answered Sep 04 '14 at 00:54

0

Might as well make it an answer

insert into databaseB..suppliers 
Select * from databaseA..suppliers where id not in (select id from databaseB..suppliers)

The table references should be correct, but I can't verify. You might want to go databasea.dbo.suppliers if that's the correct full name

answered Sep 04 '14 at 00:54

Twelfth

7,070
3
26
34

invalid object name 'databaseB.suppliers' – Shen Shen Sep 04 '14 at 00:57
there is two periods there. Table names are 4 parts, server.schema.owner.tablename you want to specify schema.owner.tablename. either databaseb..suppliers or databaseb.dbo.suppliers – Twelfth Sep 04 '14 at 00:58
First i want to let you know something that the database is not created by me, it's from our vendor of company so i tried to do like what my question and i got this error An explicit value for the identity column in table 'DatabaseA.dbo.Suppliers' can only be specified when a column list is used and IDENTITY_INSERT is ON. – Shen Shen Sep 04 '14 at 01:02
ah_hau put it in the comments...you'll need to disable identity insert – Twelfth Sep 04 '14 at 01:05
How to disable? I am not using mssql often – Shen Shen Sep 04 '14 at 01:06
lazy shen shen, a simple google search will show you that...googling disable identity insert in sql server brought up a few thousand lines. Here's one from stackoverflow: http://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008 – Twelfth Sep 04 '14 at 01:08
Thanks for answers but i still need some explanatiions – Shen Shen Sep 04 '14 at 01:11

score 0 · Answer 2 · answered Sep 04 '14 at 01:03

Assuming the two databases are on the same server and both tables are part of the "dbo" schema, inserting only those records in the A table that don't already exist in B into the B table can be handled like this:

INSERT INTO DatabaseB.dbo.Suppliers
SELECT ID,SupplierCode, SupplierName FROM  DatabaseA.dbo.Suppliers
WHERE SupplierCode NOT IN
(SELECT SupplierCode FROM  DatabaseB.dbo.Suppliers)

If the tables in A and B belong to a different schema, replace the "dbo" above with the appropriate schema name(s).

If the databases reside on different servers, this article that discusses creating linked servers may be useful but the syntax will be similar. Create Linked Servers

score 0 · Answer 3 · answered Sep 04 '14 at 01:12

I do not normally recommend using In or Not in statements i usually uses joins so try this

Insert into DatabaseB 
    (SupplierCode, 
    SupplierName)
Select SupplierCode,
       SupplierName
  From DatabaseA A
  Left join DatabaseB B
  On A.SupplierCode = B.SupplierCode
  Where B.SupplierCode IS NULL

How to Insert from one table to another table from difference Database

3 Answers3