Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Have I been smoking something?? Insert construction question.


Have I been smoking something?? Insert construction question.

Author
Message
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 7820
Tobar (5/23/2013)The one draw back with this method, I just noticed :-D, 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. :-D

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Eugene Elutin
    Eugene Elutin
    Hall of Fame
    Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

    Group: General Forum Members
    Points: 3036 Visits: 5478
    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!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    Sergiy
    Sergiy
    SSCertifiable
    SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

    Group: General Forum Members
    Points: 5812 Visits: 11390
    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.
    Sergiy
    Sergiy
    SSCertifiable
    SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

    Group: General Forum Members
    Points: 5812 Visits: 11390
    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.
    w00t
    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!
    :-P

    What does not make that statement totally right thought...
    GilaMonster
    GilaMonster
    SSC-Forever
    SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

    Group: General Forum Members
    Points: 47101 Visits: 44346
    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, MVP, M.Sc (Comp Sci)
    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


    Phil Parkin
    Phil Parkin
    SSCrazy Eights
    SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

    Group: General Forum Members
    Points: 8299 Visits: 19444
    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.
    w00t
    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!
    :-P

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    Sergiy
    Sergiy
    SSCertifiable
    SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

    Group: General Forum Members
    Points: 5812 Visits: 11390
    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.
    Sergiy
    Sergiy
    SSCertifiable
    SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

    Group: General Forum Members
    Points: 5812 Visits: 11390
    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.
    w00t
    GilaMonster
    GilaMonster
    SSC-Forever
    SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

    Group: General Forum Members
    Points: 47101 Visits: 44346
    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, MVP, M.Sc (Comp Sci)
    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


    Bhaskar.Shetty
    Bhaskar.Shetty
    Right there with Babe
    Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

    Group: General Forum Members
    Points: 778 Visits: 509
    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.
    w00t


    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.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search