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.
| Author |
Topic |
|
sportsguy
Starting Member
39 Posts |
 Posted - 2013-10-07 : 18:42:01
|
Does this look correct for MERGE SYNTAX?I want to INSERT INTO the target table,new service contracts sold with the start date,from historical table by month, only if they don't exist in the target table, doing nothing if matched. . . but the source table is indexed Fiscal Period descending and I have multiple renewal versions of the same contract number in following years. . . MERGE dbo.SERVICE_CONTRACTS_NEW AS Target USING (SELECT r.Customer_Acct, r.[Contract], r.Start_Period FROM dbo.ACTIVE CONTRACTS r ORDER BY r.FiscalPeriod ASC) AS Source ON Target.Account_Nbr = Source.Customer_Acct AND Target.[Contract] = Source.[Contract]WHEN NOT MATCHED BY TARGETTHEN INSERT (Account_Nbr, [Contract], FiscalPeriod_Start) VALUES ( Source.Customer_Acct ,Source.[Contract] ,Source.Start_YYYYMM ); just a newbie looking for affirmation before he makes a mess of a database!thanks in advance!MS Access 20 years, SQL hack |
|
|
sportsguy
Starting Member
39 Posts |
Posted - 2013-10-07 : 19:12:53
|
| Thanks for reading so far. . . I have completely redesigned the process to populate a tmp table first, and then merge from there, as the error message says, one target record can only be updated once, and I had multiple records on a contract. . So, although I didn't see anywhere in the examples where only one record could be updated once, I made that work.However, I put in a bogus update statement when the fields matched, as I don't want to do anything if they match, so is there an option for that?thanks again for reading. . .MS Access 20 years, SQL hack |
 |
|
|
|
|
|
|
|
MERGE with sort order requirements