identity in select into table
-
Hello everybody,
I'm having a strange situation with a query.
Can anyone explain this behaviour?
I have two tables : A and B
CREATE TABLE A (
col1 int IDENTITY (1, 1) NOT NULL ,
col2 char (10) NOT NULL
)
GO
CREATE TABLE B (
a char(10) NOT NULL ,
b int NOT NULL
)
GO
When I do the next query:
select A.col1, B.b
into AUX
from A, B
where A.col2 = B.a
The target table AUX doesn't have an identity column, as you see:
select objectproperty(object_id('AUX'),'TableHasIdentity')
--> 0
But if I do:
select A.col1, 111
into AUX2
from A
where A.col2 = 'Hola'
The target table has an identity, as you see below:
select objectproperty(object_id('AUX2'),'TableHasIdentity')
--> 1
I see that the diference is that there is a join in the from.
I want that my target table doesn't have and identity, but without doing the join. How can I do this???
Thanks in advance, and sorry for my english
-
Since you've explicity created the target table, you should not be using "SELECT/INTO"... use INSERT INTO/SELECT instead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, I might miss something, but I don't the table AUX explicitely created in Julia's script.
Anyway, I get an error when I try to run
select A.col1, 111
into AUX
from A
where A.col2 = 'Hola'
Server: Nachr.-Nr. 8155, Schweregrad 16, Status 1, Zeile 1
Keine Spalte wurde für die Spalte 2 von 'AUX' angegeben.
But like Jeff already said, I would first create the table and then do INSERT INTO, that way you also avoid problems with recompiles
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yeah, you're right Frank... Not enough coffee
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello again,
And thanks a lot, but I know that I can create the table first, and then do the 'select .. into' , but i wonder if it would have other solution.
Thanks!
-
Any specific reasons for it?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
No, not an specific reason...
Only to know if it's possible.
I wanted to know why this behaviour, but now that I know that this is the way SQL Server do that kind of things, I will do the things in other way.
Thanks a lot to everyone!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply