Delete Between 2 tables - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Delete Between 2 tables

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-08-28 : 09:23:20

Morning Chiefs,

I have a situatiuon at hand.. Please I need your assistance

I have 2 table .. Table A and Table
Both have same columns and same in number too...

Table A is staging table(BI people, :P), this contains new records, Table B contains History..

I want delete the content of Table A from Table B and re-insert all over again if exists, else It insert fresh record...

This is the logic am thinking of, a better logic will be highly appreciated.

Thanks you all, folks.
'Nice day ahead

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 09:45:48

delete and re-insert? didnt understand why you want to do that. can you illustrate with some data?

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-08-28 : 09:56:04

quote:
Originally posted by visakh16

delete and re-insert? didnt understand why you want to do that. can you illustrate with some data?

Am afraid I might have a wrong logic.. what about check
if exists,

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 09:58:46

quote:
Originally posted by abacusdotcom

quote:
Originally posted by visakh16

delete and re-insert? didnt understand why you want to do that. can you illustrate with some data?

Am afraid I might have a wrong logic.. what about check
if exists,

I sign for fame not for shame but all the same, I sign my name.

Can you give some data and explain what you're trying to do. Without knowing your background its difficult to suggest what you need.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-08-28 : 10:12:17

quote:
Originally posted by visakh16

delete and re-insert? didnt understand why you want to do that. can you illustrate with some data?

Good day visakh16

Am afraid I might have a wrong logic..

ok this is what I want..

STEPS
1. check if exists the whole records in TABLE A in TABLE B
2. if exists replace the records in TABLE B with TABLE A
3. if not exists insert all

Thanks

Allow me give you the scenario am working...
I have a table called Budget, it has the following rows

|ID|Field|Period|Amount| ..

I source data from excel document, using ETL of SSIS... I then dump into a temp table. I want to dump the temp into budget table, but i dont want to duplicate any records, so checking by year and month, i will know if it exist or not.. if it exist remove and insert (or update) else insert...

thanks

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 10:42:38

ok. i think you just need below
--records in temp not in budget so new ones and insert

INSERT INTO Budget (fields...)
SELECT t.*
FROM Temp t
LEFT JOIN Budget b
ON b.Year=t.year
AND b.month=t.month
WHERE b.year IS NULL
AND b.month IS NULL

--records in both temp and Budget so update

UPDATE b
SET b.field1=t.field1,...
FROM Temp t
INNER JOIN Budget b
ON b.Year=t.year
AND b.month=t.month

--records in Budget not in Temp...so delete


DELETE b
FROM Budget b
LEFT JOIN Temp t
ON b.Year=t.year
AND b.month=t.month
WHERE t.year IS NULL
AND t.month IS NULL

and remember to replace * by actual field names and also in select list

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-08-28 : 14:07:59

saw this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96292&SearchTerms=upsert..

Thanks visakh16... done!!!!!!!!!!!!!!!!!!!!!!!! :)

I sign for fame not for shame but all the same, I sign my name.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-28 : 14:13:52

Or use SSIS package if you want performance gain:
1)Oledb task
2)Lookup task(excludes if find matches and updates or insert)
3)Oledb destination.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources