Merging data from Table 1 to Table 2?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Merging data from Table 1 to Table 2??

Author

Topic

Conjurer
Starting Member

30 Posts

Posted - 2006-01-11 : 14:45:04

I don't know the correct syntax to accomplish this. I've looked in the on-line help but am not coming up with anything.

Basically I have two identically structured tables in two separate databases.

I wanted to select anything from the dbo2.table that isn't in the dbo1.table and insert it into dbo1.table.

I thought I could do something like...

quote:

Select INTO dbo1.table

and then add my select statement. But that throws an error:

quote:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'bkupproceduresfeeschedule' in the database.

And the on-line documentation says an INTO is only for a new table.

How can I merge records from one table into another table?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-11 : 16:05:19

Look up the syntax for INSERT in Books Online.

INSERT into table1 (blah, blah, blah)
select blah, blah, blah
from table2
left outer join table1 on table2.id = table1.id
where table1.id is null

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-11 : 16:15:38

Since he has 2 identical tables he doesn't need to have field lists. (I know its a bad practice)

Insert into [dbo1.table]
Select * from dbo2.table
left outer join dbo1.table on dbo2.table.[primary key] = dbo1.table.[primary key]
where dbo1.table.[primary key] is null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-11 : 16:17:47

IMO, one should not provide bad practice solutions when the good practice solution has been provided.

Tara Kizer
aka tduggan

Conjurer
Starting Member

30 Posts

Posted - 2006-01-11 : 16:24:01

Thanks -- I will look at Insert. Didn't know that was the command so this helps.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 09:31:57

Select * may be fine for examples or for one-time jobs, but should never be used in production code. If I ever found a developer using it for application logic I would send them back to DBA kindergarten.

Kristen
Test

22859 Posts

Posted - 2006-01-12 : 13:42:15

In this instance, and specifically where the script is used as a "hack", rather than a pucker scheduled process, we usually do:

INSERT INTO TableA (... full column list ...)
SELECT *
FROM TableB
WHERE / JOIN to identified differences

The idea is that if a column is added to TableB this will fail, and that will alert us to the need to fix the conversion process - typically someone forgets to add the New Column in both places; or the script is a "hack" to sort out data between Test / Production and needs to take into account different versions that are running, and so on.

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 14:13:10

quote:
Originally posted by Kristen
The idea is that if a column is added to TableB this will fail, and that will alert us to the need to fix the conversion process

This is what I usually refer to as "the scream test". As in "does anybody still use this stored procedure?" "I dunno. Delete it and listen for screaming....".

Kristen
Test

22859 Posts

Posted - 2006-01-12 : 14:33:43

"scream test"

LOL! Terminology adopted, thanks!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-12 : 15:39:46

For certain critical stored procedures, the test is accurate and effective even if the accounting department is two floors below you.

Note: when they call the helpdesk, blame Microsoft.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources