Autonumbering Issue - Very Urgent **

Question

Autonumbering Issue - Very Urgent **

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

premnathk.chn SSC Veteran Points: 280 More actions · Answer 1

yes i a wrong 🙁

only one table have identity_insert on for a given session. so my logic wont work.

Guys my application is an report generator where end user enter values about a company and that information is saved in various tables like company_details,employee_details,financial_details,Awards

etc each of these tables has its own primary key like CompanyDetailsID,EmployeeID,FincancialId,AwardId etc. now while storing datas that are entered by an end user i am using a lookup table for my primary keys which has two columns 1.Tablebname 2.Sequencevalue.Every time i insert i refer to this lookup table with the corresponding table name i am going to insert and gets the sequence value and increments it with one and update the lookuptable.

This all work fine when data is saved from my webApplication but now during the final implementation where i have to pull the existing data from my Client(Bank or financial institution) database i have problem since the script written for that doesn't have any cursor or loops it's just

insert into Mytable (company,addr,phno)

select from client_table(company,addr,phno)

so i thought of turning off and on identity_insert but didnt work for more than one table.

after this data migration my webapplication will move into production where my application users will put data about various companys which will get stored in various where primary keys will get generated with the lookup table logic..

any help to solve this issue will be greatly appreciated

thanks,

prem

premnathk.chn SSC Veteran Points: 280 More actions · Answer 2

also i cannot have a primary key column with Identiy enabled since my web application(Java based) is not coded to handle it that way.

thanks,

prem

James Horsley Hall of Fame Points: 3196 More actions · Answer 3

OK - I guess I misunderstood your requirement - I thought it was just getting in the existing data you had a problem with. Your manual sequence table will probably scale up very poorly under load - and unless you are really careful will quite possibly try to assign the same ID twice if hit by concurrent requests.

There is a good reason why auto-numbering Identity fields have been built in to SQL Server - it is a very common requirement and most efficiently handled in the database engine. I can understand that you may need you system to support manually generated sequences in case it is operating on a database where auto-numbering is not supported but I strongly suggest the change you require is in your code - not in the database - basically you should have some configuration file setting that indicates whether or not you need manual auto-numbering - and the code would be structured something like this:

If manualNumbering then

NewID = GetNumberFromManualTable

Insert Row with ID and all other fields needed

Else

Insert Row with just other fields (not ID)

Select ID from database (IDENT_CURRENT) if need for related table inserts

Endif

James Horsley
Workflow Consulting Limited

premnathk.chn SSC Veteran Points: 280 More actions · Answer 4

No i dont have any such file thats the problem.The component i use for data insert through my web application needs the Id value to be given and it doesnt return any value if have related tables to be inserted. so if i need to change it would be a huge one.We did a load testing with manual primary key generation and it didnt give us any performance problem.

Is there is a way to get rowNumber in select statement so that i can use that row number while trying to do the bulk insert during data migration.

eg:insert into mytable(id,data1,data2)

select rownum value1,value2 from client_table

thanks,

Prem

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

I agree with SSCarpal Tunnel comments that you have created an ugly and non-scalable solution.

"they are mandating that our tables should have identity for primary key "

I agree.

Regarding "database independence", identity works identically with MS SQL Server, Sybase, and SQL Anywhere - that is, there are no SQL changes needed.

For DB2, there is both SEQUENCE and IDENTITY. In this case, IDENTITY should be used as a column default and there are only minor syntax difference to SQL Server.

create table MyTable

(id integer GENERATED ALWAYS AS IDENTITY

(START WITH 1, INCREMENT BY 1,CYCLE, MINVALUE 1, MAXVALUE 999999999)

.....

);

For Oracle, a sequence needs to be defined and then a seven line trigger to use the sequence.

create sequence MyTable_seq;

create table MyTable

(id integer

.....

);

create or replace trigger MyTable_autonumber

before insert on MyTable for each row

begin

if :new.id is null then

select MyTable_seq.nextval into :new.id from dual;

end if;

end;

/

SQL = Scarcely Qualifies as a Language

Shaun McGuile SSCarpal Tunnel Points: 4141 More actions · Answer 6

prem:

Does you client code include the SQL commands for insert, update, select?

If this is the case you've got major issues.

Passing data parameters to Stored Procedures should have been the way to go.

Within a stored proc you could use SCOPE_IDENTITY to retieve the ID of the last record entered and place it in a variable and then use the variable to insert that value into related records in other tables.

Tell us, are you using this method?

--Shaun

Hiding under a desk from SSIS Implemenation Work :crazy:

premnathk.chn SSC Veteran Points: 280 More actions · Answer 7

hi,

i have no problem in the client code writtten i have problem in my we application which is written in java and as of now it doesnt support identity. the component i use in java explicity sends a value for the primary key table. My client code is written as a procedure only.

As i have asked earlier is there is a way generate a rownumber in the select statement.

Otherswise i will have to write a script to drop and add primary key constraint which will be added to my client code.

Thanks & regards,

Premnath

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Did you ever play a scenario when 2 or more users at the VERY SAME MOMENT are trying to save a record in your table?

How your java code will know about other treads running in parallel?

How will it resolve the collision?

Remember - 2 identical SELECT statement running in parallel against the same data will always return THE SAME result.

But YOU NEED DIFFERENT results for different threads, one of them to take into account number of rows to be inserted from another one (but not yet inserted).

Are you sure your code is good enough to handle such scenario?

_____________
Code for TallyGenerator

premnathk.chn SSC Veteran Points: 280 More actions · Answer 9

yes i am sure and we have also tested.The particular block is synchronised so it can be accessed by one thread at a time . It's equal to locking the table in SQL.

thanks,

Prem

Sergiy SSC Guru Points: 110208 More actions · Answer 10

premnathk.chn (5/19/2008)
yes i am sure and we have also tested.

Sure you did.

Everybody does.

Of course, you wrote proven code which will decide which of threads being held by a thread currently being executed will go next and preventing others from applying the lock.

And for sure you have 100% reliable network, so you'll never get broken connections and you'll never face abandoned locks.

Man, I'm not gonna convince you.

Just copy-paste my post, your response, actually the whole thread, print it out and save in some secret pocket.

After several month after going to production, when users start complaining, open it and read again. I believe you'll have quite different attitude at that time, just like guys I'm watching struggling with similar solution they defended so hard just a year ago.

_____________
Code for TallyGenerator