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 Thursday, May 16, 2013 10:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:40 PM
Points: 20,687, Visits: 32,295
I see nothing wrong with the INSERT statment. Looks to me like you would like to see it more like the UPDATE statement.



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)
Post #1453651
Posted Friday, May 17, 2013 12:22 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 23, Visits: 576
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"
Post #1453857
Posted Friday, May 17, 2013 5:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:40 PM
Points: 20,687, Visits: 32,295
And I will just have to disagree with you. I would find it more difficult to work with, The current syntax is just fine.



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)
Post #1453950
Posted Friday, May 17, 2013 7:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 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 #1453987
Posted Wednesday, May 22, 2013 5:49 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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.
Post #1455767
Posted Wednesday, May 22, 2013 6:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:47 PM
Points: 1,778, Visits: 5,730
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


  • 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 #1455769
    Posted Wednesday, May 22, 2013 11:48 PM


    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Today @ 7:26 AM
    Points: 23, Visits: 576
    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"
    Post #1455811
    Posted Thursday, May 23, 2013 12:04 AM


    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Today @ 7:26 AM
    Points: 23, Visits: 576
    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"
    Post #1455818
    Posted Thursday, May 23, 2013 12:05 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 2:25 PM
    Points: 39,910, Visits: 36,250
    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 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 #1455819
    Posted Thursday, May 23, 2013 12:12 AM


    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Today @ 7:26 AM
    Points: 23, Visits: 576
    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"
    Post #1455821
    « Prev Topic | Next Topic »

    Add to briefcase ««12345»»»

    Permissions Expand / Collapse