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 Thursday, May 23, 2013 12:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 19, 2014 12:54 AM
Points: 463, Visits: 416
You cannot call insert like that, = operator works while you are updating any row or table.
Post #1455839
Posted Thursday, May 23, 2013 1:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1455843
Posted Thursday, May 23, 2013 1:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 19, 2014 12:54 AM
Points: 463, Visits: 416
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
Post #1455847
Posted Thursday, May 23, 2013 1:18 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1455849
Posted Thursday, May 23, 2013 1:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 19, 2014 12:54 AM
Points: 463, Visits: 416
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...
Post #1455854
Posted Thursday, May 23, 2013 5:38 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
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.
Post #1455956
Posted Thursday, May 23, 2013 5:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #1455962
Posted Thursday, May 23, 2013 1:50 PM


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
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 , 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.


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1456208
Posted Thursday, May 23, 2013 3:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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.
Post #1456231
Posted Thursday, May 23, 2013 3:31 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 42,438, Visits: 35,493
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 2008, MVP
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

Post #1456235
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse