Have I been smoking something?? Insert construction question.

Question

Have I been smoking something?? Insert construction question.

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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);

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

GilaMonster (5/23/2013)
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.

One of the aliasing form was removed from SQL 2012, the following no longer will work:

SELECT 'Custom_Col_Name' = t.Column1 FROM Table t

This one still valid:

SELECT Custom_Col_Name = t.Column1 FROM Table t

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 3

GilaMonster (5/23/2013)
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).

Not valid is your claim that aliasing in your example belongs to INSERT.

Or has any relevance to it at all.

As I illustrated in the following example any kind of aliasing you use in SELECT part does not have any effect on INSERT itself.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 4

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

Heh,

The only one who "left looking silly in perpetuity" is actually you, Phil.

:w00t:

Your example actually proves the "blanket statement" from Bhaskar.Shetty:

it inserts 2 rows, and it uses 2 "=" operators.

2/2 = 1 row per each operator: exactly whar Bhaskar been saying!

😛

What does not make that statement totally right thought...

_____________
Code for TallyGenerator

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Sergiy (5/24/2013)
Not valid is your claim that aliasing in your example belongs to INSERT.
Or has any relevance to it at all.

I never said the aliasing 'belongs' to insert, or that it has any effect on the insert. I said it's a valid form of T-SQL. That is all I said.

It is a valid form of a select (ie parses successfully) and therefore is is a valid form of the select portion of an insert statement (ie parses successfully).

I never claimed or stated anywhere that it has any effect on the insert, by magically fixing incorrect column ordering or otherwise changing the behaviour of the insert.

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Sergiy (5/24/2013)
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
Heh,
The only one who "left looking silly in perpetuity" is actually you, Phil.
:w00t:
Your example actually proves the "blanket statement" from Bhaskar.Shetty:
it inserts 2 rows, and it uses 2 "=" operators.
2/2 = 1 row per each operator: exactly whar Bhaskar been saying!
😛
What does not make that statement totally right thought...

Bizarre that you should think that Sergiy. Especially the "..exactly what Bhaskar has been saying!" bit. Exactly? Where is that post?

No one has been asserting that the = operator in the SELECT has any function beyond (arguably) helping readability. But saying that you "cannot call insert like that" is clearly wrong. Clear to me, at least. I can only assume that you are interpreting this differently.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sergiy SSC Guru Points: 110208 More actions · Answer 7

GilaMonster (5/24/2013)
I never claimed or stated anywhere that it has any effect on the insert

Look, just 1 row above you say:

the select portion of an insert statement

If anything has an effect on the part than it mast have an effect of the whole.

If SELECT is a potion of INSERT, as you say, then aliasing which changes SELECT must make a difference on INSERT.

Which - again, as you say - it does not.

So, at least one of your statements is wrong.

I say - it's the one where you unite SELECT and INSERT into one statement.

They are not.

SELECT is not a portion of INSERT.

INSERT may use any source of recordsets, not only SELECT queries.

It may be EXEC one as well.

Or an array of "VALUES".

All examples of smart (or not, does not matter) aliasing belong to SELECT statement and have nothing to do with INSERT.

Only "aliasing" which is relevant to INSERT happens in between the brackets following the name of the table.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Phil Parkin (5/24/2013)

Where is that post?

The first post of this topic.

But saying that you "cannot call insert like that" is clearly wrong.

Not if "like that" means like in the initial post.

But yes, it's wrong, if you take it in the context of following replies.

Than indeed, it becomes a "blanket statement" which does not have enough grounds.

But I was talking not about correctness of that statement (I noted in the end I cannot say it's correct) but about your example.

You managed to build it in such a way that you kinda proved his point!

And you comment about someone being silly made it even more funny.

:w00t:

_____________
Code for TallyGenerator

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Sergiy (5/24/2013)
GilaMonster (5/24/2013)
I never claimed or stated anywhere that it has any effect on the insert
Look, just 1 row above you say:
the select portion of an insert statement
If anything has an effect on the part than it mast have an effect of the whole.

I never claimed it had an effect, on the whole or the part, in that statement or any other. I stated it is valid, that is, it parses successfully.

So, at least one of your statements is wrong.

Except I never made such a statement. If you want to claim I said something I never did, please, by all means carry on, have fun.

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

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 10

Sergiy (5/24/2013)

Phil Parkin (5/24/2013)
Where is that post?
The first post of this topic.
But saying that you "cannot call insert like that" is clearly wrong.
Not if "like that" means like in the initial post.
But yes, it's wrong, if you take it in the context of following replies.
Than indeed, it becomes a "blanket statement" which does not have enough grounds.
But I was talking not about correctness of that statement (I noted in the end I cannot say it's correct) but about your example.
You managed to build it in such a way that you kinda proved his point!
And you comment about someone being silly made it even more funny.
:w00t:

Even me explaining to phil , that the original post and the solution what he has given has no link between it, didn't understood the original post and calling other silly. what made me furious.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

Bhaskar.Shetty (5/24/2013)

Sergiy (5/24/2013)
Phil Parkin (5/24/2013)
Where is that post?
The first post of this topic.
But saying that you "cannot call insert like that" is clearly wrong.
Not if "like that" means like in the initial post.
But yes, it's wrong, if you take it in the context of following replies.
Than indeed, it becomes a "blanket statement" which does not have enough grounds.
But I was talking not about correctness of that statement (I noted in the end I cannot say it's correct) but about your example.
You managed to build it in such a way that you kinda proved his point!
And you comment about someone being silly made it even more funny.
:w00t:
Even me explaining to phil , that the original post and the solution what he has given has no link between it, didn't understood the original post and calling other silly. what made me furious.

Please reread my post. Use a dictionary, if necessary, to help you understand its meaning. I did not call anyone silly. I said that the post made you look silly - totally different. Even super-intelligent people can look silly sometimes, it does not make them silly.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bhaskar.Shetty Hall of Fame Points: 3054 More actions · Answer 12

Please reread my post. Use a dictionary, if necessary, to help you understand its meaning. I did not call anyone silly. I said that the post made you look silly - totally different. Even super-intelligent people can look silly sometimes, it does not make them silly.

Better you re-read your post and compare it with what you are written above, and its better that we indulge more in solving technical issues than wasting time discussing who's expert and novince. (or silly in your word). ;-). anyways I will like to hear and learn and hear more and mpre from you whole my life.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 13

Sergiy (5/24/2013)

Phil Parkin (5/24/2013)
Where is that post?
The first post of this topic.
But saying that you "cannot call insert like that" is clearly wrong.
Not if "like that" means like in the initial post.
But yes, it's wrong, if you take it in the context of following replies.
Than indeed, it becomes a "blanket statement" which does not have enough grounds.
But I was talking not about correctness of that statement (I noted in the end I cannot say it's correct) but about your example.
You managed to build it in such a way that you kinda proved his point!
And you comment about someone being silly made it even more funny.
:w00t:

I will accept that, had Bhaskar's response been tied to the original post, it would have been valid. But it wasn't - it came after 19 responses. As it did not quote the initial post, I understood it to be taken in the context of the ongoing discussion, not as an independent and isolated comment. I do not think I am alone.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 14

Bhaskar.Shetty (5/24/2013)
Please reread my post. Use a dictionary, if necessary, to help you understand its meaning. I did not call anyone silly. I said that the post made you look silly - totally different. Even super-intelligent people can look silly sometimes, it does not make them silly.
Better you re-read your post and compare it with what you are written above, and its better that we indulge more in solving technical issues than wasting time discussing who's expert and novince. (or silly in your word). ;-). anyways I will like to hear and learn and hear more and mpre from you whole my life.

I can't follow your logic. I have not made any claims about myself or anyone else being expert, novice or anything in between.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 15

I don't know why, but whole this thread looks a bit "silly" to me. The OP original post was about INSERT syntax is not the same as he would expect.

I'm not sure that this sort of thing worth of discussion. As it's really up to the personal preferences and expectations.

I can list few of mine one:

Why UPDATE and not simply CHANGE

Why there is no CLEAR [tablename]?

And at the end, why SELECT?

What does it do? Doesn't it takes and displays the data?

Therefor TAKE * FROM or GET * FROM or SHOW * FROM or DISPLAY * FROM or etc, would be more logical than SELECT which is kind of similar to CHOOSE, at least based on my knowledge of English...

It's endless :hehe:

Are you all been smoking something?????????

:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]