insertion and deletion - 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 2000 Forums

SQL Server Development (2000)

insertion and deletion

Author

Topic

sona
Yak Posting Veteran

68 Posts

Posted - 2002-01-03 : 04:21:35

hai ,

i have to do the following

insert into table2 from (select ... from table1 where ... )
then the exported records have to be deleted
delete from table2 where ...

Is there any way that these two operations can be performed in a single query
(oops......)

monya123
Starting Member

1 Post

Posted - 2002-01-03 : 04:39:48

Hi Sona,

Do u want to delete the exported records from table1 or table2 after the export.....??

Nazim
A custom title

1408 Posts

Posted - 2002-01-03 : 04:58:50

Sona , U want to delete records in a Query!!!.

nah! dont think you can add records and delete them in a single statement.

For that you need to write a stored procedure and execute it , that will make it a single statment

.

Anywayz you can do something like this to delete the inserted records.

insert into table2 from (select ... from table1 where ... )
delete from table1 where primarkey in (select primarkey key in table1)

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Rafiq
Starting Member

25 Posts

Posted - 2002-01-03 : 07:13:01

I don't think it can be done in single statement. The best way is either to use SP or DB Trigger.You can have a flag in table2 for export and update it as one every time a record is exported and delete the records which have the export flag as 1.

Rafi

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-01-03 : 09:38:23

I think what you really want is a transaction. You want the INSERT and the DELETE to both run or you want neither of them to run. That's a transaction. Something like:

BEGIN TRAN
insert into table2 from (select ... from table1 where ... )
delete from table2 where ...
COMMIT TRAN

Just putting them in a stored procedure isn't enough. You need the transaction to wrap around them.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources