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