Insert Into problems

Question

Insert Into problems

Viewing 15 posts - 16 through 30 (of 34 total)

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 1

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

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 2

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.

marlon.seton SSCrazy Points: 2623 More actions · Answer 3

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.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

November 29, 2011 at 7:00 am

#1414038

@Tol, I like her even better that way 😀

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 6

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,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 7

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/

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 8

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,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 9

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

November 29, 2011 at 8:39 am

#1414140

thanks for the questions

Revenant SSC-Forever Points: 42467 More actions · Answer 10

Thanks for the question -- it was a really easy one!

Ken SSCoach Points: 17191 More actions · Answer 11

I too was looking for a catch. Good back to basics question. Thanks!

Mattrick Ten Centuries Points: 1176 More actions · Answer 12

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

T_Peters Hall of Fame Points: 3052 More actions · Answer 13

Thanks for the question HESPO, good to see you again.

--TPET

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 14

Iulian -207023

SSCertifiable

Points: 7639

November 29, 2011 at 1:31 pm

#1414321

Thank you for the question

Iulian

Henrik Staun Poulsen SSCertifiable Points: 6551 More actions · Answer 15

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