March 27, 2007 at 7:20 pm
Hi,
i need help on this.
i have a table A in SQL SERVER DATABASE in which i want to do an update and insert from a #temp table which i created and has data in it.
table A has the following two columns
MS_ID, Amount where MS_ID IS THE PRIMARY KEY
what i want is to do is check if the record already exists in table A,if exists update it ,if does not exist do the insert.
March 28, 2007 at 12:31 am
You have to do an "UPSERT".
First an UPDATE
UPDATE t1
SET t1.ColX = t2.Col1
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ColBlue = t1.ColWarm
Then an INSERT
INSERT Table1 (ColA, ColB, ColC)
SELECT t2.Col1, t2.Col2, t2.Col3
FROM Table2 AS t2
LEFT JOIN Table1 AS t1 ON t1.ColWarm = t2.ColBlue
WHERE t1.ColWarm IS NULL
N 56°04'39.16"
E 12°55'05.25"
March 28, 2007 at 8:21 am
Or if you don't need the data for other purposes, use Peter's idea, but update, then delete from temp where matching the table, then insert everything else.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply