INSERT INTO SELECT with check to see if ID exists

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 2008 Forums

Transact-SQL (2008)

INSERT INTO SELECT with check to see if ID exists

Author

Topic

magikminox
Starting Member

27 Posts

Posted - 2013-03-22 : 07:36:18

I have a table called trades1 with fields tradeId,version and status.

I am trying to do an INSERT INTO Trades1 SELECT TradeID,Version,Status from another table called Trades_2 and check if the TradeID alreads exists in Trades1 table and if it exists,update the Status to 'INACTIVE' and if it does not exist, update the status to 'ACTIVE'

Your help and suggestions will be very appreciated.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-22 : 08:18:19

CREATE TABLE Trades2 (TradeID INT ,Version varchar(10), Status varchar(15))
Insert into Trades2 values(1, 'aaa', 'active'), (2, 'bbb', NULL), (3, 'ccc', NULL), (4, 'ddd', 'acive')
Insert into Trades1 values(1, 'aaa', 'active'), (4, 'ddd', NULL)
--INSERT INTO Trades1
SELECT t2.TradeID, t2.Version, CASE WHEN ta.TradeId IS NULL THEN 'ACTIVE' ELSE 'INACTIVE' END
from Trades2 t2
LEFT JOIN Trades1 ta ON ta.TradeID= t2.TradeId

Check this illustration... But already matched TradeIds are available in Trades1 table.. So you have to do MERGE operation ( MERGE is for doing INSERT and UPDATE operations at a time)
--This is solution for your scenario
MERGE INTO Trades1 Tar
USING Trades2 Sou ON (sou.TradeId = tar.TradeId)
WHEN MATCHED THEN
UPDATE SET [status] = 'INACTIVE'
WHEN NOT MATCHED THEN
INSERT (TradeID,Version, status)
VALUES( sou.TradeID, sou.Version, 'ACTIVE');

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources