adding rows to a table with an identity column

Question

Post reply

adding rows to a table with an identity column

winston Smith

SSCoach

Points: 19500
More actions
March 15, 2006 at 10:53 am

#96687

i have a table with a load of columns called tableA.
the 1st column is an identity column.
i have another table in the exact same formatc called tableB, but with no identity col.
i want to insert the rows from tableB into tableA.
i tried this
tableA
select * from tableB
this gives me this error:
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'tableA' can only be specified when a column list is used and IDENTITY_INSERT is ON.
so i set the IDENTITY_INSERT to ON for tableA and try again and get the same error.
the only way i can find to insert the rows is individually, but that could take ages.
does anyone know a way around this??

Viewing 15 posts - 1 through 15 (of 40 total)

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

JeffB Hall of Fame Points: 3541 More actions · Answer 1

Try something like this:

drop table #table1

go

drop table #table2

go

create table #table1 (id int identity(1,1), name varchar(10))

insert #table1 values('jeff')

insert #table1 values('mary')

insert #table1 values('edward')

create table #table2 (id int, name varchar(10))

insert #table2 values(1, 'james')

--if you want new ids

insert #table1(name)

select name from #table2

--if you want the same ids but there can be duplicates

set identity_insert #table1 on

insert #table1(id, name)

select id, name from #table2

set identity_insert #table1 off

select * from #table1

Sergiy SSC Guru Points: 110208 More actions · Answer 2

> a table with a load of columns

Stop right here.

You don't need to go anywhere further.

You have already failed.

_____________
Code for TallyGenerator

Ian Yates SSCoach Points: 19738 More actions · Answer 3

Gee that's a bit harsh

Anyhow, your code

insert into tableA

select * from tableB

and the error message

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'tableA' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Tell you exactly what is wrong... Yes you've done the IDENTITY_INSERT bit, but it also says "When a column list is used"....

You'll need to do

insert into tableA(col1, col2, col3) /* always good idea to specify these */

select col1, col2, col3

from tableB

Using select * for such things is inviting hard to detect trouble down the line if you change any of your table definitions... Also applies if your app is expecting columns in a certain order - you are better returning the columns by name to guarantee the order rather than relying on the order of columns in the table (which could change as you perform maintenance).

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

As Ian described, you need to explicitly list the columns in both the insert clause and the select clause. You do not need to include the identity column in the insert clause, just list the rest of the columns.

And here is a tip if you don't want to type out all the columns: Use Query Analyzer, select the table for your insert, right-click on the table name, and select "Script to New Window as" then select "Insert". You will get an insert statement with all the columns listed. Do the same thing for your TableB but select the "Select" menu option, to create the select clause for the rest of your insert statement.

Hope this helps. (at least helps more than telling you that "you already failed")

Mark

Sergiy SSC Guru Points: 110208 More actions · Answer 5

> Hope this helps. (at least helps more than telling you that "you already failed")

Probably helps. For today.

And leads to a deeper trouble tomorrow.

"The road to hell is paved with good intentions"

_____________
Code for TallyGenerator

Ian Yates SSCoach Points: 19738 More actions · Answer 6

But it's no good saying "you failed" without at least saying why and how things could be improved...

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Seems, i explained the reason clearly:

"a table with a load of columns"

It's against any of relational database rules and all of them together.

So it's failure. And there is no point to waste time to make some workarounds.

Normalisations - that's what this database requires.

_____________
Code for TallyGenerator

RyanRandall SSChampion Points: 13623 More actions · Answer 8

Sergiy - does a table with a load of columns imply that it's not normalised?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

Can you name any entity with more than 10 different profiles to describe it?

_____________
Code for TallyGenerator

RyanRandall SSChampion Points: 13623 More actions · Answer 10

Erm... me? Does that count?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

First Name, Second Name, Last Name, Date of Birth.

What else do you have in your passport?

_____________
Code for TallyGenerator

RyanRandall SSChampion Points: 13623 More actions · Answer 12

Come on, Sergiy, there's more to me than my passport. You can do better than that...

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Does your passport identify you completely?

And what else do you need to identify a person?

_____________
Code for TallyGenerator

RyanRandall SSChampion Points: 13623 More actions · Answer 14

I don't know about identifying me, but I have loads of attributes which help to describe me. I'm pretty sure I do, any way.

Do I really have to start naming them?

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.