Deleted Data in two different tables...

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)

Deleted Data in two different tables...

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-15 : 06:50:54

Hello, every one i need to insert deleted data in two different table.
Is this possible to do it in a single query
for ex.

Delete A
OUTPUT deleted.col1, deleted.col2
INTO t1(col1,col2)
FROM tab1
where col1 like '%aaa%'

but i want to use it like

Delete A
OUTPUT deleted.col1, deleted.col2
INTO t1(col1,col2)

OUTPUT deleted.col1, 'd'
INTO t2(col1,col2)

FROM tab1
where col1 like '%aaa%'

Is this possible ??

Please suggest me so that i can accomodate the code in my existing query
other wise i will have to insert it seperately

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-15 : 07:39:55

No, insert it into a table variable and then into each table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 10:01:34

Or create a trigger which can do it transparently for you

CREATE TRIGGER	dbo.trgMyTrigger
ON		dbo.Tab1
AFTER		DELETE
AS

SET NOCOUNT ON

INSERT	t2
	(
		Col1,
		Col2
	)
SELECT	Col1,
	'd'
FROM	deleted

INSERT	t1
	(
		Col1,
		Col2
	)
SELECT	Col1,
	Col2
FROM	deleted

N 56°04'39.26"
E 12°55'05.63"

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-15 : 10:42:05

Thank you very much for your replies.

I have been used RickD's Solution

Peso:
in our database we are not allowed to use triggers on tables.
I dont know the reason why do not use this rich feature of SQL Server.
Might be our database is so much bulky thats why....

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources