Copying tables gracefully - SQL Server Forums

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

Copying tables gracefully

Author

Topic

A.J.Gibson
Starting Member

9 Posts

Posted - 2008-10-28 : 11:07:18

Hi, I'm completely new to SQL and this forum, so please bare with me. I've been doing some research, and I think I have a solution, but it's pretty aweful.

I have 2 tables, db1.A and db2.B. I need table db1.A to match db2.B. In other words, I can delete everything in db1.A and do a straight copy from db2.B, but this seems pretty inefficient to me. Assuming the column names are the same in both tables, is there a better, more professional way of doing this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:10:13

whats the Primary Key of the tables?

A.J.Gibson
Starting Member

9 Posts

Posted - 2008-10-28 : 11:20:40

Table db1.A is:
brand_id*, logo_id, brand_name
and Table db2.B is:
cm_known_brand_id*, brand_name, logo_id, brand_name_fr
with the asterix denoting the primary key, the brand_name fields are strings, everything else is a number, I'm going to perform a select first on db2.B to rename the first field to brand_id and to get rid of the redundant fourth field

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 11:24:07

are you trying to backup tables? If so use backup/restore feature.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:28:04

[code]INSERT INTO db1.A
SELECT logo_id,brand_name
FROM db2..B b
LEFT JOIN db1..A a
ON a.brand_id=b.cm_known_brand_id
WHERE a.brand_id IS NULL

UPDATE a
SET a.logo_id=b.logo_id,
a.brand_name=b.brand_name
FROM db2..B b
INNER JOIN db1..A a
ON a.brand_id=b.cm_known_brand_id

DELETE a
FROM db1..A a
LEFT JOIN db2..B b
ON a.brand_id=b.cm_known_brand_id
WHERE b.cm_known_brand_id IS NULL
[/code]

A.J.Gibson
Starting Member

9 Posts

Posted - 2008-10-28 : 11:40:26

Thanks visakh, I really didn't want to have to delete and reload a DB every night.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:45:38

quote:
Originally posted by A.J.Gibson

Thanks visakh, I really didn't want to have to delete and reload a DB every night.

you're not deleting db. you're just deleting the ones which doesnt exist on B to make them similar. if not required, ignore delete query and use other two

A.J.Gibson
Starting Member

9 Posts

Posted - 2008-10-28 : 11:47:47

I meant my original solution: to empty the DB and copy everything over. Yeah, I see how your solution works. Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:49:22

quote:
Originally posted by A.J.Gibson

I meant my original solution: to empty the DB and copy everything over. Yeah, I see how your solution works. Thanks again.

Cheers

A.J.Gibson
Starting Member

9 Posts

Posted - 2008-10-29 : 10:28:04

One last question, how can I adjust this to work under MySQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:10:21

quote:
Originally posted by A.J.Gibson

One last question, how can I adjust this to work under MySQL?

not sure about that. This is MS SQL Server solution so syntaxes will be that which works in SQL Server. you may post this in some MySQL related forums if you need syntax specific to MySQL. there's one such forum at www.dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources