Insert Into problems
-
Hugo Kornelis (11/29/2011)
Here's the way I write INSERT statements, to make it easier to say which expression in the SELECT list matches which column in the INSERT list:INSERT INTO #ATable(col1, col2, col3)SELECT 2, 1, 3;(Where I change the column positions based on the length of the column names and expressions in the SELECT list).
I usually limit myself to three columns per line, because I don't like to scroll horizontally. So when there's a long column list, I use this style:
INSERT INTO #BTable(col1, col2, col3,col4, col5, col6,col7, col8)SELECT 2, 1, 3,4, 5, 6,7, 8;I prefer this method as well. It makes it easier for the next person, or myself, to maintain the code. I work with a programmer that runs the query text out past the edge of a reasonable editor and uses all lower case. That makes it very hard to read.
To the OP: Thanks for the question! It was easy, but a good question.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
I put to much though into that one at first because I though it was going to be a collate issue with the INT vs int.
In the end, nice and simple question.
-
What he said.
Making it obvious what's going where is, IMHO, really important. Similarly, I try hard to avoid scrolling past 80 characters (as that seems to be a default for things like command line editing on Unix).
Thomas Abraham (11/29/2011)
Hugo Kornelis (11/29/2011)
Here's the way I write INSERT statements, to make it easier to say which expression in the SELECT list matches which column in the INSERT list:INSERT INTO #ATable(col1, col2, col3)SELECT 2, 1, 3;(Where I change the column positions based on the length of the column names and expressions in the SELECT list).
I usually limit myself to three columns per line, because I don't like to scroll horizontally. So when there's a long column list, I use this style:
INSERT INTO #BTable(col1, col2, col3,col4, col5, col6,col7, col8)SELECT 2, 1, 3,4, 5, 6,7, 8;I prefer this method as well. It makes it easier for the next person, or myself, to maintain the code. I work with a programmer that runs the query text out past the edge of a reasonable editor and uses all lower case. That makes it very hard to read.
To the OP: Thanks for the question! It was easy, but a good question.
-
Good question.
It caught me, although it most certainly shouldn't have :blush:. Guess I shouldn't try to understand SQL until the hangover subsides :doze:
Tom
-
@Tol, I like her even better that way
-
Hugo Kornelis (11/29/2011)
INSERT INTO #ATable(col1, col2, col3)
SELECT 2, 1, 3;(Where I change the column positions based on the length of the column names and expressions in the SELECT list).
I usually limit myself to three columns per line, because I don't like to scroll horizontally. So when there's a long column list, I use this style:
INSERT INTO #BTable(col1, col2, col3,col4, col5, col6,col7, col8)SELECT 2, 1, 3,4, 5, 6,7, 8;That's a great advice. In my day job, I can see, in many codes, a long list of columns without proper formatting and it gets harder and harder to read once the table's structure changes and you need to update code.
I usually code my INSERTs like this whenever possible:
INSERT INTO #ATable(Col1,Col2,Col3)VALUES(2 AS Col1,1 AS Col2,3 AS Col3);I'm not sure if it's a good way of doing it but it looks clear to me.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA -
codebyo (11/29/2011)
I usually code my INSERTs like this whenever possible:INSERT INTO #ATable(Col1,Col2,Col3)VALUES(2 AS Col1,1 AS Col2,3 AS Col3);I'm not sure if it's a good way of doing it but it looks clear to me.
I'm not really too fond about the aliases; I think it'd be better to use comments - as that is the only function the alias serves anyway.
Other than that, this method is fine - as long as the comments and the actual columns match up. But what happens if a change is made somewhere and the comments are not updated? If that happens a few time, developers will end up ignoring the column names in the comments (or aliases), as they know that those aren't always reliable - and than it's back to counting the 23rd column name from the list. :crying:
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Thanks for the comments, Hugo.
I will keep that in mind.
We have a lot denormalized tables with 100+ columns and it's a major pain to maintain their DML.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA -
thanks for the questions
-
Thanks for the question -- it was a really easy one!
-
I too was looking for a catch. Good back to basics question. Thanks!
-
Good question. Something like this can trip anyone up. I'm sorry that it took a few hours to recognize the mistake, but we have all been there at some point. I can recall pouring over code only to find a colon where a semi-colon was required.
Thanks,
Matt
-
Thanks for the question HESPO, good to see you again.
--TPET
-
Thank you for the question
Iulian
-
Thank you all, for the nice words.
A special "thank you" goes to Hugo, for the suggestion for formatting the fields in 3 columns, or how-ever many columns that fit onto one screen.
Best regards,
Henrik
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply