Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help with Output in a Merge Statement Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 1:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:00 AM
Points: 39, Visits: 174
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.

Post #1492400
Posted Friday, September 6, 2013 2:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
John.Hagen (9/6/2013)
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.



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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492408
Posted Friday, September 6, 2013 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:00 AM
Points: 39, Visits: 174
Mistake post
Post #1492412
Posted Friday, September 6, 2013 2:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:00 AM
Points: 39, Visits: 174
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.
Post #1492414
Posted Friday, September 6, 2013 2:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
Sean Lange (9/6/2013)
John.Hagen (9/6/2013)
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.



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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492416
Posted Friday, September 6, 2013 2:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:00 AM
Points: 39, Visits: 174
That returns 'Invalid column name' for all but the GETDATE() column
Post #1492422
Posted Friday, September 6, 2013 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492426
Posted Friday, September 6, 2013 2:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:00 AM
Points: 39, Visits: 174
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.
Post #1492430
Posted Friday, September 6, 2013 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1492431
Posted Friday, September 6, 2013 3:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:27 PM
Points: 2,127, Visits: 3,216
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1492437
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse