SQL Clone
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38989 Visits: 38508
I see nothing wrong with the INSERT statment. Looks to me like you would like to see it more like the UPDATE statement.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
marc.snoeys
marc.snoeys
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 881
That's basically my point :-)

There is nothing wrong with the INSERT-statement at all.

But, my personal opinion is that it would be more intuitive to be able to write an INSERT-statement just like an UPDATE-statement.


But that's merely a discussion on improvement/addition of syntax.

"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38989 Visits: 38508
And I will just have to disagree with you. I would find it more difficult to work with, The current syntax is just fine.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86444 Visits: 45236
marc.snoeys (5/17/2013)
But, my personal opinion is that it would be more intuitive to be able to write an INSERT-statement just like an UPDATE-statement.


Personally I'd prefer an update in the form of the insert statement, easier to write, easier to test.

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


Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10234 Visits: 11959
marc.snoeys (5/17/2013)
That's basically my point :-)

There is nothing wrong with the INSERT-statement at all.

But, my personal opinion is that it would be more intuitive to be able to write an INSERT-statement just like an UPDATE-statement.


But that's merely a discussion on improvement/addition of syntax.


Try to insert 2 or more records using your proposed syntax.
Or implement a check for existence of a key value in the table.

I could hardly name it any kind of improvement.
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4090 Visits: 7865
marc.snoeys (5/17/2013)
That's basically my point :-)

There is nothing wrong with the INSERT-statement at all.

But, my personal opinion is that it would be more intuitive to be able to write an INSERT-statement just like an UPDATE-statement.


But that's merely a discussion on improvement/addition of syntax.


There is nothing preventing you from doing something like this:


insert mytable(col1,col2,col3,col4)
select
col1 = myothertable.col3
, col2 = getdate()
, col3 = newid()
, col4 = 'foo'
from myothertable
where foo='bar';



If you find that more readable, you can handle it like that, but this doesn't guarantee that what you alias as col2 actually ends up in col2 - that relies on your good skills.

Personally, if I have a large number of columns in an insert, I like to put the column name in a comment in the select:


insert mytable(col1,col2,col3,col4)
select
myothertable.col3 -- col1
, getdate() -- col2
, newid() -- col3
, 'foo' -- col4
from myothertable
where foo='bar';



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

  • marc.snoeys
    marc.snoeys
    Valued Member
    Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

    Group: General Forum Members
    Points: 65 Visits: 881
    Sergiy (5/22/2013)
    marc.snoeys (5/17/2013)
    That's basically my point :-)

    There is nothing wrong with the INSERT-statement at all.

    But, my personal opinion is that it would be more intuitive to be able to write an INSERT-statement just like an UPDATE-statement.


    But that's merely a discussion on improvement/addition of syntax.


    Try to insert 2 or more records using your proposed syntax.

    Or implement a check for existence of a key value in the table.

    I could hardly name it any kind of improvement.


    That's a non-argument because you can't insert two or more records with the VALUES-syntax as well ;-)

    "Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
    marc.snoeys
    marc.snoeys
    Valued Member
    Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

    Group: General Forum Members
    Points: 65 Visits: 881
    mister.magoo (5/22/2013)
    marc.snoeys (5/17/2013)
    That's basically my point :-)

    There is nothing wrong with the INSERT-statement at all.

    But, my personal opinion is that it would be more intuitive to be able to write an INSERT-statement just like an UPDATE-statement.


    But that's merely a discussion on improvement/addition of syntax.


    There is nothing preventing you from doing something like this:


    insert mytable(col1,col2,col3,col4)
    select
    col1 = myothertable.col3
    , col2 = getdate()
    , col3 = newid()
    , col4 = 'foo'
    from myothertable
    where foo='bar';



    If you find that more readable, you can handle it like that, but this doesn't guarantee that what you alias as col2 actually ends up in col2 - that relies on your good skills.

    Personally, if I have a large number of columns in an insert, I like to put the column name in a comment in the select:


    insert mytable(col1,col2,col3,col4)
    select
    myothertable.col3 -- col1
    , getdate() -- col2
    , newid() -- col3
    , 'foo' -- col4
    from myothertable
    where foo='bar';




    Playing the devil's advocate I could say that both your suggestions are a kind of mimicking the UPDATE-syntax with the INSERT :-)

    But the layout of the code is more important than the syntax.

    And I also use your second style of writing the INSERT-statement.
    I even put the column-names on a seperate line as well, with comment if it's appropriate.

    "Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
    GilaMonster
    GilaMonster
    SSC Guru
    SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

    Group: General Forum Members
    Points: 86444 Visits: 45236
    marc.snoeys (5/22/2013)
    That's a non-argument because you can't insert two or more records with the VALUES-syntax as well ;-)


    Like this you mean?

    CREATE TABLE #test (
    ID INT
    )

    INSERT INTO #test (ID)
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

    SELECT * FROM #test AS t



    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


    marc.snoeys
    marc.snoeys
    Valued Member
    Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

    Group: General Forum Members
    Points: 65 Visits: 881
    GilaMonster (5/23/2013)
    marc.snoeys (5/22/2013)
    That's a non-argument because you can't insert two or more records with the VALUES-syntax as well ;-)


    Like this you mean?

    CREATE TABLE #test (
    ID INT
    )

    INSERT INTO #test (ID)
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)

    SELECT * FROM #test AS t



    I stand corrected

    "Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
    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