Tobar (5/23/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.
Not true at all, as long as a column has a default or is nullable, you can leave it out of the insert. Why do you think you can't?
Obviously the columns you want to insert must be specifically listed in the INSERT statement AND have corresponding outputs from the SELECT clause, but that it always true.
GilaMonster (5/23/2013)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';
Who is Marc ? 🙂
Sergiy (5/23/2013)INSERT will put values into wrong columns despite your best effort of aliasing.
That is exactly what I stated at the time, but using different words...
mister.magoo (5/22/2013)... but this doesn't guarantee that what you alias as col2 actually ends up in col2 - that relies on your good skills.
By saying "that relies on your good skills", I can see that I left that open to interpretation, but what I meant was that aliasing a column does not mean it will insert into a column of the same name, the order of the columns in the INSERT clause matches the order in the SELECT clause regardless of name. 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);