Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Unanswered: Alter a Column to and Identity [Solved]

    I am in the middle of making an install program that will Install SQL Express on a Laptop and dump the database to it. To make the dump run right I have created the table structure without the Identity Fields(Columns) so that the data dump will go much faster...

    CREATE TABLE [dbo].[AdministrationData] (
    -- [ID] int IDENTITY(1, 1) NOT NULL,
    [ID] int NOT NULL,
    [Inspector] int NOT NULL,
    [Time] float NOT NULL,
    [Task] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Date] datetime CONSTRAINT [DF_AdministrationData_Date] DEFAULT getdate() NOT NULL,
    [FID] int NULL,
    [PermitID] int NULL
    )
    ON [PRIMARY]
    GO

    After the raw data dump I want to add the Referential Integrity (Key, Constraints and so on). In my Create Integrity script I have added the Alter Table Alter Column scripts to make my Identity Columns...

    ALTER TABLE [dbo].[AdministrationData] (
    ALTER COLUMN [ID] int IDENTITY(1, 1) NOT NULL
    )
    GO

    The issue is the Seed. I am worried about it being 1. Is there a way to use a Select Max to set the seed to the last record in the table?

    P.S. I do not want to drop the column and recreate it to let the system auto generate the Key fields because it will cause foreign key issues with the raw data and the master data that is on our server. Also, the Keys have not been created at this point. I am adding the keys after this script.
    Last edited by AceOmega; 03-29-12 at 18:47.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    To the best of my knowledge, it is not possible to add identity to a column. Things may have changed since 2k. :-)

    My limited suggestion is to not set the seed value and set the insert identity on. Haven't tried it, but this is just off the top of my head.
    Last edited by corncrowe; 03-29-12 at 13:25.

  3. #3
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    OK, I will try that. I am currently trying it with a seed of 1 and then go in and insert a record and see what happens. It just takes time for it to dump the data and it is still running. I will get back to you.

    Thanks.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by AceOmega View Post
    OK, I will try that. I am currently trying it with a seed of 1 and then go in and insert a record and see what happens. It just takes time for it to dump the data and it is still running. I will get back to you.

    Thanks.
    I was successful in creating a table with the identity, set the identity seed on, insert into table with values including identity, turned off the insert seed option, inserted a new value and it incremented correctly.

    I inserted a few more rows of data and they incremented correctly.

    Should work for you. Good luck!

  5. #5
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75
    Can you post the script you used?

    I finished my dump but there seems to be no way of using Alter Column to add an identity just like you just said that it would not work, in your first post. (I wish Microsoft would fix this.) I have seen some suggestion as to creating a new temporary column as an Identity then copy the data from the old column into the new then drop the old column and rename the new column to the old.

    The major problem which I was trying to get around in the first place is that this is very slow. That's wy I simply created the tables with out identities in the first place because the data dumps much faster from the server to the local database when there are no identities.

    Its funny. When I first start copying the data to a table that has an Identity Column, it start fast but progressively gets slower the more records it copies. I think I will try to put each copy in a separate transaction to see if it goes any faster.

    Thanks for all of your help.
    Last edited by AceOmega; 03-29-12 at 14:24.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by AceOmega View Post
    Can you post the script you used?

    I finished my dump but there seems to be no way of using Alter Column to add an identity like you said in your first post. I have seen some suggestion as to creating a new temporary column as an Identity then copy the data from the old column into the new then drop the old column and rename the new column to the old.

    The major problem which I was trying to get around in the first place is that this is very slow. That's wy I simply created the tables with out identities in the first place because the data dumps much faster from the server to the local database when there are no identities.

    Its funny. When I first start copying the data to a table that has an Identity Column, it start fast but progressively gets slower the more records it copies. I think I will try to put each copy in a separate transaction to see if it goes any faster.

    Thanks for all of your help.
    My first post was "You can't alter a column and add identity".

    P.S. I don't know what data dump method you are using, but my example would not be good for large volumes of data. BCP is the way to go.

    Here is a very simplistic example:


    Code:
    CREATE TABLE #temp_tbl
    (id int identity,
    spell VARCHAR(5),
    spellindicator TINYINT,
    episode TINYINT)
    GO
    
    SET IDENTITY_INSERT #temp_tbl ON 
    INSERT INTO #temp_tbl (ID,spell,spellindicator,episode)
    SELECT 1,'A1234',1,2
    UNION ALL
    SELECT 2,'B5674',1,1
    UNION ALL
    SELECT 3,'C8739',2,3
    UNION ALL
    SELECT 4,'A1234',1,1
    UNION ALL
    SELECT 5,'B5674',2,1
    UNION ALL
    SELECT 6,'A123',2,2
    UNION ALL
    SELECT 7,'A1234',1,3
    
    SET IDENTITY_INSERT #temp_tbl off 
    INSERT INTO #temp_tbl values ('Ac264',1,3)
    
    SELECT t1.id,T1.spell,T1.spellindicator,T1.episode
    FROM #temp_tbl T1
    
    DROP TABLE #temp_tbl
    Last edited by corncrowe; 03-29-12 at 14:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •