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 Wednesday, May 15, 2013 5:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1453019
Posted Wednesday, May 15, 2013 5:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 3,537, Visits: 2,647
"=" 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'
Post #1453025
Posted Wednesday, May 15, 2013 5:49 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1453031
Posted Wednesday, May 15, 2013 5:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 3,537, Visits: 2,647
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.
Post #1453032
Posted Wednesday, May 15, 2013 6:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1453045
Posted Wednesday, May 15, 2013 6:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 3,537, Visits: 2,647
You can use some other buttons like [Enter], [Tab] etc and format it as per your requirement. Here comes your creativity in picture.
Post #1453067
Posted Wednesday, May 15, 2013 7:42 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 5:09 PM
Points: 23,089, Visits: 31,635
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.



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 #1453098
Posted Wednesday, May 15, 2013 9:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Helpful. Thanks.

<><
Livin' down on the cube farm. Left, left, then a right.
Post #1453175
Posted Thursday, May 16, 2013 7:46 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:18 AM
Points: 23, Visits: 530
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"
Post #1453541
Posted Thursday, May 16, 2013 8:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1453596
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse