Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Identity insertion and updation Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2007 3:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, December 19, 2007 4:10 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 1,451, Visits: 2,911
See SET IDENTITY_INSERT in BOL



Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #434697
Posted Wednesday, December 19, 2007 5:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 6,997, Visits: 8,410
- 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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #434722
Posted Monday, December 24, 2007 12:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, December 24, 2007 1:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 6,997, Visits: 8,410
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #436113
Posted Monday, January 07, 2008 12:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 AM
Points: 6,997, Visits: 8,410
I've just received this article and thought it might also help you :

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


Johan


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #439459
Posted Tuesday, January 08, 2008 12:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, January 08, 2008 12:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
use sp_msforeachtable to drop the identity column in all tables.


Post #439895
Posted Tuesday, January 08, 2008 12:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
use sp_msforeachtable to drop the identity column in all tables.


Post #439896
Posted Tuesday, January 08, 2008 2:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase 12»»

Permissions Expand / Collapse