March 3, 2003 at 3:48 pm
I am using insert into by selecting from another table. I need to run this operation multiple times and provide a column containing a sequential number. I cannot use an identity column. So my first batch might be 1 - 100, the next batch 101 - 200, and so on. This might seem pretty basic but I am new to all of this.
March 3, 2003 at 5:11 pm
Why can you not use an identity?
Also, does the number need to be generated in the select?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 3, 2003 at 6:04 pm
We cannot use identity because the table we are using is also processed by some program content that we have and the program content assigns the sequence number programmatically. I am not sure what you mean by does the number need to be generated in a select. Hmmm, I guess it does not have to be and could be assigned after the records were inserted if that is what you mean.
March 7, 2003 at 9:18 pm
hi grasch
I think Steve asked if the value of the new identity column is generated in the select part of the insert into construct like this
insert into NewTable NewSeq, ColA
select OldSeq, A1 from OldTable
where OldSeq is the column from which the sequence number to be inserted in the NewSwq column is based.
I am not sure what's the order in which SQL Server retrieves the records from the source table in an insert into...select...from construct. If we cannot determine the order, how are we suppose to know which record is inserted when? If I were to implement this, I would use static cursor. Thru this, you can track which record is being inserted to the new table and know the correct sequence number.
Edited by - bani on 03/07/2003 9:20:05 PM
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
March 8, 2003 at 7:06 pm
Grasch can you please walk us thru the logic and what the data layout will look like so we can get a better idea of your vision.
March 10, 2003 at 7:08 am
My vision is quite clouded these days. For good or otherwise I am loading records into a work table we use for printing reports and doing other processing. Our performance has been horrible doing row at a time processing so we are starting to construct sql statements in a stored procedure based environment to load this table. Now in a programmatic sense the handling of all of this is easy.
The high order key in the table is a batch number. The second key is this sequence number column which we assign today programmatically. 1st record is one, second record is two and so on. The key combination is used for some of the subsequent updates we perform (based on some of the other attributes contained in the table).
So I am working on constructing my stored procedures, I want to be able to "dump" the contents for a batch into this work table (multiple users share the same table each users work is differentiated by the batch #). As I dump in my work I was hoping to somehow be able to construct in the sql statement a sequence number like process.
As stated in my original posting I cannot change the column to an identity column but perhaps I have missed some capability of the identity function itself. I figured you had to have an identity column to use the identity function.
Right now our only solution is to create our initial loading to a table in #tempdb and the copy the contents of the table in #tempdb to the work table we use.
March 10, 2003 at 8:33 am
grasch, here is one solution (direct from memory so no guarantees). However, it assumes that you can do this in a two-step proocess and that the batchid is unique for every batch. it's just an example, but it should show you one way to achieve your goals:
drop table batch
drop table store
go
create table batch (batchid int, data int)
go
create table store (batchid int, seq int, data int)
go
insert into batch values (1, 1)
insert into batch values (1, 2)
insert into batch values (1, 3)
insert into batch values (1, 4)
insert into batch values (1, 5)
declare @seq int
set @seq = 0
insert into store
select batchid, @seq, data
from batch
select * from store
update store set @seq = seq = @seq + 1 where batchid = 1
select * from store
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 03/10/2003 08:44:31 AM
March 13, 2003 at 8:53 am
I needed a unique identifier for a view as I could not store the value in a table.
This also works as the default value for a field too
This is certainly not sequential, but does provide uniqueness on-the-fly
Here is the version for SQL Server 7 (or 2000):
SELECT CONVERT(money,CONVERT(varbinary,NEWId())) as [UniqueNumber]
Here is the version for SQL Server 2000:
SELECT CONVERT(bigint,CONVERT(varbinary,NEWId())) as [UniqueNumber]
March 17, 2003 at 2:29 pm
grasch, As Steve was alluding to, I think what you really need to do is something like the following.
CREATE TABLE #t
(
SeqNum int identity(1,1),
ColA int,
Batch int
)
INSERT INTO #t(ColA, Batch)
SELECT ColA FROM Foo
WHERE batch = 1
INSERT INTO #t(ColA, Batch)
SELECT ColA FROM Foo
WHERE batch = 2
INSERT INTO ReportTable(Seq, ColA, Batch)
SELECT Seq, ColA, Batch
FROM #t
DROP TABLE #t
By using the identity on the temp table #t you have the ability to get your sequential number for each batch. You might need to drop and create the temp table before each batch depending on your sequencing needs.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 17, 2003 at 5:07 pm
Temp tables with identity columns as suggested seem to be the easiest way for us to get going with this. We are going to give it a try and see what happens.
March 18, 2003 at 2:22 am
Just a small suggestion, I hope I didn't totally misunderstand the question.
Why can't you create an IDENTITY? It is only for generating an increasing number.
* Start a TRANSACTION,
* Enable IDENTITY on your 'sequence number column'.
* Do your inserts(including the number of your batch).
* Turn of IDENTITY.
* Commit the TRANSACTION.
In this way you do not have the additional overhead of inserting into a #table and from there do extra inserts just to end up where you could arrive directly.
Happy for comments on this!
(If you don't want to turn off the IDENTITY, try to use SET IDENTITY_INSERT ON|OFF if you want to insert a value disregarding the IDENTITY. [Inserts with IDENTITY ON will always start from the MAX(...) + 1])
Regards, H.Lindgren
March 21, 2003 at 10:13 am
Does the sequence number actually need to be a "sequence" number or just a unique identifier? If it doesn't need to perform as a sequence number I would do something like this:
CREATE TABLE t_table (
table_id numeric identity,
seq_num int null,
batch_id int not null,
constraint xpk_table primary key (table_id))
go
CREATE TRIGGER tI_t_table ON t_table FOR INSERT AS
BEGIN
-- assign missing sequence numbers
UPDATE t_table SET seq_num = table_id * -1 WHERE seq_num IS NULL
END
go
-- inserts from program specifying a sequence number
INSERT INTO t_table (seq_num, batch_id) VALUES (1, 1)
INSERT INTO t_table (seq_num, batch_id) VALUES (2, 1)
INSERT INTO t_table (seq_num, batch_id) VALUES (3, 1)
-- inserts from a source not specifying a sequence number
INSERT INTO t_table (batch_id) VALUES (1)
INSERT INTO t_table (batch_id) VALUES (1)
go
SELECT * FROM t_table
go
/*
table_id seq_num batch_id
1 1 1
2 2 1
3 3 1
4 -4 1
5 -5 1
*/
This allows you to process both types of inserts. Those where the sequence number is defined and those that aren't.
Instead of throwing the sequence negative in the trigger you could just throw it up high enough to stay above the standard sequencing unless, of course, the sequencing is based upon the program doing a max(seq_num).
March 27, 2003 at 12:29 pm
Let me see if I can respond to both of the suggestions. Regarding turning identity on and off, is that session specific or table specific? If session specific then that might work, if table specific then it will not as I have multiple users in this work table at the same time with a mix of programmatic content and sql content.
Second, regarding trigger idea, I have many sequences numbers to assign within a batch. I assume this is an example of what could be done and that the actual trigger would have to go get some kind of max or min value and use that in the trigger logic. If that is true then what do you think the performance might be like. It is possible we could up to 250,000 records into a batch for some of the requests we run.
March 28, 2003 at 8:27 am
Just a quick reply, if you have a transaction with a TAB lock, there is basically no different between session specific and table specific. But you are right, the 'turning' on and off the identity (NOT speaking of always having Identity on and 'turning it' off with 'SET IDENTITY INSERT (...) OFF) requires an ALTER TABLE (which is table specific and not session...).
Regards, Hanslindgren!
Edited by - hanslindgren on 03/31/2003 02:55:02 AM
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply