Identity column error - 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 2008 Forums

SQL Server Administration (2008)

Identity column error

Author

Topic

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2009-12-16 : 03:12:58

I am using sql server 2008 ..

i want to import data from one database into another database ..
i deleted data from second database ..and trying to import from first database ..in both database PK of all tables is set to identity column ...

now during insert ...it is saying error in inserting
read only column 'rec_num' ....
how i can resolve this error ...?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-12-16 : 05:44:57

Either SET IDENTITY_INSERT <TableName> ON

Or give a fieldlist and exclude the identity field.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-16 : 05:47:06

Don't forget to OFF After Insert

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

savior faire
Posting Yak Master

194 Posts

Posted - 2009-12-31 : 15:07:22

I ran into this issue the other night, while going through a study guide. I searched the SQL Books Online and there is a command where you can reset the Identity settings, on the table. Meaning if the identity is specified as Identity(1,1), I forget the exact syntax, and data has already been added to the table, but you want to re-load, you can reset the values so that you start from the initial values.
Sorry but I forget the command.

Talk sense to a fool and he calls you foolish.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-31 : 17:16:41

You are probably thinking of DBCC CHECKIDENT, however that does not reset the existing data. It's purely to change the current identity value so that all future inserts start from that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

savior faire
Posting Yak Master

194 Posts

Posted - 2010-01-01 : 11:05:28

Tara, I forget the command. I needed to remove all entries in the table with the key as Identity. Then reload from an extracted source, and needed to retain the original key values.

______________________________________________
Talk sense to a fool and he calls you foolish.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 11:50:40

http://www.sqlteam.com/article/understanding-identity-columns

EDIT: And please see what RickD has posted.

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 11:58:36

And see this:
http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

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

rohitmathur11
Yak Posting Veteran

77 Posts

Posted - 2010-01-18 : 09:08:36

In my production database PK is used as a identity column for all tables .. Now i need to import data from prod dtabase to another database.. i am using import wizard in sql server management studio..
to import indintity column for each table we need to go to edit > sheck enable identity ... there are 200 tables ..

Can we set identity ON or OFF in one sql statement for all tables ..
or ..any other way ..

any idea ..

Kristen
Test

22859 Posts

Posted - 2010-01-18 : 09:34:05

"Can we set identity ON or OFF in one sql statement for all tables .."

Not in the same database connection, no.

You can do


SET IDENTITY_INSERT TableA ON

INSERT INTO TableA SELECT Col1, Col2, ... FROM OtherDatabase.dbo.ThatTableA

SET IDENTITY_INSERT TableA OFF

SET IDENTITY_INSERT TableB ON

INSERT INTO TableB SELECT Col1, Col2, ... FROM OtherDatabase.dbo.ThatTableB

SET IDENTITY_INSERT TableB OFF

...

would that do?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources