Insert Into problems

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Henrik Staun Polsen... » Insert Into problems

35 posts, Page 1 of 4

1 2 3 4 »»»

Insert Into problems

Rate Topic

Display Mode

Topic Options

Author

Message

henrik staun poulsen

Posted Monday, November 28, 2011 8:21 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 1,133, Visits: 856

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

Post #1212861

codebyo

Posted Monday, November 28, 2011 8:24 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436

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

Post #1212862

SQLRNNR

Posted Monday, November 28, 2011 11:22 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 18,754, Visits: 12,337

Thanks for the question.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1212919

Koen Verbeeck

Posted Monday, November 28, 2011 11:42 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473

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". BigGrin

)

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

palotaiarpad

Posted Tuesday, November 29, 2011 12:42 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:06 AM
Points: 849, Visits: 323

Thanks, but easy! Tongue

Post #1212963

paul s-306273

Posted Tuesday, November 29, 2011 1:49 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:24 AM
Points: 1,161, Visits: 642

So QOTD is a double bluff today...

Post #1212999

Ninja's_RGR'us

Posted Tuesday, November 29, 2011 1:55 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 21,357, Visits: 9,539

Koen Verbeeck (11/28/2011)

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

)

Glad to see I had so much effects on you all Hehe

Post #1213004

henrik staun poulsen

Posted Tuesday, November 29, 2011 1:56 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 1,133, Visits: 856

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

sharath.chalamgari

Posted Tuesday, November 29, 2011 2:41 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:30 AM
Points: 1,038, Visits: 679

Simple One........

Post #1213026

Nic-306421

Posted Tuesday, November 29, 2011 2:53 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:31 AM
Points: 58, Visits: 540

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 »

35 posts, Page 1 of 4

1 2 3 4 »»»

Permissions