Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Insert Into problems Expand / Collapse
Author
Message
Posted Monday, November 28, 2011 8:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 5:25 AM
Points: 1,430, Visits: 1,025
Comments posted to this topic are about the item Insert Into problems


Post #1212861
Posted Monday, November 28, 2011 8:24 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:02 AM
Points: 912, Visits: 1,500
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,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Post #1212862
Posted Monday, November 28, 2011 11:22 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1212919
Posted Monday, November 28, 2011 11:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
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". )




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1212934
Posted Tuesday, November 29, 2011 12:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 12:44 AM
Points: 1,392, Visits: 516
Thanks, but easy!
Post #1212963
Posted Tuesday, November 29, 2011 1:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:32 AM
Points: 1,417, Visits: 809
So QOTD is a double bluff today...
Post #1212999
Posted Tuesday, November 29, 2011 1:55 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
Koen Verbeeck (11/28/2011)

(and I was looking frantically for 5 minutes to find the "catch". )



Glad to see I had so much effects on you all .
Post #1213004
Posted Tuesday, November 29, 2011 1:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 5:25 AM
Points: 1,430, Visits: 1,025
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.




Post #1213005
Posted Tuesday, November 29, 2011 2:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 10:49 AM
Points: 1,194, Visits: 791
Simple One........
Post #1213026
Posted Tuesday, November 29, 2011 2:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 11, 2013 8:39 AM
Points: 58, Visits: 606

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
Post #1213032
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse