Have I been smoking something?? Insert construction question.

Question

Post reply

Have I been smoking something?? Insert construction question.

Tobar

SSCarpal Tunnel

Points: 4876
More actions
May 15, 2013 at 5:18 am

#394781

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.

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

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

sqlnaive SSCoach Points: 17437 More actions · Answer 1

"=" 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'

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 2

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.

sqlnaive SSCoach Points: 17437 More actions · Answer 3

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.

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 4

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.

sqlnaive SSCoach Points: 17437 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 7

Helpful. Thanks.

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

marc.snoeys Ten Centuries Points: 1106 More actions · Answer 8

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"

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 9

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

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

marc.snoeys Ten Centuries Points: 1106 More actions · Answer 11

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 SSC Guru Points: 442467 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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 SSC Guru Points: 110208 More actions · Answer 14

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