Insert and Update

Question

Post reply

Insert and Update

CrazyMan

SSChampion

Points: 12560
More actions
October 8, 2008 at 10:15 am

#199129

Hi All
Is there any way where i can insert and update between 2 tables at the same time using one T-SQL statement
Cheers
🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

no, it'll be two separate statements....but that's not a bad thing.

can you give more details what you are trying to do?

this is a very common situation, where you update existing data, then insert data that wasn't there after that, based on some criteria.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

CrazyMan SSChampion Points: 12560 More actions · Answer 2

exactly i am using table a and table b

i want to update records on table b to a and then insert records that are not on table a

Thanks

🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

yeah...still two statements, you really didn't give enough info to help, but here's a lame example:

--only matches will get updated, right?

UPDATE TABLEA

SET PROCESSFLAG='Y' WHERE PROCESSFLAG='N'

FROM TABLEB

WHERE TABLEA.ID = TABLEB.ID

--now insert from TABLEA

INSERT INTO TABLEA(column list)

SELECT TABLEB.ID, 'N' AS PROCESSFLAG,other columns..

FROM TABLEB

LEFT OUTER JOIN TABLEA ON TABLEB.ID = TABLEA.ID

WHERE TABLEA.ID IS NULL --null, because not in TableA yet....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 4

Here comes the Microsoft answer: "Upgrade to MSSQL 2008, start using the MERGE statement" :D:D

Wilfred
The best things in life are the simple things

CrazyMan SSChampion Points: 12560 More actions · Answer 5

I need to make the 2 SQL statements into one, is'nt there a MERGE statement on SQL 2005 🙂

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

CrazyMan (10/10/2008)
I need to make the 2 SQL statements into one, is'nt there a MERGE statement on SQL 2005 🙂

Just put them both in a single transaction. Commit only if both operations occur with no error.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 7

CrazyMan (10/10/2008)
I need to make the 2 SQL statements into one, is'nt there a MERGE statement on SQL 2005 🙂

Yeah, but in 2005 it's call UPSERT.

...tick...

...tock...

(Wait for it....)

...tick...

...tock...

Ok, before you waste a lot of time searching the bol, UPSERT is a principle, not a SQL statement. Lowell has illustrated how it's done -- wrap the two statements in a transaction to make them one unit of work. We here can only offer what will work, not what we wished would work.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

CrazyMan SSChampion Points: 12560 More actions · Answer 8

Thanks Guys, I am looking forward to change to 2008, next year, I will make this as 2 statements for time being .

Thanks for all the posts

Cheers

🙂