quote:

---

Originally posted by jooorj

but in One statement ! I need

---

begin tran
insert into  yourTargetTable select col1,col2..coln from yourSourceTable;
delete from yourSourceTable
commit tran

delete from yourSourceTable
output deleted.col1, deleted.col2,...,deleted.colN into yourTargetTable

quote:

---

you can wrap the delete and insert into a transaction.
That would guarantee that either both operations succeed, or neither operation is performed.It would be something like this

begin tran
insert into yourTargetTable select col1,col2..coln from yourSourceTable;
delete from yourSourceTable
commit tran

---

begin try
  begin transaction
  insert into  yourTargetTable select col1,col2..coln from yourSourceTable;
  delete from yourSourceTable
  commit transaction
end try
begin catch
  rollback transaction
end catch

create table dbo.TA (id int);
create table dbo.TB (id int);

insert into TA values (1);
insert into TA values (2);
insert into TA values (3);

delete from TA
output deleted.id into TB
where id > 2;

select * from TB;

drop table TA;
drop table TB;

quote:

---

Originally posted by GilaMonster

Just note that the OUTPUT ... INTO <permanent table> is SQL 2008+ only.

quote:

---

you can wrap the delete and insert into a transaction.
That would guarantee that either both operations succeed, or neither operation is performed.It would be something like this

begin tran
insert into yourTargetTable select col1,col2..coln from yourSourceTable;
delete from yourSourceTable
commit tran

---

With that code, the insert could fail, the delete succeed and the transaction commit. It both or neither is required, error handling is needed.

Simplified code:

begin try
  begin transaction
  insert into  yourTargetTable select col1,col2..coln from yourSourceTable;
  delete from yourSourceTable
  commit transaction
end try
begin catch
  rollback transaction
end catch

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by sunitabeck

If you don't, I think the risk is not that the first statement will be committed and the second won't; rather it is that the transaction will be left open indefinitely, and Jooorg will be scrathing his head why he can't even do a select from that table!!

---

 CREATE TABLE #TestingTransactions (
 ID INT UNIQUE NOT NULL
);

BEGIN TRANSACTION

INSERT INTO #TestingTransactions (ID)
VALUES (1) -- succeeds

INSERT INTO #TestingTransactions (ID)
VALUES (NULL) -- fails. Null into a not-null column

INSERT INTO #TestingTransactions (ID)
VALUES (2) -- succeeds

INSERT INTO #TestingTransactions (ID)
VALUES (1) -- fails. Unique key violation

INSERT INTO #TestingTransactions (ID)
VALUES (3) -- succeeds

COMMIT TRANSACTION

SELECT * FROM #TestingTransactions

quote:

---

Originally posted by sunitabeck

I could have sworn that the output feature was part of SQL 2005. On my SQL 2005 SP2, it does seem to work.

---

quote:

---

Edit: my version is 9.00.1399.06, Build 2600, Service Pack 2.

---

quote:

---

Originally posted by GilaMonster


How many rows do you think that select will return?

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by GilaMonster

That's RTM of SQL 2005. No service packs. You may want to apply SP4.

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by sunitabeck

You are absolutely right. It generates the exception, and goes right past the exception to the next statement as though "Meh! what is a silly exception while I have more things to do!!"

This is very different from the .Net world where if an exception thrown, execution stops at that point.

---