Identity insertion and updation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » Identity insertion and updation

15 posts, Page 1 of 2

1 2 »»

Identity insertion and updation

Rate Topic

Display Mode

Topic Options

Author

Message

balaji_rcs

Posted Wednesday, December 19, 2007 3:30 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 9:23 AM
Points: 30, Visits: 93

I have a lot of tables with identity column. I want to remove the identity properties of all the tables while inserting and updating the tables.

Post #434683

Carolyn Richardson

Posted Wednesday, December 19, 2007 4:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:52 AM
Points: 1,397, Visits: 2,738

See SET IDENTITY_INSERT in BOL

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists

Post #434697

ALZDBA

Posted Wednesday, December 19, 2007 5:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049

- Maybe creating a DTS package to load your tables may be the way to go, because it has the option "keep identities"

-If using set identity_insert on, keep in mind you have to list all columns in your insert statement.
e.g.
set identity_insert on
insert into mytable(col1,col2,col333,...)
select * from mysourcetable
set identity_insert off

dbcc checkident('mytable') -- check bol

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #434722

balaji_rcs

Posted Monday, December 24, 2007 12:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 9:23 AM
Points: 30, Visits: 93

Hi,

Thanks for your reply. I want to remove the identity properties in the table totally. i.e I want to remove the identity while update also.

Post #436103

ALZDBA

Posted Monday, December 24, 2007 1:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049

if you want to completely get rid or the usage of the identity property, you'll have to generate drop/create table statements !!
Keep in mind, that there is a purpose for identity ! (automatic increment at insert time.)

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #436113

ALZDBA

Posted Monday, January 07, 2008 12:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049

I've just received this article and thought it might also help you :

http://www.mssqltips.com/tip.asp?tip=1397

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #439459

balaji_rcs

Posted Tuesday, January 08, 2008 12:03 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 9:23 AM
Points: 30, Visits: 93

Thanks for your reply. I've nearly 700 tables, and more than 500 tables containing identity column and i want to remove the identity property without dropping/creating the table.

Post #439893

Kishore.P

Posted Tuesday, January 08, 2008 12:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, November 26, 2012 3:25 AM
Points: 1,389, Visits: 596

use sp_msforeachtable to drop the identity column in all tables.

Post #439895

Kishore.P

Posted Tuesday, January 08, 2008 12:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, November 26, 2012 3:25 AM
Points: 1,389, Visits: 596

use sp_msforeachtable to drop the identity column in all tables.

Post #439896

balaji_rcs

Posted Tuesday, January 08, 2008 2:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 9:23 AM
Points: 30, Visits: 93

Hi Kishore,

Thanks for your reply. Let me know how to use this procedure for removing the identity column of all tables. And can you tell me about this procedure.

Post #439935

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions