September 23, 2003 at 10:14 am
I have 1200 databases. How could i use t-sql "alter table ...." to dynamically
deal with a pk int filed with identity yes and identity seed 1, increment 1?
1. turn off the identity of a pk filed?
2. turn on the identity of a pk filed?
3. to change the indentity seed and/or identity increment?
[without drop the field at all]
thanks
David
September 23, 2003 at 11:15 am
You can use the 'SET IDENTITY_INSERT' command to allow you to insert values into the identity column
i.e.
SET IDENTITY_INSERT <table> ON
SET IDENTITY_INSERT <table> OFF
and the DBCC CHECKIDENT command to reseed the identity.
ie
DBCC CHECKIDENT (<table>, RESEED, 30)
Steven
September 24, 2003 at 9:04 am
Interesting and useful DTS behavior, SQL Server 2000 (8.00.818 SP3). (As pointed out elsewhere, capture what DTS is doing using a SQL trace (Profiler tool) and make T-SQL following its example.) Destination db is on this SQL Server, with tables with identity columns and pk indexes on the same columns. Source db is on another SQL Server and has same table definitions. I use Windows authentication on both servers and my userid is in the Windows group that is dbo of each db. Right click on the source db, All Tasks, Export Data. The DTS interface appears. Source is SQL Server and destination is also SQL Server. Copy Table(s) (and views). Checkbox the tables with data you want to copy to the destination. DTS assumes copy to same name table. No "create table" error because "table already exists" (although this error does occur and is non-fatal when DTS exports data from another db on the same server). DTS appends rows to your table if there is no PK violation AND KEEPS THE SAME SET OF VALUES FOR THE IDENTITY COLUMN as in the source db. It seems the identity property has been turned off for the DTS package and then back on again, because a SQL statement inserting a row into the table after this DTS is required to let the identity property fill in the value for that column, and it does so with max+INCREMENT. When this "Export Data" is from a source db on the SAME SERVER, the source values in the source column for the destination column with the identity property are ignored, no error message stops the DTS, and the identity property fills in the values, starting with max-of-data-deleted-from-this-table (if table has been deleted to receive new) + INCREMENT, or with max+INCREMENT.
Edited by - katesl on 09/24/2003 10:36:21 AM
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply