Need to copy data from table to table with update statement

Question

Post reply

Need to copy data from table to table with update statement

Viewing 12 posts - 16 through 26 (of 26 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Pat,

What this all kinda boils down to, is that every solution here does exactly the same thing (assuming nobody typo'd an alias), it just shows different styles of doing it. The old style syntax (and Jeff's more modern version) also incorporates a derived table to join two of the tables, and then joins that to the first one. I'd be willing to bet that as far as the optimizer is concerned, that's exactly the same as just joining all the tables together in the parent query.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Garadin (12/10/2008)

Egad I hate those commas on the left.:hehe:

(I know I know, it makes it easier to comment out lines and such, but I still hate it)

I do put each column in the SELECT list on a separate line if it's going to be a long list, or if I'm doing concatenation/casting etc.

Ack! You hit a tender spot... leading commas do NOT make it easier to comment out code. Leading commas and trailing commas have the same simple ability for all rows that are not the first nor last row. Leading commas make it easier to comment out just the last row... trailing commas make it easier to comment out the first row.

It's just a personal opinion, but leading commas are a real eye sore to me.

--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)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

Jeff Moden (12/10/2008)

Garadin (12/10/2008)

Egad I hate those commas on the left.:hehe:

(I know I know, it makes it easier to comment out lines and such, but I still hate it)

I do put each column in the SELECT list on a separate line if it's going to be a long list, or if I'm doing concatenation/casting etc.

Ack! You hit a tender spot... leading commas do NOT make it easier to comment out code. Leading commas and trailing commas have the same simple ability for all rows that are not the first nor last row. Leading commas make it easier to comment out just the last row... trailing commas make it easier to comment out the first row.

It's just a personal opinion, but leading commas are a real eye sore to me.

Uh oh - looks like this might just devolve into a style war. :hehe:

My personal preference is leading commas - but, that is just for now. I will probably change that when I find a style I like better 😉

What really bothers me the most is when I'm looking at code that is mixed. You know, one programmer liked leading commas, the next one liked trailing commas, and of course there was that one guy who liked putting everything he could fit on a single line - and none of them could keep anything in any relative alignment.

I am so glad I have a tool that will reformat SQL code with a simple Ctrl-F11 combo...

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 4

Steve Jones - SSC Editor

SSC Guru

Points: 728159

December 10, 2008 at 7:22 pm

#910772

I think it's personal preference. I'm a leading comma guy.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Nope... no style wars... I'm just tired of people blaming it on the ability to comment out rows easier. The difference between the two styles only affects the ease in which either the first or last item in a Select list are commented out. If you trully wanted to make it universally easy, the comma would always be on a separate line. 😉 I've actually seen shops that do that, but I think the code is butt ugly.

This is why coding and style standards within an organization are important. Everyone has their own way of doing things... if you want the code to look the same in a given shop, you must have such standards and.... enforce them.

Heh... I think it's a bit odd that anyone would write code to make commenting it out easy when they refuse to comment code to make it easy to troubleshoot. 😉

--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)
Intro to Tally Tables and Functions

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

Jeff Moden (12/11/2008)

Heh... I think it's a bit odd that anyone would write code to make commenting it out easy when they refuse to comment code to make it easy to troubleshoot. 😉

Nice one, Jeff. Another signature gem. 😎

^{“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

Garadin One Orange Chip Points: 29613 More actions · Answer 7

You know, I never really stopped to think about it, but you're right, it doesn't even make commenting out easier. I do think it makes it a little easier to see where you're missing a comma, but if my biggest problem on a query is a missing comma, I figure I'm in pretty good shape, and I'd rather not have my code look crappy to save me 10 seconds. (sorry leading comma guys, I know it's just a personal preference, but it's a strong one 😛 )

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4553 More actions · Answer 8

pbreitenbeck Posted Dec 10, 2008 @ 4:47 PM

SSC Veteran -

I basically understand everything in the block of code below, except the last two lines. I understand that we are first doing a SELECT query in paranthesis, but what does the last line do? The only thing I can figure is that you are re-assigning the alias of b to the resultset returned by the select query in parenthesis. So, alias b no longer refers to the LeaseInfoImport table. Is this correct?

/* last line of code*/

b where a.machineID = b.machineID

/* original code */

update MachineAudit set leaseNumber = b.leaseNumber

from MachineAudit a,

(select a.machineID, b.leaseNumber from MachineName a, LeaseInfoImport b

where a.machineName = b.computerName) b

where a.machineID = b.machineID

1. You are right pbreitenbeck. alias b in the last line no longer refers to the LeaseInfoImport table. You can also assign a new alias also for the subquery in paranthesis if you like. I used subquery method to break the soluton in parts in order to to be able to anyalyze better and come to the final result set step by step.

2. As Jeffrey Williams said I am also omitting the 'Inner Join' and 'ON' clause. I just feel it convenient and it works fine. In fact I am used to it as I am using this way since the 'Inner Join' clause was not part of the SQL syntax.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Chris Morris (12/11/2008)

Jeff Moden (12/11/2008)

Heh... I think it's a bit odd that anyone would write code to make commenting it out easy when they refuse to comment code to make it easy to troubleshoot. 😉

Nice one, Jeff. Another signature gem. 😎

Heh... I guess I should start writing some of those beaut's in a book somewhere, huh? Thanks, Chris. 🙂

--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)
Intro to Tally Tables and Functions

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

Garadin (12/11/2008)

You know, I never really stopped to think about it, but you're right, it doesn't even make commenting out easier. I do think it makes it a little easier to see where you're missing a comma, but if my biggest problem on a query is a missing comma, I figure I'm in pretty good shape, and I'd rather not have my code look crappy to save me 10 seconds. (sorry leading comma guys, I know it's just a personal preference, but it's a strong one 😛 )

BWAA-HAAA!! A kindred spirit! I pray that the only thing wrong with my code is the occasional missing or extra comma! And since I promised not to start a holy war about leading commas, I quit at that. 🙂

.

(but I whole heartedly agree :P)

--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)
Intro to Tally Tables and Functions

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 11

Steve Jones - SSC Editor

SSC Guru

Points: 728159

December 12, 2008 at 7:36 am

#911640

Nice one Jeff, you should write those down. I'm not convinced, but that's a good quote.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

J-440512 SSCertifiable Points: 6285 More actions · Answer 12

Leading commas ...

Always hated them myself but did not condemn the style, it did LOOK easier to comment out some lines while debugging.

Not that this was a problem with trailing commas, all I had to do was leave the LAST item active (not commented out) in the select statement.

Funny, I never thunk about it: leading commas will require that the FIRST column in the SRELECT list be always active (not commented out).

So I will stick with trailing commas, they look more natural than leading commas, which actually offer no practical advantage.