sql server programming: using identity on select into -- sql server programming

RE: using identity on select into

Alejandro Mesa
6/18/2007 10:20:02 AM

sql server programming:

Roy Goldhammer,

Yes, cast that column to the same datatype.

create table #t1 (
c1 int not null identity
)

insert into #t1 default values

select cast(c1 as int) as c1, identity(int, 100, 2) as c2
into #t2
from #t1

select * from #t2

drop table #t1, #t2
go

AMB

[quoted text, click to view]

Re: using identity on select into

Tony Rogerson
6/18/2007 6:16:03 PM

Use CAST on it....

use tempdb
go
create table source (
id int not null identity,
blah int )

insert source( blah ) values ( 1234)
go

-- Errrors...
select identity( int, 1, 1 ) as new_id, id, blah
into dest
from source

-- Works....

select identity( int, 1, 1 ) as new_id, id = cast( id as int ), blah
into dest
from source

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

[quoted text, click to view]

using identity on select into

Roy Goldhammer
6/18/2007 8:03:06 PM

Hello there

I'm creating new temp table using select into statement.

the temp table has has 2 main fields:
rowid id identity(1, 1),
Sign bit

and all other fields from another table choosed dinamicly by parameter.

it works fine exept tables which has their own identity field. in this case
the select into statement set error:
Msg 8108, Level 16, State 1, Line 1

Cannot add identity column, using the SELECT INTO statement, to table
'TempTable.dbmReferenceModel_dbo_FunctionalInterfaceParameters_DBMOTION_royg',
which already has column 'FIParameterId' that inherits the identity
property.

Is there a way to make sure that the identity field from the source table
will transfare as normal table and not as identity in order to solve the
problem?

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.