|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
how to write extended stored procedure to generate sequences.I have one table called sequence_number with two columns Seq_name nvarchar(50) last_num bigint. I have one function, in that i am calling extended stored procedure. this XP has to return the next seq number based on the sequence name we have given. My intension is I have get the db connection select the last_num from table update the sequence_number table with last_num+1 and reurn the value to the function. Can you please guide me how to do that. If you have sample code plase send it to me Thanks in advance Regards Veeru Why would you need an extended procedure for this? Why would you even have to
write a new sequential value into the table before actually using it? This can all be done simply by using locks appropriately. In pseudo code: 1) read the max value, lock the table to prevent inserts; 2) create new value; 3) insert the row using the new value; 4) release locks. For the duration of the above transaction no other transaction can insert a new row, so the sequence is safe. Of course this also means that any inserts would be blocked, but this is the price to pay if you need a clean sequence. I'd just use IDENTITY and a maintenance procedure to fill any possible gaps in the sequence with dummy rows (i.e. to persuade the tax man that all receipts have valid numbers ;). If you post DDL we can help you design a proper solution. ML --- http://milambda.blogspot.com/ Hi ML
Thanks for you response, I am going to use the sequence function in INSERT.. OPENROWSET function to load the flat file data into the database. The sequence should be unique in the database (I am not going to use newid()) level, not at table level. suppose if I check the condition like below the sequence should generate in INSERT.. SEKECT OPENROWSET( BULK If 'I' then sequence_num('ABC') else sequence_num('DEF') inside this I would like to call XP to get the value and update the same value in the table. before call the Xp the table values are seq_name last_num ABC 1 DEF 0 after the XP call has made the table values should be if it is 'I' seq_name last_num ABC 2 DEF 0 Regards Veeru Show quote "ML" wrote: > Why would you need an extended procedure for this? Why would you even have to > write a new sequential value into the table before actually using it? > > This can all be done simply by using locks appropriately. > > In pseudo code: > > 1) read the max value, lock the table to prevent inserts; > > 2) create new value; > > 3) insert the row using the new value; > > 4) release locks. > > For the duration of the above transaction no other transaction can insert a > new row, so the sequence is safe. Of course this also means that any inserts > would be blocked, but this is the price to pay if you need a clean sequence. > I'd just use IDENTITY and a maintenance procedure to fill any possible gaps > in the sequence with dummy rows (i.e. to persuade the tax man that all > receipts have valid numbers ;). > > If you post DDL we can help you design a proper solution. > > > ML > > --- > http://milambda.blogspot.com/ In this case I'd suggest you use two staging tables with the IDENTITY coulmn,
then insert one set of rows (" if 'I' ") into one staging table and the other set (" if not 'I' ") into the other. After that you can easily join the two sets in one final destination. ML --- http://milambda.blogspot.com/ Hi
Can you please the post with the subject as "Can I call stored procedure inside the case statement" You can understand more, what is the my exact requirement. the flat file contains so many records, not a single record. I have a procedure to generate sequences. but unfortunatly sql server is not supporting that use in OPENROWSET statement please guide me with an example Regards Veeru Show quote "ML" wrote: > In this case I'd suggest you use two staging tables with the IDENTITY coulmn, > then insert one set of rows (" if 'I' ") into one staging table and the other > set (" if not 'I' ") into the other. > > After that you can easily join the two sets in one final destination. > > > ML > > --- > http://milambda.blogspot.com/ As far as I can see the solution to your problem has been suggested by
Omnibuzz (http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=aec1ca59-f932-4546-af0a-8da539e0833c&sloc=en-us). He also suggested using two staging tables that provide two sets of rows that can be joined in a single destination table. Have you also made sure that your data model is properly normalised? ML --- http://milambda.blogspot.com/ Hi,
He only suggested in his last post across the tables we cant generate the sequences and use XP. I have one doubt, How we can return the values to output parameters in Xp Veeru Show quote "ML" wrote: > As far as I can see the solution to your problem has been suggested by > Omnibuzz > (http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=aec1ca59-f932-4546-af0a-8da539e0833c&sloc=en-us). > He also suggested using two staging tables that provide two sets of rows that > can be joined in a single destination table. > > Have you also made sure that your data model is properly normalised? > > > ML > > --- > http://milambda.blogspot.com/ Maybe you should explain in more detail the requirements here. Yes, I agree
that an extended procedure may not be of help here, but then again I really see no need for it at all. You're talking about two (or more?) sets of rows that need to be imported from a flat file into a table. In the destination table you want to create a surrogate key for the rows, that's globally unique across the database. Is that correct? If it is, create two (or more) tables, each with its own definition for an identity column (such as Omnibuzz suggests) in order to create two (or more) sets of data with globally unique identity values. E.g.: create table Staging1 ( StagingId int identity (1, 2) ,<the rest of the columns> ) go cerate table Staging2 ( StagingId int identity (2, 2) ,<the rest of the columns> ) go insert Staging1 ( <column list> ) select <column list> from opensource(...) where (<condition> = 'I') go insert Staging2 ( <column list> ) select <column list> from opensource(...) where (<condition> <> 'I') go insert <final destination> ( <column list> ) select <column list> from Staging1 union all select <column list> from Staging2 go ML --- http://milambda.blogspot.com/ HI
my requirement is I need to generate sequence for DATA_STREAMER_ID and KEY_4 and in CASE statement. INSERT INTO AESEISQLSERVER.dbo.DATA_STREAMER_1 (t1.DATA_STREAMER_ID,t1.OPERATION,t1.KEY_4,t1.KEY_5,t1.IDENTIFIER_15) SELECT <need the sequence num here> , CASE t1.OPERATION WHEN 'I' THEN 1 WHEN 'L' THEN 1 WHEN 'U' THEN 0 END ,<need the sequence num here> ,CASE t1.OPERATION WHEN 'I' THEN <need the sequence num here> WHEN 'L' THEN <need the sequence num here> END FROM OPENROWSET(BULK 'E:\DS_SQL_SERVER\Presciber_1_I.idl', FORMATFILE='E:\DS_SQL_SERVER\DI_PRESCRIBER.Fmt') as t1; GO Please suggest me Show quote "ML" wrote: > Maybe you should explain in more detail the requirements here. Yes, I agree > that an extended procedure may not be of help here, but then again I really > see no need for it at all. > > You're talking about two (or more?) sets of rows that need to be imported > from a flat file into a table. In the destination table you want to create a > surrogate key for the rows, that's globally unique across the database. Is > that correct? > > If it is, create two (or more) tables, each with its own definition for an > identity column (such as Omnibuzz suggests) in order to create two (or more) > sets of data with globally unique identity values. > > E.g.: > > create table Staging1 > ( > StagingId int identity (1, 2) > ,<the rest of the columns> > ) > go > > cerate table Staging2 > ( > StagingId int identity (2, 2) > ,<the rest of the columns> > ) > go > > insert Staging1 > ( > <column list> > ) > select <column list> > from opensource(...) > where (<condition> = 'I') > go > > insert Staging2 > ( > <column list> > ) > select <column list> > from opensource(...) > where (<condition> <> 'I') > go > > insert <final destination> > ( > <column list> > ) > select <column list> > from Staging1 > union all > select <column list> > from Staging2 > go > > > ML > > --- > http://milambda.blogspot.com/ Doing an import in a single step as you suggest would most likely seriously
stress the server, and might take a very long time to execute. I suggest you take another good look at your data and try to identify individual sets of rows that require special transformations for the purpose of importing the data. Only a set-based approach will give you the performance you need. Think in sets, not procedurally. SQL is all about sets - e.g. in your case a set of rows where OPERATION equals 'I' and another set where OPERATION does not equal 'I'. These two sets should be imported separately (into staging or intermediate tables) since they require data modifications that can easily be done using identity columns. If you need several sequential columns in a single table, then you'd have to use more than two staging tables to reach the expected result(s), but nonetheless, the basic principle remains the same as me and Omnibuzz have mentioned in our previous posts. If you still can't get anywhere with these suggestions, then please post DDL, sample data and expected results, so that we can give you more precise answers. ML --- http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics