Using "Update" or "Merge" on 2014 SQL table

Question

Using "Update" or "Merge" on 2014 SQL table

alex_martinez

SSCrazy

Points: 2144
More actions
September 19, 2016 at 10:45 am

#330302

Hello,
I am using SQL Server 2014 and I want to update all the columns in the table based on two unique columns from another table (AS400 table):
For example column 1 "Sequence" and column 2 "Type":
Sequence Type Name Address City State Zip
777 B James Hamilton 123 Main St. Encino Ca 91411
777 S James Hamilton 123 Main St. Encino Ca 91411
There will always be duplicate Sequence number, but the type will be unique the comination of the two columns will always be unique. If there are no Sequence number 777 in my SQL table, but the AS400 table has it I want to update the Sql table to include Sequence 777 and it's respective Type "B" or "S" alone with the other columns are Name, Address, City State, Zip in another words all the columns will be populated. The table is call "CustTicket". Also, I was wondering if I can you a "Merge" In any case I have never have the need to use an "Update" or "Merge" until now. if anyone can assist me with the coding I will appreciate the help. Thank you in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

The actions UPDATE, INSERT, and DELETE refer to the rows in the table, not the table itself. If the row doesn't already exist within a table, you cannot update it. You want to insert it instead.

Other than that, this situation is commonplace, and you should be able to find the answer to your question by a simple search in your favorite search engine.

If you're still having problems. Show us what you've tried and we can help you with it.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

alex_martinez SSCrazy Points: 2144 More actions · Answer 2

alex_martinez

SSCrazy

Points: 2144

September 19, 2016 at 1:13 pm

#1901729

Thank you Drew

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

If you haven't read up on the MERGE operation in TSQL, be sure to do so. It allows you to insert and update (and d elete if need be) in a single query. Look at the sample queries HERE and try to adapt them to your issue.

Hint: Example A is just what you're trying to do: Update where a row exists, and insert where a row doesn't exist.

If you have any questions, post your code up here and we'll have a look at it.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

The Dixie Flatline (9/23/2016)
If you haven't read up on the MERGE operation in TSQL, be sure to do so. It allows you to insert and update (and d elete if need be) in a single query. Look at the sample queries HERE and try to adapt them to your issue.
Hint: Example A is just what you're trying to do: Update where a row exists, and insert where a row doesn't exist.
If you have any questions, post your code up here and we'll have a look at it.

I prefer the individual commands rather than Merge. There have been too many problems with it for me to trust it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Jeff, I have to agree that if you're trying to do a large number of rows in a single merge, you need to jump through some hoops to set up a merge join.

But for individual transactions, I've never run into the problems you mentioned.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

The Dixie Flatline (9/26/2016)
Jeff, I have to agree that if you're trying to do a large number of rows in a single merge, you need to jump through some hoops to set up a merge join.
But for individual transactions, I've never run into the problems you mentioned.

That's good to know. Thanks, Bob.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

For what it's worth, we haven't gone back and rewritten everything as merges, either 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

alex_martinez SSCrazy Points: 2144 More actions · Answer 8

Thanks Dixie it's what I am really looking for. By chance it's only about 10 - 20 rows per day to insert.

alex_martinez SSCrazy Points: 2144 More actions · Answer 9

Jeff,

By chance it's only about 10 - 20 rows per day to insert so that shouldn't be an issue. Thank you.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

alex_martinez (9/26/2016)
Jeff,
By chance it's only about 10 - 20 rows per day to insert so that shouldn't be an issue. Thank you.

What are the ramifications if those 1-20 rows per day are incorrect? 😉

Just a suggestion... Never justify possible lack of accuracy or performance on a low row count even if you're in a hurry. Even if you can guarantee such low row counts over the entire life of the code (and most people can't), someone in a pinch may pick up on your code and use it for something much larger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)