Counter
-
Hi all,
I am new to the SQL Server (2000) world after working with Oracle for a while. My problem is that I'm missing the Sequence object dearly.
I need to run a SQL statement from within a SP that populates a table where one column is a sequence number named SEQ_NUM. I'm not able to change the layout of the table as it is defined by the package we're implementing (Siebel) so an Ident column is out of the question.
My idea was to have a separate table containing just one column and one row with a numeric value. Then to have a UDF which I would call from the SQL statement populating the Siebel table, which would give me the desired sequence number. The data type of the SEQ_NUM column is Number so that's what I'm limited to.
I can't use a Procedure as this can't be called from a SQL statement, (I'm running a INSERT INTO TABLE SELECT... statement) so it has to be a UDF.
Am I going about this problem all wrong? Anybody out there with a clever idea? I've looked through a number of sites for clues as to how this can be done, but nothing has worked so far.
All help is greatly appreciated.
Thanks in advance.
David
-
I'm a little confused as to exactly what you want to do. Could you provide a small illustrating example, and also explain any rules regarding this sequence number?
/Kenneth
-
>My idea was to have a separate table containing just one column and one row with a numeric value<
No bad idea, but what is your sequence based on?
_/_/_/ paramind _/_/_/ -
Thanks for the replies.
Here's a shot at trying to explain in furhter detail. If we disregard the rest of the stuff I'm trying to do here's what I want from the UDF.
1. It should return a numeric value when called from the SELECT part of a INSERT INTO... SELECT statement. Something like:
INSERT INTO t_table_1 (A, B) (SELECT fn_seq_num(), col_1 FROM t_table_2)
would create a number of rows in table t_table_1 with the following result
A B
1 XYZ
2 ABC
(assuming that table t_table_2 contains two rows with values XYZ and ABC)
2. The numeric values the fn_seq_num function returns is kept in a table named (for instance) t_sequence which is defined like this:
CREATE TABLE [dbo].[t_sequence] (
[A] [numeric](18, 0) NULL
) ON [PRIMARY]
3. The t_sequence table will always contain only 1 value which is increment with 1 each time the fn_seq_num is called. The t_sequence table will have to be initiated with the value 0 to have a starting point.
My futile tries have resulted in a function that looks like this
ALTER FUNCTION fn_dh_test()
RETURNS @table_variable_name TABLE
(A numeric)
AS
BEGIN
Declare
@table_var as numeric
SELECT @table_var = A + 1 FROM Stage..t_seqence
UPDATE @table_variable_name SET A = @table_var
RETURN
END
GO
It compiles, but I am not able to call the function (I get a message saying Invalid object name 'dbo.fn_dh_test'.) or see that it actually does anything. Please note that the code is based on an example I found elsewhere on the net so I'm not sure I've understood the full functionality of the table function concept. For one, how do I get the function to understand that it's actually the value from table Stage..t_sequence that should be returned?
Thanks again.
/David
-
> It should return a numeric value when called from the SELECT part of a INSERT INTO... SELECT statement<
Should this value be unique? should it represent the order of inserts?
_/_/_/ paramind _/_/_/ -
I think the real question, disregarding description of various attempts how to solve it, is this:
There is a table with column that would normally be identity column. However, design does not allow to set it as one, so it is plain INTEGER. What must one do to make sure that on every insert, this column will be populated with unique number that is greater by 1 than the highest existing number in that column? In other words, how can one have a reliable identity column if it is forbidden to set the IDENTITY property...
Is that so, David?
-
Vladan,
You are correct in everything except that I need to get a numeric back. Not an integer.
> paramind,
Yes, the value should be unique.
Thanks again.
David
-
Well, then I don't think you need a special table to hold the last value. You can get the highest existing value from the table itself by SELECT MAX(SEQ_NUM) FROM table_name, add 1 and cast it to any datatype you require. You didn't mention anything about possible concurrency issues or whether you will be inserting individual rows (one after another) or entire sets of rows at once, but both these things should be manageable.
You would need an auxiliary table in case all rows are periodically deleted from the table before other are inserted, and still you need to continue in your numbering where you stopped before the delete. Of course this is something that shouldn't be done, but sometimes you are stuck with what the application provider gives you.
-
>Well, then I don't think you need a special table to hold the last value. You can get the highest existing value from the table itself by SELECT MAX(SEQ_NUM) FROM table_name,<
As far as I can see, the problem is not to hold the last value, but to generate unique values IN A SET OPERATION. If you "SELECT MAX(SEQ_NUM) FROM table_name" every insert will have the same number - which is definitely not unique
Your Max() is only good for the starting point - whether out of the table itself or in another table.That's a limitation in SQL-2000 and that's why they invented ROW_ID() in SQL-2005. If you use SQL-2005 by any chance, that's your solution.
Otherwise, you'll need a construct to provide you with some numbers. This can of course by a #temptable, a resident table or a table variable. But instead of looping through the inserts one by one, this could be done at once, provided your rows have SOME key. Do they?
Example:
to test in Query Analyzer:
DECLARE @seed INT
SET @seed = 123
DECLARE @tempID TABLE ([internalKey] INT IDENTITY (1, 1) NOT NULL ,
[externalKey] VARCHAR(255) NOT NULL)
INSERT INTO @tempID ([externalKey]) VALUES ('XXX')
INSERT INTO @tempID ([externalKey]) VALUES ('BBB')
INSERT INTO @tempID ([externalKey]) VALUES ('CCC')
SELECT
internalKey + @seed,
externalKey
FROM @tempID
to work with:
CREATE storedProcInsert
AS
DECLARE @seed INT
SET @seed = COALESCE(SELECT MAX([SEQFIELDNAME] FROM [targetTable]),0)
DECLARE @tempID TABLE ([internalKey] INT IDENTITY (1, 1) NOT NULL ,
[externalKey] VARCHAR(255) NOT NULL)
INSERT INTO @tempID ([externalKey])
SELECT yourKeyFieldintheInsert FROM WHATEVER
-- NOW, join this
INSERT INTO targetTable
([SEQFIELDNAME],
Field1,
Field2,
....
SELECT
@tempID.internalKey + @seed,
Field1,
Field2
FROM .... WHATEVER
INNER JOIN @tempID
ON WHATEVER.externalKey = @tempID.externalKey
_/_/_/ paramind _/_/_/ -
I have to add another possibly working solution (not tested that, just an idea):
INSERT INTO targetTable
([SEQFIELDNAME],
Field1,
Field2,
....
SELECT
CHECKSUM (NEW_ID()),
Field1,
Field2
FROM .... WHATEVER
: New_ID() is a GUID created for each record. As you want no string, you can use the Checksum function to create an integer (which is of course a numeric too ;-)). Checksum is usually used to create hash-indexes, but as far as I can see, the checksum on a GUID should be unique by design.
... but this will work ONLY, if you don't care of the real numbers and uniqueness is all you seek. (virtually any negative or positive numbers)
_/_/_/ paramind _/_/_/ -
Vladan, Paramind,
Thanks so much for your inputs and ideas! I went ahead and used the idea that Vladan introduced and selected the MAX(SEQ_NUM) from the target table. In addition I added a Ident column to the stage table (which I am, in fact, able to change the layout of). So my UDF will get the MAX(SEQ_NUM) and add the Ident value to the returned MAX(SEQ_NUM) result.
I realize that this may not be the perfect solution, but it's good enough. So, a great big thanks to both of you!
Best regards,
David
-
What's a difference between numeric(p, 0) and integer?
I don't see any except numeric takes more space to store the same values.
If integer is not big enough for you use bigint.
If the problem is in returning datatype use cast(ID as numeric(18, 0))
_____________
Code for TallyGenerator -
I'm glad it worked... good that you could add identity column to the staging table - just make sure that it always starts at 1, if you are going to add this number to the selected MAX... If you drop and recreate the staging table every time, or use TRUNCATE TABLE to empty it, it's OK; if you have to use DELETE, you'll also need to reseed identity. And if the rows remain in the staging table after import, you'll have to modify the formula (e.g. subtract the MIN identity of rows being processed and add 1)... maybe this wouldn't be a bad precaution anyway, since it will work always. Of course, all this based on assumption that there are no gaps in the numbering. If there are some, they will be transferred to the target table.
paramind, yes I'm aware that it was only a starting point... but I wasn't sure in which direction to continue. What I thought originally was that intermediate temporary table to number the rows is necessary, but I didn't mention it since I wasn't sure whether this is acceptable (and also hoped that someone will come up with better solution... which happened, as it seems :-)).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply