Insert into Select - only new or changed rows

Question

Insert into Select - only new or changed rows

gjoelson 29755

SSC Eights!

Points: 848
More actions
September 7, 2018 at 9:10 am

#408363

Good morning,
I have a "insert into select" script that copies records from a database I don't have full access to, to a staging table. The script seems to work fine except I'm having trouble getting only new or changed rows. any suggestion on the best way to go about this ? my script is attached.

Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you do an outer join between the source table and the target table, you can insert only the rows that don't already exist.

John

drew.allen SSC Guru Points: 76996 More actions · Answer 2

Do you really want to insert changed rows? Usually people want to update changed rows and insert new rows. As for identifying which are new/changed, I like EXCEPT unless your table has only a few columns.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 3

Thanks Guys...True I wont want to insert changed row should I do a merge ?
I was hoping to keep the script as is...the outer join takes me away from that.

Jackie Lowery SSCommitted Points: 1889 More actions · Answer 4

gjoelson 29755 - Friday, September 7, 2018 9:10 AM
Good morning,
I have a "insert into select" script that copies records from a database I don't have full access to, to a staging table. The script seems to work fine except I'm having trouble getting only new or changed rows. any suggestion on the best way to go about this ? my script is attached.

Thank you

Are your co_num in ascending order? Maybe you could add something like this to your select?

AND co_num > (select max(co_num) from customerorders)

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 5

gjoelson 29755 - Friday, September 7, 2018 9:28 AM
Thanks Guys...True I wont want to insert changed row should I do a merge ?
I was hoping to keep the script as is...the outer join takes me away from that.

Merge is slower than an UPDATE followed by an INSERT. So you should really have two statements to do it.
What column(s) do you match on to do an UPDATE or an INSERT?