SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Henrik Staun Polsen / Insert Into problems / Latest Posts

RE: Insert Into problems

Danny Ocean — Mon, 19 Dec 2011 05:34:01 GMT

good one. Learn new thing !!!

RE: Insert Into problems

Danny Ocean — Mon, 19 Dec 2011 04:04:29 GMT

Thank for question, but it's easy.

RE: Insert Into problems

amit_adarsh — Wed, 07 Dec 2011 02:52:12 GMT

Simple but Nice one ..

RE: Insert Into problems

malleswarareddy_m — Tue, 06 Dec 2011 23:13:21 GMT

Good Question.I got it correct .I realised this issue long back when am working with insert statements due to mismatch columns.some it will throw exceptions if data type is not macthing while inserting.

RE: Insert Into problems

Britt Cluff — Wed, 30 Nov 2011 07:26:55 GMT

Good question, thanks for submitting.

RE: Insert Into problems

henrik staun poulsen — Wed, 30 Nov 2011 01:48:21 GMT

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

RE: Insert Into problems

Iulian -207023 — Tue, 29 Nov 2011 13:31:24 GMT

Thank you for the questionIulian

RE: Insert Into problems

T_Peters — Tue, 29 Nov 2011 13:29:44 GMT

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

RE: Insert Into problems

Mattrick — Tue, 29 Nov 2011 10:48:18 GMT

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

RE: Insert Into problems

KWymore — Tue, 29 Nov 2011 09:53:55 GMT

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

RE: Insert Into problems

Revenant — Tue, 29 Nov 2011 08:57:47 GMT

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

RE: Insert Into problems

OzYbOi d(-_-)b — Tue, 29 Nov 2011 08:39:34 GMT

thanks for the questions

RE: Insert Into problems

codebyo — Tue, 29 Nov 2011 08:16:08 GMT

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,

RE: Insert Into problems

Hugo Kornelis — Tue, 29 Nov 2011 07:58:33 GMT

[quote][b]codebyo (11/29/2011)[/b][hr]I usually code my INSERTs like this whenever possible:[code="sql"]INSERT INTO #ATable( Col1, Col2, Col3)VALUES( 2 AS Col1, 1 AS Col2, 3 AS Col3);[/code]I'm not sure if it's a good way of doing it but it looks clear to me. :-)[/quote]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:

RE: Insert Into problems

codebyo — Tue, 29 Nov 2011 07:50:01 GMT

[quote][b]Hugo Kornelis (11/29/2011)[/b][hr][code="sql"]INSERT INTO #ATable (col1, col2, col3)SELECT 2, 1, 3;[/code](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:[code="sql"]INSERT INTO #BTable (col1, col2, col3, col4, col5, col6, col7, col8)SELECT 2, 1, 3, 4, 5, 6, 7, 8;[/code][/quote]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:[code="sql"]INSERT INTO #ATable( Col1, Col2, Col3)VALUES( 2 AS Col1, 1 AS Col2, 3 AS Col3);[/code]I'm not sure if it's a good way of doing it but it looks clear to me. :-)Best regards,

RE: Insert Into problems

Ninja's_RGR'us — Tue, 29 Nov 2011 07:00:57 GMT

@Tol, I like her even better that way :-D

RE: Insert Into problems

L' Eomot Inversé — Tue, 29 Nov 2011 06:57:34 GMT

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:

RE: Insert Into problems

marlon.seton — Tue, 29 Nov 2011 06:53:18 GMT

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).[quote][b]Thomas Abraham (11/29/2011)[/b][hr][quote][b]Hugo Kornelis (11/29/2011)[/b][hr]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:[code="sql"]INSERT INTO #ATable (col1, col2, col3)SELECT 2, 1, 3;[/code](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:[code="sql"]INSERT INTO #BTable (col1, col2, col3, col4, col5, col6, col7, col8)SELECT 2, 1, 3, 4, 5, 6, 7, 8;[/code][/quote]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.[/quote]

RE: Insert Into problems

mbova407 — Tue, 29 Nov 2011 06:26:44 GMT

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.

RE: Insert Into problems

Thomas Abraham — Tue, 29 Nov 2011 05:49:28 GMT

[quote][b]Hugo Kornelis (11/29/2011)[/b][hr]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:[code="sql"]INSERT INTO #ATable (col1, col2, col3)SELECT 2, 1, 3;[/code](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:[code="sql"]INSERT INTO #BTable (col1, col2, col3, col4, col5, col6, col7, col8)SELECT 2, 1, 3, 4, 5, 6, 7, 8;[/code][/quote]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.

RE: Insert Into problems

Hugo Kornelis — Tue, 29 Nov 2011 05:23:04 GMT

Nice question. Not terribly hard, but interesting. It could have been made a bit harder by omitting the column list in the INSERT:[code="sql"]INSERT INTO #ATableSELECT 2 AS col2, 1 AS col1, 3 AS col3[/code]For the record, I am not condoning the above as good practice. In my book, both omitting the column list of the INSERT statement and adding aliases to the SELECT list of an INSERT ... SELECT are bad practices.A final remark - I don't see how the version in the explanation, that uses comments instead of aliases, is any clearer than the original. The column names in the comments don't match reality, so these comments are obfuscating the code instead of clarifying it.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:[code="sql"]INSERT INTO #ATable (col1, col2, col3)SELECT 2, 1, 3;[/code](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:[code="sql"]INSERT INTO #BTable (col1, col2, col3, col4, col5, col6, col7, col8)SELECT 2, 1, 3, 4, 5, 6, 7, 8;[/code]

RE: Insert Into problems

rajucse.kumar — Tue, 29 Nov 2011 05:19:36 GMT

It is simply can be vieved, value inserted in respective columns will be in column order ...

RE: Insert Into problems

henrik staun poulsen — Tue, 29 Nov 2011 05:00:22 GMT

well, it was a bug...In my own code!I know; I created it myself.Easy for me to fix, as soon as I noticed that column order was important.It is a bit like this problem in SSIS:Will this round or truncate?[code="other"](DT_I4)(SpeedIntervalFrom / 0.5) [/code]It is an expression in a Derived column component.Well, it should have been [code="other"](DT_I4)FLOOR(SpeedIntervalFrom / 0.5) [/code]because we wanted it to truncate.

RE: Insert Into problems

rfr.ferrari — Tue, 29 Nov 2011 04:15:20 GMT

thanks for question!

RE: Insert Into problems

Koen Verbeeck — Tue, 29 Nov 2011 03:08:07 GMT

[quote][b]Nic-306421 (11/29/2011)[/b][hr][quote]I've spent a few hours, before I noticed the bug.[/quote]It may just be me, but I wouldn't class this a bug...[/quote]It's not just you ;-)

RE: Insert Into problems

Nic-306421 — Tue, 29 Nov 2011 02:53:12 GMT

[quote]I've spent a few hours, before I noticed the bug.[/quote]It may just be me, but I wouldn't class this a bug, if that's what are you are saying, the AS is (as has been stated in this post) for the alias, it has no bearing on the order of the insert.None the less a fun question, so thank you.Nic

RE: Insert Into problems

sharath.chalamgari — Tue, 29 Nov 2011 02:41:26 GMT

Simple One........

RE: Insert Into problems

henrik staun poulsen — Tue, 29 Nov 2011 01:56:42 GMT

Well, since I wrote it up as a QotD, you may not need three guesses as to who went into this "trap" big time.I've spent a few hours, before I noticed the bug.

RE: Insert Into problems

Ninja's_RGR'us — Tue, 29 Nov 2011 01:55:08 GMT

[quote][b]Koen Verbeeck (11/28/2011)[/b][hr](and I was looking frantically for 5 minutes to find the "catch". :-D)[/quote]Glad to see I had so much effects on you all :hehe:.

RE: Insert Into problems

paul s-306273 — Tue, 29 Nov 2011 01:49:14 GMT

So QOTD is a double bluff today...

RE: Insert Into problems

palotaiarpad — Tue, 29 Nov 2011 00:42:19 GMT

Thanks, but easy! :-P

RE: Insert Into problems

Koen Verbeeck — Mon, 28 Nov 2011 23:42:01 GMT

As said before, the AS clause in the SELECT statement is for aliasing the columns, which is disregarded by the INSERT statement.Your statement should have looked like this:INSERT INTO #ATable (col2, col1, col3)...if you wanted to change the order of inserts.Appreciate the effort of putting this in a QoTD.(and I was looking frantically for 5 minutes to find the "catch". :-D)

RE: Insert Into problems

SQLRNNR — Mon, 28 Nov 2011 23:22:00 GMT

Thanks for the question.

RE: Insert Into problems

codebyo — Mon, 28 Nov 2011 20:24:36 GMT

Thank you for the question but I guess that's straightforward.The "AS colX" parts of the SELECT statement are aliases to the columns returned by the SELECT statement and they don't reference column positions. The INSERT statement will follow the order specified inside the parenthesis and if no columns were specified in that statement, it will follow the creation order of the columns.Best regards,

Insert Into problems

henrik staun poulsen — Mon, 28 Nov 2011 20:21:14 GMT

Comments posted to this topic are about the item [B]Insert Into problems[/B]