SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 INSERT INTO SELECT with check to see if ID exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magikminox
Starting Member

South Africa
27 Posts

Posted - 03/22/2013 :  07:36:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/22/2013 :  08:18:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000