set identity_insert problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

set identity_insert problem

Author

Topic

bill_
Starting Member

38 Posts

Posted - 2010-05-27 : 09:00:16

Trying to put query rows in a table variable but getting error that says:

an explicit value for the identity column in table @tv1 can only be specified when a column list is used and IDENTITY_INSERT is ON

Tried set identity_insert @tv1 on in this code - no luck

declare @tv1 table
(
id identity(1,1)
,desc varchar(30)
)
insert into @tv1 select * from tbltest

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 09:06:02

declare @tv1 table
(
id identity(1,1)
,desc varchar(30)
)
insert into @tv1(desc)
select desc from tbltest

Madhivanan

Failing to plan is Planning to fail

bill_
Starting Member

38 Posts

Posted - 2010-05-27 : 09:32:01

Thank you very much.

Kristen
Test

22859 Posts

Posted - 2010-05-27 : 09:39:14

You need data type for IDENTITY, and preferably NULL / NOT NULL for ALL columns in the table

"desc" is a reserved word, so you'll need square brackets around that - or use a better name

Although I don't approve you can ommit the INSERT column list, provided you provide exactlt the right numebr fo columns EXCLUDING the IDENTITY column, so this would work:


declare @tv1 table
(
	id INT identity(1,1) NOT NULL 
	, [desc] varchar(30) NULL
)
insert into @tv1 select [desc] from tbltest
OR
insert into @tv1 select * from tbltest -- Provided it has same number of columns as @TV1 excluding the IDENTITY column

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 11:23:40

quote:
Originally posted by Kristen

You need data type for IDENTITY, and preferably NULL / NOT NULL for ALL columns in the table

"desc" is a reserved word, so you'll need square brackets around that - or use a better name

Although I don't approve you can ommit the INSERT column list, provided you provide exactlt the right numebr fo columns EXCLUDING the IDENTITY column, so this would work:

declare @tv1 table
(
id INT identity(1,1) NOT NULL
, [desc] varchar(30) NULL
)
insert into @tv1 select [desc] from tbltest
OR
insert into @tv1 select * from tbltest -- Provided it has same number of columns as @TV1 excluding the IDENTITY column

But I always prefer listing out the columns which wont throw errors if columns are changed

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-05-27 : 11:31:50

" But I always prefer listing out the columns which wont throw errors if columns are changed"

Absolutely agree 100%

Although ...

... if my expectation is to keep Source and Target in sync then I would want error when column added on one side, but not the other, so that might be a case for using SELECT * and forcing the "default behaviour"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources