June 17, 2008 at 9:29 am
Hello,
I am running "insert into t select * from t" and getting the above error.
How is this possible? There should be no data type conversion whatsoever.
Thanks,
Gabor
June 17, 2008 at 12:01 pm
Does the same problem occur if you specify column names?
Also, if you could provide the table definition, it would greatly increase your chances of getting an answer quickly.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Kyle
June 17, 2008 at 12:07 pm
Thanks for the response, Kyle.
Yes, in fact my statement had columns initially, I just put the * there to be more concise.
Since then I realized that if I put the column names in parentheses, it works. This is either the correct syntax (which is different form my Oracle background knowledge) or a quirk. Do you know which one?
Thanks,
Gabor
June 17, 2008 at 1:34 pm
On the parentheses thing, do you mean:
insert into t (col1, col2, col3)
select col1, col2, col3
from t
If that's what you mean by putting the columns in parentheses, yes, this is the standard format for T-SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 1:35 pm
You may find that it simply has to do with certain columns not automatically lining up with the "correct" destination. Without specifying the column names, you're gambling a little bit that last name will line up with last name, etc.... If the two tables weren't created in exactly the same way, you could easily end up with column mismatches. In the case where it happens with, say a character column being mismatched against a datetime column, you would see those types of conversion errors (since 'bob' doesn't cast to a datetime, for example).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2008 at 1:51 pm
GSquared (6/17/2008)
On the parentheses thing, do you mean:
insert into t (col1, col2, col3)
select col1, col2, col3
from t
If that's what you mean by putting the columns in parentheses, yes, this is the standard format for T-SQL.
Hello,
No, that's not what I meant. This is what I meant:
insert into t
select (col1, col2, col3)
from t
where the columns do line up with those in the table.
I think this should normally be equivalent to this:
insert into t
select col1, col2, col3
from t
and assuming that the columns line up with those of the table's, both should work. The funny thing is, I need the parentheses to make it work.
Thanks,
Gabor
June 17, 2008 at 2:44 pm
Strange. I've never written a query with parentheses on the select list.
As a matter of fact, I just tried it, and I got a syntax error. Here's what I tried:
create table #T (
Col1 char(1),
Col2 char(1),
Col3 char(1))
insert into #T
select 1,2,3
insert into #T
select (col1, col2, col3)
from #T
select *
from #T
Result:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.
If I run it without the second insert statement, it runs correctly.
Are you sure that what you typed in here is what you ran? If so, please copy and paste the exact script here, as I'd like to try it out.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2008 at 8:03 pm
Matt Miller (6/17/2008)
If the two tables weren't created in exactly the same way, you could easily end up with column mismatches.
Good point... but look again...
insert into t
select * from t
There shouldn't be an implicit conversion or column mismatches.
Of course, this is gonna make dupes which is a problem... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 17, 2008 at 8:16 pm
Jeff Moden (6/17/2008)
Matt Miller (6/17/2008)
If the two tables weren't created in exactly the same way, you could easily end up with column mismatches.Good point... but look again...
insert into t
select * from t
There shouldn't be an implicit conversion or column mismatches.
Of course, this is gonna make dupes which is a problem... :hehe:
I was actually in denial that that was ACTUALLY what he was doing., especially with no WHERE clause....but yup - if that really is the query, then misaligned is probably not an issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2008 at 10:27 pm
Heh... Denial is a good word for this... 😀 I've seen lot's of folks try to make test data this way... Cross Join would suit better for that.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 8:02 am
My first thought was that an ID column would mess that up, since it gets ignored by an implicit insert statement, but included in "select *", but I tested it and it gives the error message about having to turn on identity_insert, not a misplaced column problem.
On the other hand, I'm wondering if this question came from a database other than MS SQL, because of the insert statement the original poster says he used a few posts back. The syntax he used doesn't appear to work in T-SQL, but he says he used it, so maybe it's not an MS SQL database?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply