Use EXcept command for insert to other DB

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)

Use EXcept command for insert to other DB

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-08-31 : 15:51:47

I came across this command for 2005 trying to find the differences between DB's. Below are my 2 dbs. But what I need to know is how can I take the differences between the 2 and insert them into tblPermTask. New things should be in tblTask and I need to put them in tblPermTask which is an exact replica of tblTask.

select * from tblTask

except

select * from tblPermTask

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-31 : 15:59:25

I'm lazy. I use RedGate's SQL data compare for that kind of stuff.

An infinite universe is the ultimate cartesian product.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-31 : 16:00:45

So your SELECT using EXCEPT shows the rows that are not in tblPermTask I think.
In this case I would try:


insert tblPermTask
select * from
(select * from tblTask
 except
 select * from tblPermTask)dt

Or am I wrong?

No, you're never too old to Yak'n'Roll if you're too young to die.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-31 : 16:01:41

You can definitely use EXCEPT for this task but the performance may not be that good.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-31 : 16:02:09

btw. why do you not use replication?

No, you're never too old to Yak'n'Roll if you're too young to die.

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-09-01 : 14:01:47

quote:
Originally posted by robvolk

You can definitely use EXCEPT for this task but the performance may not be that good.

I found this way to do it, might not be optimal way but I cannot have doubles of anything and this seems to work.

NSERT INTO tblPermTask (intTaskID, intAssignedId, intFilterId, intPositionId)
SELECT t.*
FROM tblTask t
LEFT JOIN tblPermTask p
ON p.intFilterID = t.intFilterID AND
p.intPositionId = t.intPositionId
WHERE p.intTaskId IS NULL

Select * from tblTask
Select * from tblPermTask

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources