SQL Clone
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
Bhaskar.Shetty
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 509
You cannot call insert like that, = operator works while you are updating any row or table.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51567 Visits: 21163
Bhaskar.Shetty (5/23/2013)
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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Bhaskar.Shetty
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 509
Phil Parkin (5/23/2013)
Bhaskar.Shetty (5/23/2013)
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51567 Visits: 21163
Your statement:

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


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


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Bhaskar.Shetty
Bhaskar.Shetty
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 509
Phil Parkin (5/23/2013)
Your statement:

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


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... :-)
Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

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

<><
Livin' down on the cube farm. Left, left, then a right.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224280 Visits: 46311
Bhaskar.Shetty (5/23/2013)
You cannot call insert like that, = operator works while you are updating any row or table.


You mean the way Marc suggested?
insert mytable(col1,col2,col3,col4)
select
col1 = myothertable.col3
, col2 = getdate()
, col3 = newid()
, col4 = 'foo'
from myothertable
where foo='bar';



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.

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


Tobar
Tobar
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 758
mister.magoo (5/22/2013)


insert mytable(col1,col2,col3,col4)
select
col1 = myothertable.col3
, col2 = getdate()
, col3 = newid()
, col4 = 'foo'
from myothertable
where foo='bar';



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)
Change is always possible. There once was a time when you could not do "positional or named parameters". Hope springs eternal.


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

<><
Livin' down on the cube farm. Left, left, then a right.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25586 Visits: 12467
GilaMonster (5/23/2013)
[quote]Bhaskar.Shetty (5/23/2013)
Since that's a valid form for the select, it's also valid for the insert...select.


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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224280 Visits: 46311
Sergiy (5/23/2013)
GilaMonster (5/23/2013)
Since that's a valid form for the select, it's also valid for the insert...select.


Very arguable.


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.

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


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