best way to regularly cross reference large databa

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

General SQL Server Forums

New to SQL Server Programming

best way to regularly cross reference large databa

Author

Topic

atooms
Starting Member

3 Posts

Posted - 2009-10-20 : 04:38:49

Hello :)

I'm getting stuck into sql server 2005 and, i'm doing ok getting the data out that I want and gradually my queries are getting more complex, but I'm really struggling to get around this one, so I was hoping to get some help.

I have a database of approximately 90,000 records (database 1), and once a month I need to cross reference 6 fields in database 1 with 6 fields in another database (database 2), and update database 1 with the values from database 2.

Which sounded simple enough to me, but the problem is that my method involves a subquery to select the correct info from database 2, which is creating a very time and resource consuming process.

To give you an idea, to update these 6 fields it seems to want to max out the server for 3-5 hours, judging by the test runs I have done.

It occurs to me that there must be a quicker and easier way to do it than this:

UPDATE TABLE1
SET FIELD1 = (
				SELECT FIELD1
				FROM TABLE2
				WHERE TABLE2.IDFIELD = TABLE1.IDFIELD
			)
WHERE EXISTS (
				SELECT FIELD1
				FROM TABLE2
				WHERE TABLE2.IDFIELD = TABLE1.IDFIELD
			)

Any help is appreciated, as I say i'm new at this so I'm very happy to receive any and all feedback.

Many thanks,

Adrian

oooooh...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-20 : 06:43:08

Would this do? ->

UPDATE a SET 
  a.Field1 = b.Field1,
  a.Field2 = b.Field2,
  a.Field3 = b.Field3,
  a.Field4 = b.Field4,
  a.Field5 = b.Field5,
  a.Field6 = b.Field6
FROM TABLE1 a
  INNER JOIN TABLE2 b
    ON a.IDFIELD = b.IDFIELD

- Lumbago
http://xkcd.com/327/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-20 : 06:44:29

This does the same. You don't need the WHERE EXISTS if you use an inner join and you can just join the two tables. As they are in different databases and on the same server (although you haven't shown that here), then you will just need to use 3 part naming (database.owner.table). If they are on a different server and you are using a linked server, then you will need tto use 4 part naming (linkedserver.database.owner.table)


UPDATE TABLE1
SET FIELD1 =  t2.FIELD1
FROM TABLE1 t1
INNER JOIN TABLE2 t2
ON t2.IDFIELD = t1.IDFIELD

atooms
Starting Member

3 Posts

Posted - 2009-10-20 : 07:20:56

Thanks a lot, I'll test them shortly and get back to you :) looking good!

oooooh...

atooms
Starting Member

3 Posts

Posted - 2009-10-20 : 08:42:35

Awesome, it worked a treat. Unbelievably, the query that Lumbago submitted managed to perform the update almost instantly. I guess I have a long way to go with SQL.

Thanks a lot

oooooh...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-20 : 08:51:58

Great that it worked out for you...the power of sql is (almost) unlimited

- Lumbago
http://xkcd.com/327/

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-22 : 18:23:36

Well just to give you guys a hint

You have to do it that way in DB2

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources