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 7:50 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, September 23, 2014 1:04 PM
Points: 900, Visits: 1,490
Hugo Kornelis (11/29/2011)

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;



That's a great advice. In my day job, I can see, in many codes, a long list of columns without proper formatting and it gets harder and harder to read once the table's structure changes and you need to update code.

I usually code my INSERTs like this whenever possible:

INSERT INTO #ATable
(
Col1,
Col2,
Col3
)
VALUES
(
2 AS Col1,
1 AS Col2,
3 AS Col3
);

I'm not sure if it's a good way of doing it but it looks clear to me.

Best regards,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1213259
Posted Tuesday, November 29, 2011 7:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 6,020, Visits: 8,288
codebyo (11/29/2011)
I usually code my INSERTs like this whenever possible:

INSERT INTO #ATable
(
Col1,
Col2,
Col3
)
VALUES
(
2 AS Col1,
1 AS Col2,
3 AS Col3
);

I'm not sure if it's a good way of doing it but it looks clear to me.

I'm not really too fond about the aliases; I think it'd be better to use comments - as that is the only function the alias serves anyway.
Other than that, this method is fine - as long as the comments and the actual columns match up. But what happens if a change is made somewhere and the comments are not updated? If that happens a few time, developers will end up ignoring the column names in the comments (or aliases), as they know that those aren't always reliable - and than it's back to counting the 23rd column name from the list.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1213265
Posted Tuesday, November 29, 2011 8:16 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, September 23, 2014 1:04 PM
Points: 900, Visits: 1,490
Thanks for the comments, Hugo.
I will keep that in mind.

We have a lot denormalized tables with 100+ columns and it's a major pain to maintain their DML.

Best regards,



Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1213295
Posted Tuesday, November 29, 2011 8:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
thanks for the questions
Post #1213320
Posted Tuesday, November 29, 2011 8:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,125, Visits: 3,424
Thanks for the question -- it was a really easy one!
Post #1213338
Posted Tuesday, November 29, 2011 9:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 3,337, Visits: 1,992
I too was looking for a catch. Good back to basics question. Thanks!
Post #1213402
Posted Tuesday, November 29, 2011 10:48 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: Wednesday, September 17, 2014 1:32 PM
Points: 581, Visits: 733
Good question. Something like this can trip anyone up. I'm sorry that it took a few hours to recognize the mistake, but we have all been there at some point. I can recall pouring over code only to find a colon where a semi-colon was required.

Thanks,

Matt
Post #1213447
Posted Tuesday, November 29, 2011 1:29 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: Yesterday @ 9:56 AM
Points: 892, Visits: 1,293
Thanks for the question HESPO, good to see you again.

--TPET
Post #1213529
Posted Tuesday, November 29, 2011 1:31 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: Yesterday @ 8:13 AM
Points: 995, Visits: 981
Thank you for the question
Iulian
Post #1213530
Posted Wednesday, November 30, 2011 1:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:13 AM
Points: 1,384, Visits: 998
Thank you all, for the nice words.
A special "thank you" goes to Hugo, for the suggestion for formatting the fields in 3 columns, or how-ever many columns that fit onto one screen.

Best regards,
Henrik



Post #1213729
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse