April 7, 2012 at 2:09 pm
I have a Merge Statement that uses an OPEN QUERY Statement.
I have added a Date_Inserted Column to every table.
I need to pass in a DateTime Variable that is the same for every record inserted into the Table.
How can I accomplish this?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2012 at 2:27 pm
Trick question. If this is the data/time a record is inserted, why not leave it out of the MERGE entirely and set a default constraint value for the current date/time? I would think that would be the easiest way to accomplish this task.
April 7, 2012 at 2:38 pm
Lynn Pettis (4/7/2012)
Trick question. If this is the data/time a record is inserted, why not leave it out of the MERGE entirely and set a default constraint value for the current date/time? I would think that would be the easiest way to accomplish this task.
Thank you for responding!:-)
I wish that I could just set a default constraint but I'm doing an incremental load to staging.
I want the date to be the same for all of the records. The load could start at night and carry on into the morning of the next day.
The intent is to capture the load datetime of when the batch started.
I will be using SSIS.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2012 at 10:46 pm
Lynn Pettis (4/7/2012)
Trick question. If this is the data/time a record is inserted, why not leave it out of the MERGE entirely and set a default constraint value for the current date/time? I would think that would be the easiest way to accomplish this task.
OK, I'm with you on this. I'm also thinking that if I added a Variable in SQL for DML Operation (INSERT,UPDATE or DELETE) to every record I could track how many records were Inserted, Update or Deleted.
I'm not sure how I can do this with OPENQUERY Syntax?
The current code for the Delete is:
when not matched by source then delete;
I would need to change this to Update the column to 'D' for Delete as opposed to deleting the records.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 8, 2012 at 5:46 pm
The "GETDATE()" or "CURRENT_TIMESTAMP" will give you the same value for every row in a single statement. As long as your MERGE statement is a single statement, all records will get the same value using one of the methods mentioned above.
April 8, 2012 at 5:49 pm
Lynn Pettis (4/7/2012)
Trick question. If this is the data/time a record is inserted, why not leave it out of the MERGE entirely and set a default constraint value for the current date/time? I would think that would be the easiest way to accomplish this task.
This is for an incremental load.
If NOT Matched THEN Insert.
IF Matched then UPDATE...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy