Merge with duplicate rows

Question

Merge with duplicate rows

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

SQL-Squid Default port Points: 1472 More actions · Answer 1

I am keeping the insert/update/delete routine in place for now until they give me data with a PK. I agree uniqueness is needed to properly work with the data. Thanks again and have a great day!

hominamad SSC Veteran Points: 285 More actions · Answer 2

Hi - I have a similar issue to the one discussed here. Wasn't sure if I should create a new thread or not, first I'll try reviving this one.

I also have a situation where my source data has multiple rows - but the behavior I would like to happen is that my target rows get expanded - i.e. rows get inserted for each duplicate.

I basically want it to be have the same behavior as if I was joining the two tables together.

For anyone interested - here is my use case:

My target table is data which originates from a feed we download from an external source. My source data is a table that we control. There is a common identifier in both tables and I am trying to populate IDs and other data from our table into the target table. The thing is, in our source table it is a valid scenario that there could be multiple records with the same identifier. Specifically, some of those records may be "active" and others "inactive" and we need to see all. If my target table has one record that matches 3 records in the source table, I want to end up with 3 records in my target.

Is there a way this can be accomplished using a merge? I can think of other ways to do it, like joining and inserting into a new table, etc. but I'm hoping there is a slicker way to do it - maybe using MERGE.

Thanks,

H

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You should start a new thread for this, and perhaps include the action you would like to take depending upon the distribution of ID's between the two tables. For instance, define what you plan to do if there's:

One row in each table matching on ID - update, delete & insert, or ignore;

One row in source and two rows in target, matching on ID;

Three rows in source and two rows in target, matching on ID;

No match in target for a source row;

No match in source for a target row.

You're likely to get a better answer if you can provide the structures of the tables with some readily-consumable sample data for folks to code against - if you're not sure how to do this, there's a link in my sig to a forum etiquette article which demonstrates.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden