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
Tobar
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
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.
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 Visits: 2774
"=" 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
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
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 :-P

<><
Livin' down on the cube farm. Left, left, then a right.
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 Visits: 2774
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
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
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
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 Visits: 2774
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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24198 Visits: 37963
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),>Wink
GO





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

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)
Tobar
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
Helpful. Thanks.

<><
Livin' down on the cube farm. Left, left, then a right.
marc.snoeys
marc.snoeys
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 849
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
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
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.
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