Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
prem.chn
Starting Member
2 Posts |
 Posted - 2008-05-13 : 04:57:30
|
| 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 valueeg:Table_Name Current_SequenceUSER_DETAILS 23CITY_MASTER 111TableA 122so 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 thisInsert into TABLEA(firstname,middlename,phoneno)select first_name,middle_name,ph_no from LOOKUP_TABLE_Bthey 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 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-13 : 06:38:21
|
| You can script the chages from the database structure. Just generate a script that works on all necessary tables.You can't enable and disable an identity - you can add and drop the column though.select name into #a from sysobjects where xtype = 'U'select 'alter table ' + name + ' add id int indentity (1,1)' from #aselect 'alter table ' + name + ' drop column id' from #a>> 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.This means inserting one row at a time which will be very very slow. I would only try this as a last resort.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
prem.chn
Starting Member
2 Posts |
Posted - 2008-05-14 : 03:17:21
|
hi, Thanks for the reply. I cannot drop the primary key column since i would use it in my application after the datapush .I missed one column in table 'A'.It has the following columnsID*,firstname,middlename,phoneno.. Now i need to generate the numbers for the prmary key column 'ID' regards,Prem |
|
|
|
|
|
|
|
|
Issue with autonumbering SQL server - Very urgent