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 2005 Forums
 Transact-SQL (2005)
 check status		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kalpa
Starting Member

9 Posts
Posted - 08/10/2008 :  05:20:05  Show Profile  Reply with Quote
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

India
47069 Posts
Posted - 08/10/2008 :  05:36:24  Show Profile  Reply with Quote
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)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/10/2008 :  06:01:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
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