February 4, 2013 at 6:54 am
In my stored procedures that change data, I include data change logging. It logs to a table the Table name, the PK value of the table, the field name, and old and new value. So if I ran an update query that updated two fields in a table, two records would be added to the DataChanges table.
Currently, I have to write two queries to accomplish this, because two fields are being updated. Is there a way to have multiple output statements in one query? If not, is there a better way to do this?
UPDATE TableA SET Field1 = 'newValue'
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field1', DELETED.Field1, INSERTED.Field1 INTO DataChanges
WHERE PK_TableA = @pkValue
UPDATE TableA SET Field2 = 'newValue'
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field2', DELETED.Field2, INSERTED.Field2 INTO DataChanges
WHERE PK_TableA = @pkValue
I'm trying to do something similar to this:
UPDATE TableA SET Field1 = 'newValue', Field2 = 'newValue'
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field1', DELETED.Field1, INSERTED.Field1 INTO DataChanges
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field2', DELETED.Field2, INSERTED.Field2 INTO DataChanges
WHERE PK_TableA = @pkValue
Thanks!
February 4, 2013 at 7:05 am
Output into a table variable, then work from there
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2013 at 7:06 am
Okay, but I don't know how to use the output keyword to output more than one row. To me, outputting to a real table or outputting to a table variable means about the same thing!
February 4, 2013 at 7:41 am
greg.senne (2/4/2013)
In my stored procedures that change data, I include data change logging. It logs to a table the Table name, the PK value of the table, the field name, and old and new value. So if I ran an update query that updated two fields in a table, two records would be added to the DataChanges table.Currently, I have to write two queries to accomplish this, because two fields are being updated. Is there a way to have multiple output statements in one query? If not, is there a better way to do this?
UPDATE TableA SET Field1 = 'newValue'
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field1', DELETED.Field1, INSERTED.Field1 INTO DataChanges
WHERE PK_TableA = @pkValue
UPDATE TableA SET Field2 = 'newValue'
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field2', DELETED.Field2, INSERTED.Field2 INTO DataChanges
WHERE PK_TableA = @pkValue
I'm trying to do something similar to this:
UPDATE TableA SET Field1 = 'newValue', Field2 = 'newValue'
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field1', DELETED.Field1, INSERTED.Field1 INTO DataChanges
OUTPUT 'TableA', INSERTED.[PK_UserID], 'Field2', DELETED.Field2, INSERTED.Field2 INTO DataChanges
WHERE PK_TableA = @pkValue
Thanks!
Declare @tableVar as table(field1_new varchar(50),field1_old varchar(50), field2_new varchar(50),field2_old varchar(50), pk_tableA int)
UPDATE TableA SET Field1 = 'newValue', Field2 = 'newValue'
output inserted.field1, deleted.field1, inserted.field2, deleted.field2, @pkvalue into @tablevar(field1_new ,
field1_old ,field2_new ,field2_old ,pk_tableA
)
where pk_tableA=@pk_value
That should capture the records in table variable; and then you can use it for insert in the table..
output keyword to output more than one row
While using output keyword it works for all the inserted , updated(insert/delete) or deleted rows for that particular batch, there is no extra effort...
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 4, 2013 at 8:06 am
greg.senne (2/4/2013)
Okay, but I don't know how to use the output keyword to output more than one row.
The output keyword will output as many rows as the operation affected. Insert 4 rows, the output will output 4 rows. Delete 300 rows, the output will output 300 rows.. That's default behaviour.
To me, outputting to a real table or outputting to a table variable means about the same thing!
They pretty much are
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2013 at 8:12 am
demonfox, Thanks. That makes sense.
GilaMonster (2/4/2013)
The output keyword will output as many rows as the operation affected. Insert 4 rows, the output will output 4 rows. Delete 300 rows, the output will output 300 rows.. That's default behaviour.
I understand that. I was referring to outputting multiple rows per row affected.
I think I'll create the table variable with the columns affected, and be able to create my insert statements from that.
Thanks.
February 4, 2013 at 8:14 am
greg.senne (2/4/2013)
GilaMonster (2/4/2013)
The output keyword will output as many rows as the operation affected. Insert 4 rows, the output will output 4 rows. Delete 300 rows, the output will output 300 rows.. That's default behaviour.I understand that. I was referring to outputting multiple rows per row affected.
You can't do that directly, hence why I suggested insert the raw output into a table variable and do further processing from there
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2013 at 8:17 am
That's what I'll do. Thanks for the help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply