May 13, 2008 at 3:03 am
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
May 13, 2008 at 4:15 am
Also asked here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102718
N 56°04'39.16"
E 12°55'05.25"
May 15, 2008 at 1:49 am
Hi Premnath
i think you no need to enable and disable the indentity . and not need to call any function for auto numbering . please see the following code, i hope it will help you
you simply insert the table with the use of script excluding the field which you want as a auto number after you do the following update statement as per your requirement
declare @count int
set @count = 0
update
set @count = = @count +1
May 15, 2008 at 1:54 am
Hi Premnath
i think you no need to enable and disable the indentity . and not need to call any function for auto numbering . please see the following code, i hope it will help you
you simply insert the table with the use of script excluding the field which you want as a auto number after you do the following update statement as per your requirement
declare @count int
set @count = 0
update "table"
set @count = "column" = @count +1
May 15, 2008 at 2:05 am
Hi shamshudheen ,
Thanks for your intreset.I could do the way u have mentioned if the column is not a primary key but unfortunately its a primary key so i cannot insert without it may be i should drop the constraint before doing it.Is it correct?
Thanks ,
Prem
May 15, 2008 at 2:16 am
yes you are right, drop the constraint and after updation ADD the constraint
cheers
May 15, 2008 at 2:23 am
Hi ,
I thought of dropping and adding primary keys but the problem is that the script turns out to be more complex since there are certain tables whose primary keys are referenced as foregin keys.There are totally 500 tables to which data has to be migrated.Is there is any tool to get the scripts or is any workaround
Thanks,
Prem
May 15, 2008 at 7:03 am
premnathk.chn (5/15/2008)
Hi ,I thought of dropping and adding primary keys but the problem is that the script turns out to be more complex since there are certain tables whose primary keys are referenced as foregin keys.There are totally 500 tables to which data has to be migrated.Is there is any tool to get the scripts or is any workaround
Thanks,
Prem
In how many table you want auto numbering ?
are you from chennai?
May 15, 2008 at 7:16 am
premnathk.chn (5/13/2008)
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
I don't know if you noticed but you have a paradox in your problem description. You want to autogenerate ID for a database, but you don't want to "rely on a database", so you create a (database) table. That's like a dragon swallowing its own tail...twice.
You're quite honestly inviting a lot of ugliness and painful issues onto yourself with this kind of scenario. This is the kind of thing that works just fine in testing (when most testing is done as a single-user), and then utterly falls apart when you put it into a mutli-user setting. Besides the fact that this happens to be a spot where reinventing the wheel is incredibly costly, and inefficient, often leading to a LOT of blocking/deadlocks, etc..., it's also notoriously difficult to keep from generating duplicate values.
Best advice I can give you - if you need auto-generated ID's, then use the built-in method for that. It works well (about the only method for doing this that does), and doesn't drag your system down in the process......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 9:00 am
If I understand you correctly the system will use Identity in the new database but your problem is pushing in existing data that already has ID's allocated via you manual key table method. If so just do
SET IDENTITY_INSERT YOURTABLENAME ON
-- Whatever SQL you need to populate your table including setting the ID to value from the place you are populating it
SET IDENTITY_INSERT YOURTABLENAME OFF
That allows you to force values into a table col that normally autonumbers - SQL then just carries on autonumbering from the max val that was in the data you inserted
James Horsley
Workflow Consulting Limited
May 15, 2008 at 9:27 am
James, I think you are right. SET IDENTITY_INSERT YOURTABLENAME ON should perform the same function as (in DTS) checking the Enable Identity Insert box, right?
Seth
_________________________________
seth delconte
http://sqlkeys.com
May 16, 2008 at 2:58 am
premnathk:
Are your identity columns anything other than surrogate keys?
If no then why can you not scrap the current identities and start again because the actual values used only matter for relating the correct rows together?
I think you could add an 'old key value' column to the new tables where necessary.
e.g. first table to add data to has an auto incrementing id column as primary and an oldValue column.
when inserting the data put the old primary key value into the oldValue column and let the new primary id auto generate.
This way you do not have to drop any constraints, just remove the oldValue columns once done.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
May 16, 2008 at 3:09 am
Hi all,
Thanks for the interest you all have shown.
I have completed my requirement by doing identity insert off during the datamigration and then turning it on at the end of the migration this way i can use my own primary key generation logic in my web application where i will insert values in those tables with the data entered by the user.I have synchronized that logic so it will not allow more than one to access the primary key table.
Thanks ,
Prem
May 16, 2008 at 3:18 am
premnathk.chn (5/16/2008)
Hi all,Thanks for the interest you all have shown.
I have completed my requirement by doing identity insert off during the datamigration and then turning it on at the end of the migration this way i can use my own primary key generation logic in my web application where i will insert values in those tables with the data entered by the user.I have synchronized that logic so it will not allow more than one to access the primary key table.
Thanks ,
Prem
You still won't be able to insert values into an auto increment identity column without using 'identity insert off' no matter what you do.
Why are you generating data in the client end of you application? Data manipulation other than for display purposes in the client is just wrong!
Hiding under a desk from SSIS Implemenation Work :crazy:
May 16, 2008 at 3:25 am
Seth Delconte (5/15/2008)
James, I think you are right. SET IDENTITY_INSERT YOURTABLENAME ON should perform the same function as (in DTS) checking the Enable Identity Insert box, right?Seth
Correct
James Horsley
Workflow Consulting Limited
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply