Help with insert statement from one DB to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with insert statement from one DB to another

Author

Topic

demausdauth
Starting Member

17 Posts

Posted - 2008-08-01 : 17:33:59

I have about 170 tables that I need to update. Basically DB2 needs to be merged into DB1 and make sure not to create duplicate records. Ultimately it would be nice to be able to delete the records from DB2 and leave any duplicates in it so I can go back later and look at them to see why they were duplicates. ( but the deleting is secondary if even needed )

So I found this little script -- that i cannot seem to get working
I personally would not have made the column name Type. That's a previous developer.

INSERT INTO DB1.DBO.AgencyCodes 
SELECT MDBN.* FROM DB2.DBO.AgencyCodes AS MDBN 
LEFT JOIN DB1.dbo.AgencyCodes AS ODBN 
ON ODBN.agency = MDBN.agency and odbn.[Type] = MDBN.[Type] 
and odbn.Code = mdbn.code WHERE odbn.agency is Null and odbn.[Type] is Null and odbn.Code is Null

Am I barking up the wrong tree? If not then why doesn't this seem to work?
These are the errors I get when i try to run it:


Msg 207, Level 16, State 1, Line 1
Invalid column name 'Type'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Type'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Code'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'code'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Type'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Code'.

If it helps to know. I am updating a VB6 program to VB.NET. I will be running this SQL statement from withing the VB.NET program.

In a nutshell: I have 2 db. I need to move the records from db2 into db1, they have the exact same schema. In the program I will be looping through each table and depending on other criteria move the records from db2 into db1. I am hoping to be able to do it with one SQL statement for each table.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-01 : 18:44:08

May be this:

INSERT INTO DB1.DBO.AgencyCodes 
SELECT * FROM DB2.DBO.AgencyCodes 
LEFT JOIN DB1.dbo.AgencyCodes 
ON DB2.dbo.AgencyCodes.agency = DB1.DBO.AgencyCodes.agency and DB2.dbo.AgencyCodes.[Type] = DB1.dbo.AgencyCodes.[Type] 
and DB2.dbo.AgencyCodes.Code = DB1.dbo.AgencyCodes.code WHERE DB2.dbo.AgencyCodes.agency is Null and DB2.dbo.AgencyCodes.[Type] is Null and DB2.dbo.AgencyCodes.Code is Null

demausdauth
Starting Member

17 Posts

Posted - 2008-08-01 : 23:02:09

That gives me another error -- in fact the error would suggest that what I had originally would be correct.

Msg 1013, Level 16, State 1, Line 1
The objects "nasafile60park.dbo.AgencyCodes" and "nasafile60.DBO.AgencyCodes" 
in the FROM clause have the same exposed names. Use correlation names to distinguish them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-01 : 23:09:02

specify the column name explicitly in the SELECT statement. Don't use SELECT *

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 05:29:04

quote:
Originally posted by demausdauth

That gives me another error -- in fact the error would suggest that what I had originally would be correct.

Msg 1013, Level 16, State 1, Line 1
The objects "nasafile60park.dbo.AgencyCodes" and "nasafile60.DBO.AgencyCodes"
in the FROM clause have the same exposed names. Use correlation names to distinguish them.

it seems like there are fields with same name in both db. so replace the * with only columns you want and also use aliases to clearly specify which tables field you want

demausdauth
Starting Member

17 Posts

Posted - 2008-08-04 : 10:22:49

I suppose I should have mentioned this earlier, I guess I thought I made it clear but perhaps it was only in my own mind. The databases that I am trying to merge are from an application that we have created and maintain for our customers. Occassionally one customer buys another customer and so then they want the two databases merged into one. So DB1 is the identical schema as DB2.

I will try giving the columns alt names and see if that works, as well as fully qualifying the columns in the select statement.

demausdauth
Starting Member

17 Posts

Posted - 2008-08-04 : 11:06:11

AAAAARRRRRRRRRRGGGGGGGGGGGHHHHHHHHHHHH !!!

I am sorry people -- I humbly admit that I must not have had my brain in last week. Here is the script that works:



INSERT INTO nasafile60park.DBO.AgencyCodes 
SELECT DB1.agencycode, DB1.company, DB1.agency, DB1.description  FROM nasafile60.DBO.AgencyCodes  AS DB1
LEFT JOIN nasafile60park.dbo.AgencyCodes  AS DB2
ON DB1.agencycode = DB2.agencycode and DB1.company = DB2.company 
and DB1.agency = DB2.agency WHERE DB2.agencycode is Null and DB2.company is Null and DB2.agency is Null

I had several column names that were off. The worst part is that I feel like I wasted peoples time. However, all was not entirely wasted as I did learn some new things (besides I should really double check my column names).

I would like to thank you all for the help that you did give.

demausdauth
Starting Member

17 Posts

Posted - 2008-08-04 : 11:14:22

And incidently I have found that the

SELECT DB1.* FROM nasafile60.dbo.AgencyCodes AS DB1

will work also. Just helps to have the correct column names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:03:47

quote:
Originally posted by demausdauth

And incidently I have found that the

SELECT DB1.* FROM nasafile60.dbo.AgencyCodes AS DB1

will work also. Just helps to have the correct column names.

thats because you are asking query to return only columns from one table so there's no question of duplicate column names occuring

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources