SQL Server Performance

AutoNumbering Issue- Very Urgent

Discussion in 'General Developer Questions' started by prem.chn, May 13, 2008.

  1. prem.chn New Member

    Hi All, I am at the final stage of my project(developed in java and db as sqlserver 2000) and now we are doing the data migration and we have this problem. I have a table 'A' with a primary key and i am trying to insert 4 million records into that table from another table 'B'.I dont have identity enabled in the table 'A' to populate its primary key value since that would make it database dependent so while doing my java coding i used a 'primary key' table where i will have a correponding entry for each tables primary key and their current value eg: Table_Name Current_Sequence USER_DETAILS 23 CITY_MASTER 111 TableA 122 so when ever i need to insert into table 'A' i will query the 'PrimaryKey' table and get its current value and then update it by one. The SQL which is written for the Datamigration is done by someother team at client place where they have this lookup table 'B' for the data they have to push into our Table 'A' thier script to insert data looks like this Insert into TABLEA(firstname,middlename,phoneno) select first_name,middle_name,ph_no from LOOKUP_TABLE_B they are mandating that our tables should have identity for primary key and if i do that its going to be big change. so i dont want to do that so i tried to enable identity and disable it after datapush since it would be mostly a one time activity but its taking lot of time to code(575 Tables) as i have to remove the relationship for these primary keys and again add those relationship. I tried to add a function which will take the parameter of the table name and tried to select the current value from primary_key table and update it with currentvalue plus one but update is not allowed in UDF. Can anyone please tell me an efficient way other than this? thanks, prem
  2. Adriaan New Member

    The most efficient way would be (1) add an identity column, and write out the column lists for the insert query (omitting the identity column), (2) fill the lookup table(s) before the main tables, (3) make sure you know what the natural key is to lookup tables, so you can join the lookup_tables in the insert query for the main tables.
  3. Bob L New Member

    I am new to the forum, so forgive me if I am getting this whole situation wrong.
    When inserting the rows from one table in a database to another table in another database, you can SET IDENTITY_INSERT ON, which turns off the automatic creation of the identity value, and allows you to set the value with the SELECT..INTO.. statement. Then, turn it back off, so that the automatic creation is turned back on. This could be done in a script. Of course, this only works if there is no overlap of the identity values in the two tables.
    Also remember to commit the inserts every 50,000 records or so. Keeps the overhead low. Have read of errors encountered with large numbers of records being inserted without committing often.
    Hope this helps in some minute way....
  4. prem.chn New Member

    Hi Bob,
    Thanks for your intreset.I dont have the value for primary key while doing the data migration that's the issue.But i got a clue from your post.May be i should always have an identity column for my primary key and turn Identity insert off when i do my data migration and then i should turn it on after the migration is completed.Since when the data is getting inserted via my Web application i will have a value for the primary key column. My only worry is will this affect the performance by any chance [:S]
    thanks,
    prem
  5. rohit2900 Member

    Prem,
    Can you do one thing drop the PK column from table A and then do insert and after insert try this.

    alter table a add pk int identity(1,1) not null

    you don't need to insert it manually it will automatically will insert the pk for all 4 million records.
    Rohit



Share This Page