identity column intert - 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

SQL Server 2005 Forums

Transact-SQL (2005)

identity column intert

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-07-08 : 11:35:15

Hey how do i insert a identity key into a table that has a id column name which is a identy ? am using an select to get the data for my insert like the below example.

intsert into tmp
( id,
name
)
select
name
from tbl1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 11:56:16

[code]
set identity_insert tmp on
insert into tmp
( id,
name
)
select
name
from tbl1
set identity_insert tmp off
[/code]

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-07-09 : 07:51:25

When i use the code below i get the following error

Msg 120, Level 15, State 1, Line 2
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

set identity_insert tmp on
insert into tmp
( id,
name
)
select
name
from tbl1
set identity_insert tmp off

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-09 : 07:58:52

If identity_insert is set to ON you have to give values for id. That is why you set identity_insert to ON.

No, you're never too old to Yak'n'Roll if you're too young to die.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-09 : 08:06:26

[code]set identity_insert tmp on
insert into tmp
( id,
name
)
select
my_id, my_name
from tbl1
set identity_insert tmp off
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources