Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Henrik Staun Polsen... » Insert Into problems
Add to briefcase
35 posts, Page 2 of 4
««1234»»»

Insert Into problems Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Koen Verbeeck
Posted Tuesday, November 29, 2011 3:08 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:11 AM
Points: 9,378, Visits: 6,473
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


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 #1213038
rfr.ferrari
Posted Tuesday, November 29, 2011 4:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 10:51 AM
Points: 1,219, Visits: 13,507
thanks for question!

rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1213057
henrik staun poulsen
Posted Tuesday, November 29, 2011 5:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 1,133, Visits: 856
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.


Post #1213086
rajucse.kumar
Posted Tuesday, November 29, 2011 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 22, 2012 1:36 AM
Points: 9, Visits: 22
It is simply can be vieved, value inserted in respective columns will be in column order ...
Post #1213099
Hugo Kornelis
Posted Tuesday, November 29, 2011 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 PM
Points: 5,244, Visits: 7,061
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1213103
Thomas Abraham
Posted Tuesday, November 29, 2011 5:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 1,152, Visits: 1,457
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.

Please don't go. The drones need you. They look up to you.
Post #1213130
Mike Is Here
Posted Tuesday, November 29, 2011 6:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 1,331, Visits: 433
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.
Post #1213168
marlon.seton
Posted Tuesday, November 29, 2011 6:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:49 AM
Points: 546, Visits: 188
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.
Post #1213198
L' Eomot Inversé
Posted Tuesday, November 29, 2011 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 7,110, Visits: 7,184
Good question.

It caught me, although it most certainly shouldn't have . Guess I shouldn't try to understand SQL until the hangover subsides

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.
Post #1213203
Ninja's_RGR'us
Posted Tuesday, November 29, 2011 7:00 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 21,357, Visits: 9,540
@Tol, I like her even better that way
Post #1213207
« Prev Topic | Next Topic »

Add to briefcase
35 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.