Delete and insert in one statement...

[SrkrReds]

Hi All..
Can someone help me with syntax on how to select a record from tableA(based on some criteria) and insert into tableB and then delete that record from tableA, all in one single statement in query analyzer. Do I use the memory resident DELETED table(can this even be used anywhere other than triggers) for this purpose and if so how?

THanx in advance....

 

[checkai]

Declare @ID as INTEGER;
SET @ID = (select the primary key of the table you want)

Insert into table2
select record from table1

delete from table1
where primarykeyid = @id

this work?

 

[SrkrReds]

Thanx for the suggestion. I was doing something like that based on some criteria. I was wondering if all of it can be done in one sql statement (something like a subquery - selecting the deleted record from DELETED table) without declaring any variables.

Thanx again.

 

[markVII]

DELETED/INSETED pseudo-tables are available in triggers context only.
If you need to accomplish this in one statement executed from Query Analyzer, write a stored procedure or create a trigger for INSERT.