dwain.c (8/29/2012)
ChrisM@Work (8/29/2012)
purushottam2 (8/29/2012)
I want to update in single query, other wise i have to use transaction and i can not use transaction.....Why not? Seems very unreasonable.
You're using SQL Server 2008 - Google "Composable DML". You can insert into two different tables in the same statement using the OUTPUT from one of them.
"Composable DMS" - so that's the term for it.
Too bad you can only INSERT the results somewhere or it would be really cool (instead of semi-cool)!
Heh good catch mate - thanks!
USE tempdb
GO
DROP TABLE ABC
CREATE TABLE ABC (Id INT, Name VARCHAR(10))
INSERT INTO ABC VALUES (1, 'PK'), (2, 'SK')
DROP TABLE ABC1
CREATE TABLE ABC1 (Id INT, Name VARCHAR(10))
INSERT INTO ABC1 VALUES (1, 'KKKKK'), (2, 'MMMMM')
SELECT * FROM ABC
SELECT * FROM ABC1
INSERT ABC1 (Id, Name)
SELECT *
FROM (
UPDATE A SET
A.Name = 'PPPPP'
OUTPUT deleted.Id, inserted.Name
FROM ABC A
JOIN ABC1 B ON A.Id = B.Id
WHERE A.Id = 1
) d
SELECT * FROM ABC
SELECT * FROM ABC1
You get an error if you try to UPDATE.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden