identity in select into table

Question

identity in select into table

Julia Esteban

Right there with Babe

Points: 753
More actions
November 13, 2004 at 10:04 am

#182940

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 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

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)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

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]

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

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)

Julia Esteban Right there with Babe Points: 753 More actions · Answer 4

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!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]

Julia Esteban Right there with Babe Points: 753 More actions · Answer 6

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!!!