Have I been smoking something?? Insert construction question.

That's a non-argument because you can't insert two or more records with the VALUES-syntax as well 😉

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.

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

You cannot call insert like that, = operator works while you are updating any row or table.

Before making blanket statements like this, I find that it is always best to double-check, lest you are left looking silly in perpetuity:

create table #test (ID int)

insert into #test

(ID)

select Id = 1

union all

select Id = 2

select *

from #test as t

First check the way insert statement has been written and tried, and solutions what you have given may be one from lot, there are plenty of other solution which you can use it for this purpose.

before calling other silly better you readout the problem first, the guy has asked whether he can write the statement in that manner or not

Your statement:

Is plainly wrong - I proved it in code. And now you are proving that you do not have the grace to admit it.

I already adimited that the solution given by you is one from the lot, but there's no similarity between what tobar as asked for and your solutions, and if you know something more than others, its better you mention that, instead calling other silly, and nobody can become expert from attaching a freaking face on his own profile... 🙂

Hey all. I started this little drama and I did not foresee the avenues it would take (had I I still would have started it, but that is a different story). I really have learned some great possibilities to use with my insert statements. THANK YOU all for your input, challenges, and thoughts.

You mean the way Marc suggested?

The = isn't just used in updates, it's also the older way to assign column aliases.

SELECT ThisIsTheColumnAlias = 1

Run that, you get a single row (value 1) in a column that's called ThisIsTheColumnAlias. Since that's a valid form for the select, it's also valid for the insert...select.

Create Table #Test (

Num Int

)

Insert into #Test (Num)

SELECT ThisIsANumber = 1

Select * From #Test

Drop table #Test

Personally I still prefer the AS method for aliasing columns, though you can do some odd tricks with update using aliases like this.

mister.magoo (5/22/2013)

The one draw back with this method, I just noticed :-D, is that you must specify all the columns in the table. If you are only updating 5 columns out of 20 ... Oh well.

Tobar (5/16/2013)

Maybe there is hope that they will change it so that you don't have to specify all when you use "assignment style" or whatever it will be called. :w00t:

GilaMonster (5/23/2013)

Very arguable.

All those aliasings remain within SELECT and have no relevance to INSERT.

INSERT takes values from a recordsert supplied in physical order of columns and ignores whatever aliases you assigned.

See this example:

insert mytable(col1,col2,col3,col4)

select

col2 = getdate()

, col1 = myothertable.col3

, col3 = newid()

, col4 = 'foo'

from myothertable

where foo='bar';

INSERT will put values into wrong columns despite your best effort of aliasing.

What, that it's valid? Since it passes a syntax check it's valid T-SQL, hard to argue that it's not valid (unless in SQL 2012 that aliasing form has been removed and a query using that aliasing form fails a syntax check).

I never claimed it's a good idea or that it would magically prevent column order mistake, personally I don't like that form of aliasing and never use it. All I said was that it's a valid (ie correct, parsable) form of T-SQL.