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 «««12345»»

Have I been smoking something?? Insert construction question. Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 3:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 1,796, Visits: 5,800
Tobar (5/23/2013)The one draw back with this method, I just noticed , is that you must specify all the columns in the table. If you are only updating 5 columns out of 20 ... Oh well.


Not true at all, as long as a column has a default or is nullable, you can leave it out of the insert. Why do you think you can't?
Obviously the columns you want to insert must be specifically listed in the INSERT statement AND have corresponding outputs from the SELECT clause, but that it always true.

GilaMonster (5/23/2013)You mean the way Marc suggested?
insert mytable(col1,col2,col3,col4)
select
col1 = myothertable.col3
, col2 = getdate()
, col3 = newid()
, col4 = 'foo'
from myothertable
where foo='bar';



Who is Marc ?

Sergiy (5/23/2013)INSERT will put values into wrong columns despite your best effort of aliasing.


That is exactly what I stated at the time, but using different words...

mister.magoo (5/22/2013)... but this doesn't guarantee that what you alias as col2 actually ends up in col2 - that relies on your good skills.


By saying "that relies on your good skills", I can see that I left that open to interpretation, but what I meant was that aliasing a column does not mean it will insert into a column of the same name, the order of the columns in the INSERT clause matches the order in the SELECT clause regardless of name.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1456335
    Posted Friday, May 24, 2013 3:58 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Tuesday, October 21, 2014 8:55 AM
    Points: 2,873, Visits: 5,185
    GilaMonster (5/23/2013)
    Sergiy (5/23/2013)
    GilaMonster (5/23/2013)
    Since that's a valid form for the select, it's also valid for the insert...select.


    Very arguable.


    What, that it's valid? Since it passes a syntax check it's valid T-SQL, hard to argue that it's not valid (unless in SQL 2012 that aliasing form has been removed and a query using that aliasing form fails a syntax check).

    I never claimed it's a good idea or that it would magically prevent column order mistake, personally I don't like that form of aliasing and never use it. All I said was that it's a valid (ie correct, parsable) form of T-SQL.


    One of the aliasing form was removed from SQL 2012, the following no longer will work:

    SELECT 'Custom_Col_Name' = t.Column1 FROM Table t

    This one still valid:

    SELECT Custom_Col_Name = t.Column1 FROM Table t



    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    Post #1456362
    Posted Friday, May 24, 2013 5:52 AM
    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, October 13, 2014 8:08 PM
    Points: 4,573, Visits: 8,353
    GilaMonster (5/23/2013)
    Sergiy (5/23/2013)
    GilaMonster (5/23/2013)
    Since that's a valid form for the select, it's also valid for the insert...select.


    Very arguable.


    What, that it's valid? Since it passes a syntax check it's valid T-SQL, hard to argue that it's not valid (unless in SQL 2012 that aliasing form has been removed and a query using that aliasing form fails a syntax check).


    Not valid is your claim that aliasing in your example belongs to INSERT.
    Or has any relevance to it at all.

    As I illustrated in the following example any kind of aliasing you use in SELECT part does not have any effect on INSERT itself.
    Post #1456413
    Posted Friday, May 24, 2013 6:00 AM
    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, October 13, 2014 8:08 PM
    Points: 4,573, Visits: 8,353
    Phil Parkin (5/23/2013)
    Bhaskar.Shetty (5/23/2013)
    You cannot call insert like that, = operator works while you are updating any row or table.


    Before making blanket statements like this, I find that it is always best to double-check, lest you are left looking silly in perpetuity:

    create table #test (ID int)

    insert into #test
    (ID)
    select Id = 1
    union all
    select Id = 2

    select *
    from #test as t



    Heh,
    The only one who "left looking silly in perpetuity" is actually you, Phil.

    Your example actually proves the "blanket statement" from Bhaskar.Shetty:
    it inserts 2 rows, and it uses 2 "=" operators.
    2/2 = 1 row per each operator: exactly whar Bhaskar been saying!


    What does not make that statement totally right thought...
    Post #1456419
    Posted Friday, May 24, 2013 6:06 AM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 2:32 AM
    Points: 40,205, Visits: 36,609
    Sergiy (5/24/2013)
    Not valid is your claim that aliasing in your example belongs to INSERT.
    Or has any relevance to it at all.


    I never said the aliasing 'belongs' to insert, or that it has any effect on the insert. I said it's a valid form of T-SQL. That is all I said.
    It is a valid form of a select (ie parses successfully) and therefore is is a valid form of the select portion of an insert statement (ie parses successfully).

    I never claimed or stated anywhere that it has any effect on the insert, by magically fixing incorrect column ordering or otherwise changing the behaviour of the insert.



    Gail Shaw
    Microsoft Certified Master: SQL Server 2008, MVP
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    Post #1456427
    Posted Friday, May 24, 2013 6:20 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 2:55 AM
    Points: 5,175, Visits: 12,030
    Sergiy (5/24/2013)
    Phil Parkin (5/23/2013)
    Bhaskar.Shetty (5/23/2013)
    You cannot call insert like that, = operator works while you are updating any row or table.


    Before making blanket statements like this, I find that it is always best to double-check, lest you are left looking silly in perpetuity:

    create table #test (ID int)

    insert into #test
    (ID)
    select Id = 1
    union all
    select Id = 2

    select *
    from #test as t



    Heh,
    The only one who "left looking silly in perpetuity" is actually you, Phil.

    Your example actually proves the "blanket statement" from Bhaskar.Shetty:
    it inserts 2 rows, and it uses 2 "=" operators.
    2/2 = 1 row per each operator: exactly whar Bhaskar been saying!


    What does not make that statement totally right thought...


    Bizarre that you should think that Sergiy. Especially the "..exactly what Bhaskar has been saying!" bit. Exactly? Where is that post?

    No one has been asserting that the = operator in the SELECT has any function beyond (arguably) helping readability. But saying that you "cannot call insert like that" is clearly wrong. Clear to me, at least. I can only assume that you are interpreting this differently.




    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1456435
    Posted Friday, May 24, 2013 6:24 AM
    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, October 13, 2014 8:08 PM
    Points: 4,573, Visits: 8,353
    GilaMonster (5/24/2013)

    I never claimed or stated anywhere that it has any effect on the insert


    Look, just 1 row above you say:

    the select portion of an insert statement


    If anything has an effect on the part than it mast have an effect of the whole.
    If SELECT is a potion of INSERT, as you say, then aliasing which changes SELECT must make a difference on INSERT.
    Which - again, as you say - it does not.
    So, at least one of your statements is wrong.

    I say - it's the one where you unite SELECT and INSERT into one statement.
    They are not.
    SELECT is not a portion of INSERT.

    INSERT may use any source of recordsets, not only SELECT queries.
    It may be EXEC one as well.
    Or an array of "VALUES".

    All examples of smart (or not, does not matter) aliasing belong to SELECT statement and have nothing to do with INSERT.
    Only "aliasing" which is relevant to INSERT happens in between the brackets following the name of the table.
    Post #1456439
    Posted Friday, May 24, 2013 6:37 AM
    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Monday, October 13, 2014 8:08 PM
    Points: 4,573, Visits: 8,353
    Phil Parkin (5/24/2013)
    [quote] Where is that post?

    The first post of this topic.

    But saying that you "cannot call insert like that" is clearly wrong.

    Not if "like that" means like in the initial post.

    But yes, it's wrong, if you take it in the context of following replies.
    Than indeed, it becomes a "blanket statement" which does not have enough grounds.

    But I was talking not about correctness of that statement (I noted in the end I cannot say it's correct) but about your example.
    You managed to build it in such a way that you kinda proved his point!
    And you comment about someone being silly made it even more funny.
    Post #1456450
    Posted Friday, May 24, 2013 6:40 AM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 2:32 AM
    Points: 40,205, Visits: 36,609
    Sergiy (5/24/2013)
    GilaMonster (5/24/2013)

    I never claimed or stated anywhere that it has any effect on the insert


    Look, just 1 row above you say:

    the select portion of an insert statement


    If anything has an effect on the part than it mast have an effect of the whole.


    I never claimed it had an effect, on the whole or the part, in that statement or any other. I stated it is valid, that is, it parses successfully.

    So, at least one of your statements is wrong.


    Except I never made such a statement. If you want to claim I said something I never did, please, by all means carry on, have fun.



    Gail Shaw
    Microsoft Certified Master: SQL Server 2008, MVP
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    Post #1456453
    Posted Friday, May 24, 2013 7:16 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: Thursday, October 16, 2014 9:28 AM
    Points: 512, Visits: 433
    Sergiy (5/24/2013)
    Phil Parkin (5/24/2013)
    [quote] Where is that post?

    The first post of this topic.

    But saying that you "cannot call insert like that" is clearly wrong.

    Not if "like that" means like in the initial post.

    But yes, it's wrong, if you take it in the context of following replies.
    Than indeed, it becomes a "blanket statement" which does not have enough grounds.

    But I was talking not about correctness of that statement (I noted in the end I cannot say it's correct) but about your example.
    You managed to build it in such a way that you kinda proved his point!
    And you comment about someone being silly made it even more funny.


    Even me explaining to phil , that the original post and the solution what he has given has no link between it, didn't understood the original post and calling other silly. what made me furious.
    Post #1456483
    « Prev Topic | Next Topic »

    Add to briefcase «««12345»»

    Permissions Expand / Collapse