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 Tuesday, November 29, 2011 3:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 12,206, Visits: 9,165
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
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: Tuesday, April 15, 2014 2:25 PM
Points: 1,253, Visits: 13,546
thanks for question!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1213057
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: Saturday, April 12, 2014 10:26 AM
Points: 1,310, Visits: 958
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
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
Posted Tuesday, November 29, 2011 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 5,794, Visits: 8,006
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
Posted Tuesday, November 29, 2011 5:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:09 AM
Points: 1,622, Visits: 2,032
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.
Connect to me on LinkedIn
Post #1213130
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: Tuesday, April 15, 2014 6:27 AM
Points: 1,378, Visits: 454
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
Posted Tuesday, November 29, 2011 6:53 AM
SSC Eights!

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 824, Visits: 319
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
Posted Tuesday, November 29, 2011 6:57 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
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
Post #1213203
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: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
@Tol, I like her even better that way
Post #1213207
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse