September 6, 2013 at 1:38 pm
Hi Everyone
I have a merge statement which ends with the following
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Now I need to record the deleted records in a table and I am having no luck getting the output to work. The records I need to record look like the following.
OID (int)
Version (int)
QK varchar(32)
GD varchar(32)
Geometry (Geometry)
and I would like to add a date column to record when the delete took place.
September 6, 2013 at 2:04 pm
John.Hagen (9/6/2013)
Hi EveryoneI have a merge statement which ends with the following
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Now I need to record the deleted records in a table and I am having no luck getting the output to work. The records I need to record look like the following.
OID (int)
Version (int)
QK varchar(32)
GD varchar(32)
Geometry (Geometry)
and I would like to add a date column to record when the delete took place.
What do you mean that you are not having any luck getting output to work? I don't see OUTPUT in your query here at all. You can reference the deleted virtual table in a MERGE statement just like a trigger.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 2:10 pm
Mistake post
September 6, 2013 at 2:16 pm
Hi tried a few different ways to do it. Declaring a table, that output into a @table I declared. Once that was done I did an
insert into Table1 (Column1, Columns2, etc) select (Column1, Columns2, etc) from @table where [Action] = 'DELETE'
I could not get all the info I wanted.
September 6, 2013 at 2:20 pm
Sean Lange (9/6/2013)
John.Hagen (9/6/2013)
Hi EveryoneI have a merge statement which ends with the following
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Now I need to record the deleted records in a table and I am having no luck getting the output to work. The records I need to record look like the following.
OID (int)
Version (int)
QK varchar(32)
GD varchar(32)
Geometry (Geometry)
and I would like to add a date column to record when the delete took place.
What do you mean that you are not having any luck getting output to work? I don't see OUTPUT in your query here at all. You can reference the deleted virtual table in a MERGE statement just like a trigger.
???
Did you mean to simply just quote this or did you forget to add something?
Something like this help?
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
deleted.OID,
deleted.Version,
deleted.QK,
deleted.GD,
deleted.Geometry,
GETDATE()
INTO SomeTable
--EDIT--
Forgot to include deleted. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 2:32 pm
That returns 'Invalid column name' for all but the GETDATE() column
September 6, 2013 at 2:40 pm
John.Hagen (9/6/2013)
That returns 'Invalid column name' for all but the GETDATE() column
You have met at an extreme disadvantage here. I have no idea what your table looks like. Those are the columns you stated you wanted to output. Do those columns belong to whatever table you are using as the source for your merge?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 2:54 pm
Ok, adding the deleted in front of that column makes it work. I am getting the records into the tables as needed. It is also entering 10 rows of 'NULL' in every column along with the 7 rows of deleted.
10 rows happens to be the number of records that the merge statement inserts . I tried to get some data for the inserted records by adding to the statement. I received 'Column name or number of supplied values does not match table definition.'
Delete
OUTPUT
DELETED.OID,
DELETED.Version,
DELETED.QK,
DELETED.GD,
DELETED.Geometry
INSERTED.OID,
INSERTED.Version,
INSERTED.QK,
INSERTED.GD,
INSERTED.Geometry
GETDATE()
into table1
It would not hurt to have the inserted values added to the tables.
September 6, 2013 at 3:02 pm
John.Hagen (9/6/2013)
Ok, adding the deleted in front of that column makes it work. I am getting the records into the tables as needed. It is also entering 10 rows of 'NULL' in every column along with the 7 rows of deleted.10 rows happens to be the number of records that the merge statement inserts . I tried to get some data for the inserted records by adding to the statement. I received 'Column name or number of supplied values does not match table definition.'
Delete
OUTPUT
DELETED.OID,
DELETED.Version,
DELETED.QK,
DELETED.GD,
DELETED.Geometry
INSERTED.OID,
INSERTED.Version,
INSERTED.QK,
INSERTED.GD,
INSERTED.Geometry
GETDATE()
into table1
It would not hurt to have the inserted values added to the tables.
That definitely won't work because when you use output it is like creating an insert statement. That means the columns in the output need to match the table that you are inserting into. You could add columns for both inserted and deleted values. If you do that I would consider adding one more column for Action. You can reference that by $Action in the output. That would tell you if it was an insert or a delete.
You should probably spend some time digging through the details of the MERGE statement. http://technet.microsoft.com/en-us/library/bb510625.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 3:26 pm
Just list the column names that are being inserted into the new table; you don't have to supply all columns. For example, see below; naturally your specific column names may be different.
OUTPUT
DELETED.OID,
DELETED.Version,
DELETED.QK,
DELETED.GD,
DELETED.Geometry,
GETDATE()
--,...
into table1 ( OID, Version, QK, GD, Geometry, CaptureDate ) --, ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 9, 2013 at 1:36 pm
Thanks, I was able to get it to work with your help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply