Insert Into problems

Question

Insert Into problems

Henrik Staun Poulsen

SSCertifiable

Points: 6578
More actions
November 28, 2011 at 8:21 pm

#390973

Comments posted to this topic are about the item Insert Into problems

Viewing 15 posts - 1 through 15 (of 34 total)

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

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 1

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,

Andre Guerreiro Neto

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

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)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 4

palotaiarpad

SSCertifiable

Points: 5617

November 29, 2011 at 12:42 am

#1413822

Thanks, but easy! 😛

paul s-306273 SSChampion Points: 10730 More actions · Answer 5

paul s-306273

SSChampion

Points: 10730

November 29, 2011 at 1:49 am

#1413854

So QOTD is a double bluff today...

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

Koen Verbeeck (11/28/2011)
(and I was looking frantically for 5 minutes to find the "catch". :-D)

Glad to see I had so much effects on you all :hehe:.

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 7

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.

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 8

sharath.chalamgari

SSCertifiable

Points: 5680

November 29, 2011 at 2:41 am

#1413877

Simple One........

InvisibleCat Default port Points: 1496 More actions · Answer 9

I've spent a few hours, before I noticed the bug.

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

Nic-306421 (11/29/2011)
I've spent a few hours, before I noticed the bug.
It may just be me, but I wouldn't class this a bug...

It's not just you 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 11

thanks for question!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 12

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?

(DT_I4)(SpeedIntervalFrom / 0.5)

It is an expression in a Derived column component.

Well, it should have been

(DT_I4)FLOOR(SpeedIntervalFrom / 0.5)

because we wanted it to truncate.

rajucse.kumar Newbie Points: 9 More actions · Answer 13

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

Nice question. Not terribly hard, but interesting. It could have been made a bit harder by omitting the column list in the INSERT:

INSERT INTO #ATable

SELECT 2 AS col2, 1 AS col1, 3 AS col3

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:

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;

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/