Have I been smoking something?? Insert construction question.

  • Perhaps I am confusing this formation with calls to a stored procedure.

    Isn't it possible to call an insert thus?

    insert into TableA

    ColA = @ValueA,

    ColB = 'some string'

    Documentation doesn't seem to support it (should be my first clue) but I have mis-read or been mis-lead by documentation before.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • "=" is causing an issue.

    You can use either of there:

    declare @ValueA varchar(10)

    set @ValueA = 'aaa'

    insert into dbo.T select @ValueA, 'yyy'

    GO

    insert into dbo.T values ('xxx', 'yyy')

    GO

    insert into dbo.T select 'xxx', 'yyy'

  • I wonder if the SQL overlords have ever considered implementing my approach. It is very disconnecting in reading code to try and match up the columns and values, when you are inserting a bunch of columns, because they are often separated by considerable space. IMH 😛

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • Well in that case, you can simply provide the columns while inserting as well:

    insert into dbo.T(ColA, ColB) select 'xxx', 'yyy'

    or

    insert into dbo.T(ColB, ColA) select 'xxx', 'yyy'

    depending on whether you want 'xxx' to be inserted in ColA or ColB and same for 'yyy'. It's the basic insertion and SQL Server provides you all the approaches you want to insert data.

  • still, when you have

    insert (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI) values (this, that, the, other, thing, then, one, more, thing)

    my eyes just do not match them up easily.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • You can use some other buttons like [Enter], [Tab] etc and format it as per your requirement. Here comes your creativity in picture. 🙂

  • If you right clink on the table you wish to insert values into and select script table as > Insert > New Query Editor Window, you will get something like this:

    INSERT INTO [dbo].[EventErrors]

    ([FileID]

    ,[ErrorMessage]

    ,[EventErrorObjectType]

    ,[Activity])

    VALUES

    (<FileID, int,>

    ,<ErrorMessage, nvarchar(255),>

    ,<EventErrorObjectType, nvarchar(255),>

    ,<Activity, nvarchar(255),>)

    GO

    Makes it easy to know where to put what as you finish the script.

  • Helpful. Thanks.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • Tobar (5/15/2013)


    still, when you have

    insert (ColA, ColB, ColC, ColD, ColE, ColF, ColG, ColH, ColI) values (this, that, the, other, thing, then, one, more, thing)

    my eyes just do not match them up easily.

    I have to agree with you, the syntax for the INSERT-statement is a bummer 🙂

    Unfortunately it's there and it's bound to stay

    "Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"

  • Change is always possible. There once was a time when you could not do "positional or named parameters". Hope springs eternal.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • I see nothing wrong with the INSERT statment. Looks to me like you would like to see it more like the UPDATE statement.

  • 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"

  • And I will just have to disagree with you. I would find it more difficult to work with, The current syntax is just fine.

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

    _____________
    Code for TallyGenerator

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply