check status - SQL Server Forums

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

Transact-SQL (2005)

check status

Author

Topic

Kalpa
Starting Member

9 Posts

Posted - 2008-08-10 : 05:20:05

i need to insert data from table A to table B and only after insertion i have to delete the same data from table A.Afetr insertion i need to check the insertion status and then only i need to delete.

Please suggest me how to do it in Sql Server 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-10 : 05:36:24

use OUTPUT clause


DECLARE @INSERTED_IDS
(
PKCol int
)

INSERT INTO TableB
OUTPUT INSERTED.PKCol INTO @INSERTED_IDS
SELECT columns
FROM TableA


DELETE a
FROM TableA a
INNER JOIN @INSERTED_IDS i
ON i.PKCol=a.PKCol

Here PKCol is the primary key column of tableA (i'm assuming its f type integer else change datatype in table variable)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-10 : 06:01:44

You can do the DELETE and INSERT at the same time.
See http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx

DELETE     a
OUTPUT     deleted.Col1,
           deleted.Col2
INTO       TableB
FROM       TableA AS a
WHERE      SomeCol = 'Some value'

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources