TheHiveMind
Banned
Here is what I am trying to do:
I am collecting audit data from a table.
This data is event specific and will be used to track the "status" of each event.
For example, let us say we have an event that has 4 parts.
1-Initial Received
2-Initial Sent
3-Response Received
4-Response Sent
If it is state 1, the record has 1/4 of the information
if it is state 2, the record has 1/2 of the information
if it is state 3, the record has 3/4 of the information
if it is state 4, the record has 4/4 of the information and is completed
It does this by backtracing. If we have a state 4, we will backtrace to level 1.
These records use the same "keys".
The idea is that the destination table always has the latest "state" of each record.
I want to insert a record if one does not exist, and update a record if it does exist.
This is called upserting.
Sql Server has a merge command, but it does not seem to be working as expected.
I am bulk inserting into a temp table, and that temp itself has duplicates, although it has no key. When trying to merge the two tables to upsert the data, I get primary key duplication errors.
Let's say the destination table has a record of state 1.
The temp table has that record for state 2,3 and 4.
I want the destination table to reflect the state 4 after the merge.
I would expect it, when merging, to update to state 2, then 3, then 4.
Is the problem with the way the merge works? I understand that it can merge duplicates between two tables. But can it also handle duplicates within the source table itself?
I am collecting audit data from a table.
This data is event specific and will be used to track the "status" of each event.
For example, let us say we have an event that has 4 parts.
1-Initial Received
2-Initial Sent
3-Response Received
4-Response Sent
If it is state 1, the record has 1/4 of the information
if it is state 2, the record has 1/2 of the information
if it is state 3, the record has 3/4 of the information
if it is state 4, the record has 4/4 of the information and is completed
It does this by backtracing. If we have a state 4, we will backtrace to level 1.
These records use the same "keys".
The idea is that the destination table always has the latest "state" of each record.
I want to insert a record if one does not exist, and update a record if it does exist.
This is called upserting.
Sql Server has a merge command, but it does not seem to be working as expected.
I am bulk inserting into a temp table, and that temp itself has duplicates, although it has no key. When trying to merge the two tables to upsert the data, I get primary key duplication errors.
Let's say the destination table has a record of state 1.
The temp table has that record for state 2,3 and 4.
I want the destination table to reflect the state 4 after the merge.
I would expect it, when merging, to update to state 2, then 3, then 4.
Is the problem with the way the merge works? I understand that it can merge duplicates between two tables. But can it also handle duplicates within the source table itself?