Solved

Need function to return next number in sequence within an INSERT query.

Posted on 2008-06-17
33
1,259 Views
Last Modified: 2010-05-18
I am importing data to an existing SQL Server table that has an int 4 field that must be unique, but cannot be changed to an auto-incrementing identity field.  It will be a negative integer, that could grow into the thousands.  As my INSERT query adds each row, I need to store the next (negative) sequential number in the int field.  It would be great if one of my query's columns could be some kind of function, something like NextNumber(), that would always return the next number in sequence.  I'm sure this would require storing the number in a one-record table, retrieving it, incrementing it, and saving it back to the table for the next use.

I followed the example given at the end of this Experts Exchange question:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22989578.html

This uses a stored procedure created as follows:
create proc dbo.getAndIncrease
as
declare @i int
update tbl set @i = col = col+1
select @i
GO

I can get this to work in SQL Query Analyzer, but I don't know how to use it in an INSERT query as described above.  I am an expert in Microsoft Access but a bit of a novice at some aspects of SQL Server, particularly when it comes to Stored Procedures and UDFs, so please give as much guidance about creation and usage as you can.  Thanks!
0
Comment
Question by:AutomateMyOffice
  • 11
  • 11
  • 7
  • +2
33 Comments
 
LVL 2

Expert Comment

by:floook
Comment Utility
You can try making it a calculated column to store the negative value from the identity column.

This code below shows you a use of a calculated column called GeneratedId. I made them negative. It seems to work the way you want.

You can always add a calculated column to your table by:

ALTER TABLE tableName ADD GeneratedId as (-ItemId)
if object_id('tempdb..#temp') is not null

	drop table #temp

if object_id('tempdb..#items') is not null

	drop table #items
 

create table #temp (

	ID int,

	ItemName varchar(20)

)
 

create table #items (

	ItemId int identity,

	ItemName varchar(20),

	GeneratedId as (-ItemId)

)

go
 

insert into #temp (ItemName) values ('Product1')

insert into #temp (ItemName) values ('Product2')

insert into #temp (ItemName) values ('Product3')
 

insert into #items (ItemName)

select ItemName

  from #temp
 

select * from #items

Open in new window

0
 
LVL 5

Expert Comment

by:fesnyng
Comment Utility
Why not use an IDENTITY column that counts negatively? (see below) Or, is there some other constraint that keeps you from using an IDENTITY -- such as you cannot modify the table?   If you cannot modify the table, you might try a 2 step import:  step 1 to a staging or temporary table with IDENTITY for that column, and then step 2 move the data to the final table.


DECLARE @counter TABLE(

ndx INT NOT NULL IDENTITY (-1, -1)

, col1 varchar(50)

)
 

INSERT INTO @counter ( col1) 

SELECT  'one'
 

INSERT INTO @counter ( col1) 

SELECT  'two'
 

INSERT INTO @counter ( col1) 

SELECT  'three'
 

SELECT * FROM @counter

Open in new window

0
 

Author Comment

by:AutomateMyOffice
Comment Utility
I probably could have made it clearer that I cannot alter the structure of the existing table in any way, and any footprint I leave in the database must be minimal -- so while I can't incorporate ever-increasing staging tables, I can still create a small single-record table to store a number that might be read, incremented and replaced by code.

I appreciate your suggestions, but I have to believe there is a more straightforward way to do this with a UDF or something similar, rather than by altering or adding tables.
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
I don't have a way to test the code at the moment but if it doesn't work as written, you can make it a part of a dynamic sql so you can seed the identity from a variable.

It's pretty much a two-step process: insert data into #temp and have #temp generate the numbers and then insert from #temp into the destination table.
declare @maxId int
 

set @maxId = (select max(generatedId) from destTable)
 

create table #temp (

...

GeneratedId identity(@maxId, -1)
 

)
 

insert into #temp (column list)

select column list from sourceTableName
 

insert into destTable (column list)

select column list from #temp

Open in new window

0
 

Expert Comment

by:neelsus
Comment Utility
Are you familiar with using subqueries to simulate autonumbers in MS Access?  If so, you can put the subquery code as the autonumber field with the first number being -1 + the lowest number in the SQL dataset.  If this makes any sense to you, I can elaborate on this tomorrow morning.
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
Correction:
declare @maxId int

 

set @maxId = isNull(select min(generatedId) from destTable), 0) -1

 

create table #temp (

...

GeneratedId identity(@maxId, -1)

 

)

 

insert into #temp (column list)

select column list from sourceTableName

 

insert into destTable (column list)

select column list from #temp

 

Open in new window

0
 
LVL 5

Expert Comment

by:fesnyng
Comment Utility
A user defined function cannot affect the database -- i.e. cannot insert, update or delete -- and, therefore, cannot update a sequence table to provide the counter required.  You can test this by building any simple function that contains an INSERT or UPDATE statement.  SQL Server will return an error on compile.

A staging or temporary table would not be "ever increasing" because  a staging table would be dropped  or cleared when you were done. So, I do not understand why you can create a permanent sequence table in the database, but not be allowed to create a staging table?




0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If the INSERT statement inserts *one* row at a time, you can use a function to get the next number.

But if the INSERT statement inserts multiple rows at a time, you cannot, because SQL will only evaluate the function once and so will try to use the same value for all rows.

So, are you INSERTing one row at a time (one row per INSERT statement) or mulitple rows?
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
In response to ScottPletcher... I am inserting multiple rows at once.  

My searching has uncovered the fact that a user-defined function is unlikely to work for me.  I have learned that SQL Server does not allow me to update my current sequence number (that I am storing in a separate table) from within a function, so I can't use that approach.

I also tried using a subquery (as suggested by neelsus) -- putting a select statement in my insert query -- something like this:
(SELECT Min(MyField) - 1 FROM MyTable) AS NextNum
...but I found that this does not change from row to row.  All I get as a Min value is the Min value that existed before the inserts, not during, so the same number ends up being entered in each row, not a sequence.

Apparently Microsoft has come up with something close to what I need (two functions, named NewID and NewSequentialID).  Unfortunately, these are GUID-based and I need an int.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If you can add a column to the table to identify new rows for a batch, you could use an INSERT trigger to assign the sequential numbers.  It wouldn't be especially efficient, but since the trigger would always fire, you could be sure of assigning the numbers.
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
In response to floook... I have gone back and reconsidered your approach, which I think I was not grasping at first.  Although I would much prefer to have a function or embedded SQL subquery in my INSERT query, having a two-step, temp-table-based approach may be the only way to accomplish what I need.  I definitely need to have my sequential value available when I insert into the destination table, as it is part of an index, and it looks like your approach takes care of this.

floook... remember that I am a novice with some of this.  Is your code a Stored Procedure?  Do you know if it can be run as part of a DTS package?

fesnyng... Is your approach essentially the same as floook's?  They look different to me (given my unfamiliarity), but maybe they are the same?  Want to be fair here...
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
ScottPletcher... Thanks, but I cannot alter the structure of the existing table in any way.  I have to work within the confines of the existing structure, which means coming up with a unique int(4) value to store in an existing indexed non-identity int(4) column.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> to store in an existing indexed non-identity int(4) column <<

Sorry, I mis-stated the requirement somewhat.  You could actually use any existing column that is unique within the table, or even the existing non-identity int column to hold a "batch" value temporarily.  You would need to insure that each "batch value" was unique, though.
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
You can make it into a stored procedure if you want.
Yes, it can be run in a DTS package.
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
fesnyng... I have read again your post about functions not being able to affect the database (specifically, that I could not update a sequence table).  Sorry I am a bit dense on this.  Looks like you were telling me something that I didn't "get" until I did a lot more googling.  I first read it to mean you thought I was using the function to do my INSERTs in my destination table, which I was actually doing in a separate query, from which I wanted to call the function.  Now I see that you were pointing out that I would not be able to update my sequence number stored in its own table.  Maybe it just seemed so unbelievable, given what can be done so easily with user functions in Access, that I initially disregarded it!  According to everything else I have read, you are certainly correct!
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
ScottPletcher...  Not sure I understand your approach.  If it depends on adding a trigger, I don't think I will be able to do that, since the sequential negative numbers are to be used ONLY when MY query is updating the table.  The table is updated programmatically the rest of the time, and the program has its own methods for generating a positive unique sequence number.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Expert Comment

by:floook
Comment Utility
I'm including SQL that I was actually able to test and get the desired results. I wrapped it in a stored procedure, too. I wouldn't make it a stored procedure unless you plan on running it regularly.


create procedure InsertIntoProducts

as

begin
 

-- Create sample destination table

if object_id('tempdb..#products') is not null

	drop table #products
 

create table #products (

	ItemId int identity,

	ItemName varchar(20),

	GeneratedId int

)
 

insert into #products (ItemName, GeneratedId) values ('Product1', -1)

insert into #products (ItemName, GeneratedId) values ('Product2', -2)

insert into #products (ItemName, GeneratedId) values ('Product3', -3)

 
 

-- Create sample source table

if object_id('tempdb..#source') is not null

	drop table #source

 

create table #source (

	ItemId int identity,

	ItemName varchar(20)

)
 

 

insert into #source (ItemName) values ('Product4')

insert into #source (ItemName) values ('Product5')

insert into #source (ItemName) values ('Product6')
 

if object_id('tempdb..#temp') is not null

	drop table #temp
 

declare @maxId int

 

set @maxId = isNull((select min(generatedId) from #products), 0) - 1
 

declare @sql nvarchar(4000)
 

set @sql = N'
 

create table #temp (

    ItemName varchar(20),

    GeneratedId int identity(' + convert(nvarchar, @maxId) + ', -1)

)

 

insert into #temp (ItemName)

select ItemName from #source

 

insert into #products (ItemName, GeneratedId)

select ItemName, GeneratedId from #temp

'
 

exec sp_executesql @sql
 

select * from #products
 

end

go
 

exec InsertIntoProducts

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Intereting.  But does that code insure that "GeneratedId" is unique among:+ (1) all other ids already in the table and (2) all other ids being generated by other simultaneous runs of the same proc?
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
It takes care of ID's in that exist in the table, it does not take care of simultaneous runs. But I wonder if it matters in this particular case since AutomateMyOffice asked if he could use it in a DTS package.

If you wanted to take care of simultaneous runs, #temp would have to become a permanent table to store all the generatedID's. After running the insert, a delete query could delete all but the latest record as shown below. He will have to evaluate if that's what he wants to do.


-- #temp becomes GeneratedIDs

-- Run this once to create the table to hold temporary insert data

-- and the min GeneratedId.

-- Add all columns your insert will have.

-- BatchUId will ensure you only insert records for the current insert.

declare @sql nvarchar(4000),

        @maxId int

 

set @maxId = isNull((select min(generatedId) from DestinationTableName), 0) - 1
 
 

set @sql = N'
 

create table GeneratedIDs (

	BatchUID uniqueidentifier,

    ItemName varchar(20),

    GeneratedId int identity(' + convert(nvarchar, @maxId) + ', -1)

)

'
 

exec sp_executesql @sql

go
 

-- I needed some sample destination records:

if object_id('tempdb..#products') is not null

	drop table #products
 

create table #products (

	ItemId int identity,

	ItemName varchar(20),

	GeneratedId int

)
 

insert into #products (ItemName, GeneratedId) values ('Product1', -1)

insert into #products (ItemName, GeneratedId) values ('Product2', -2)

insert into #products (ItemName, GeneratedId) values ('Product3', -3)

 
 
 

-- I had to create some sample source records:

if object_id('tempdb..#source') is not null

	drop table #source

 

create table #source (

	ItemId int identity,

	ItemName varchar(20)

)
 

 

insert into #source (ItemName) values ('Product4')

insert into #source (ItemName) values ('Product5')

insert into #source (ItemName) values ('Product6')
 
 

-- Here's the modified procedure.

-- Doesn't run dynamic sql (which makes it a bit faster).

-- The last two command of the procedure can be removed.

-- So the procedure actually becomes quite simple.

create procedure InsertIntoProducts

as

begin
 

declare @batchUID varchar(50)
 

set @batchUID = newid()
 

insert into GeneratedIDs (BatchUID, ItemName)

select @batchUID, ItemName from #source

 

-- Insert into the destination table.

insert into #products (ItemName, GeneratedId)

select ItemName, GeneratedId from GeneratedIDs

where BatchUID = @batchUID
 

delete from GeneratedIDs

 where GeneratedId <> (select min(GeneratedId) from GeneratedIDs)
 

-- This line can be removed in production version

select * from GeneratedIDs
 

-- This line can be removed in production version

select * from #products
 

end

go
 

exec InsertIntoProducts

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> It takes care of ID's in that exist in the table <<

Sorry, I missed that part.  How was the original code doing that?  Making sure that it did not insert an id that already existed in the original table?
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
Whatever approach I take, the use of a negative number ensures that there will be no conflict with other records.  The other process(es) that insert records all supply a positive number.  I am the only one using negative values.  I just need to make sure I am not conflicting with any of my own numbers any time I insert.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
So on Friday, there are no neg numbers left in the table that were inserted on Thursday?
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
The table is ever-increasing in size.  If my most recent additions had ID fields of, say, -2879, -2880 and -2881, I just need to make sure the next new record contains an ID less than -2881.  I would expect it to be -2882 (minimum value minus one), but it is just for ID purposes so it could be -9327.  It just can't be, say, -309, since that is in the range that has already been used.
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
>> It takes care of ID's in that exist in the table <<

By selecting min(GeneratedId) - 1 from that table. The inserted records have GeneratedID always less than that so they cannot conflict with what is already in the table.

AutomatedMyOffice, the best solution would be to make the column a calculated column in the destination table. Everything would be done for you, data integrity would be ensured. If you cannot do that, you can use the last query I gave you above, as long as it will be the only query that will insert into the destination table. You also have thing about that - are there any other inserts inserting directly into that table that should generate the number for you? If so, you may still want to consider making it a computed column. Otherwise you'll have to modify all inserts to call your new insert sql.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
OK, I guess I'm with you, although I still think you have concurrency issues with that approach.

I see you did "steal" batch id to deal with part of that :-) .
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
Well, it had to be either a calculated column or an autoincrement. But yes, you do deserve credit for that since you were the first one to mention it :o)
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
I'm sorry, I didn't really fully get what you were saying.

Yes, I used a batch ID to handle that.
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
floook...  I think I grasp the purpose and flow of your code, but, umm, here's where the novice part kicks in...  What do I do with it?  I can see how I would customize it for my own table and field names and such, but I don't actually know where to copy and paste the code and run it -- or even if your statements with the leading hyphens are, in fact, "commented out" because of the hyphens, or if I would need to remove the comment lines completely.  Sorry.  I don't expect a full SQL Server tutorial, but just a little guidance on what to do next.  Thanks!
0
 
LVL 5

Expert Comment

by:fesnyng
Comment Utility
You indicated that you can create a table as long as you do not 'leave behind' any significant evidence of your import.  The following creates a staging table, imports the data, copies the imported data to the destination and then drops the staging table (leaving nothing behind).

Note:  --  indicates the start of a comment in SQL Server and will by default show green in SSMS or Query Analyzer
--STEP 1: determine your 'next' number  

	

	-2882 -- from an earlier post    */
 
 

--STEP 2:  create a staging table (to be deleted at the end of this process)
 

	-- first row in table will take the value of -2882

	CREATE TABLE import_stage (

	ndx INT NOT NULL IDENTITY (-2882, -1) -- count negatively

	, col_1 varchar(50)NULL -- your data starts here

	, col_2 varchar(50)NULL

	)
 

--  STEP 3:   ... import data (you have not indicated or I have missed your method of importing) 
 

	--TEST for the example:

		INSERT INTO import_stage (col_1, col_2)

		SELECT 'one', 'two'
 

-- STEP 4:	copy data from staging table to destination 
 

	INSERT INTO destination_table (ndx, col_1, col_2)

	SELECT * from import_stage
 

-- STEP 5:  DROP TABLE import_stage -- remove traces of the import 
 

	DROP TABLE import_stage

Open in new window

0
 
LVL 2

Expert Comment

by:floook
Comment Utility
Oh, I see. Here's steps you'll have to take. Let me know if any of them is not clear or you're having any troubles with it:

- You just need to open either Enterprise Manager (SQL Server 2000) or Management Studio (2005)
- Open a new query window.
- Connect to your server.
- Select a database - either from a dropdown or by running USE databasename
- Paste in the qyery and you're ready to start playing with it.

Two leading hyphens(--) are a comment in SQL Server.
Pound sign (#) - indicates it's a temporary table. These tables will get dropped once you disconnect.

You probably already have your source and destination tables. You will have to run the creation of the table for generated ID's once. If you want the inserts in a stored procedure, run the procedure creation code once. Otherwise just take out whatever is in there and run it whenever you need it.

EXEC exec InsertIntoProducts - executes the procedure.

Hope this clarifies some of it. Comments in the code I posted above describe what the parts of code are for. Let me know if you need any more help.
0
 

Author Comment

by:AutomateMyOffice
Comment Utility
After much fruitless searching, I was about ready to give up on my quest for a "function to return next number in sequence within an INSERT query," as stated in my title line.  Every place I looked gave the same answer:  Functions can be called from queries but can't perform a simple increment to a sequence value stored in a table.  The staging-table solutions suggested here seemed like the only path to take, but I was not happy that my goal could not be accomplished in a user-defined function.
I finally came upon a "teaser" article on subscription site sqlmag.com that seemed to offer exactly what I needed.  The author promised "As an example of a function that modifies data, I'll use a much-requested implementation of a custom sequence function."  Intrigued, I paid the five bucks for one month's access and was able to read the whole article.  Indeed, it gave me just what I was looking for as requested in my title.  Very clean.  Very simple.  The teaser article is here:  http://www.sqlmag.com/Articles/ArticleID/41845/pg/3/3.html
I'm not sure exactly why or how it works, but it works perfectly.
Thanks for all your time and suggestions.  I only recently joined Experts Exchange so I am a little unclear on the etiquette.  Can  I award you points based on your effort, even though the actual solution came from a different source?  Thanks for your help!
0
 
LVL 2

Expert Comment

by:floook
Comment Utility
I'm happy it works for you. I looked at the scripts. It's a different implementation of what we were discussing in this thread. Except, they INSERT into the GeneratedIDs table for every row that is being inserted and get the newly generated ID right there. I really like their solution.

It may perform a little slower than inserting all records into the GeneratedIDs table first and let the SQL Server generate the IDs using an identity column. This is due to the fact that their function inserts and then rolls back on a record-by-record basis. Also, function calls slow things down a bit.

In either case, you will wind up with a new table to store information about the latest identity value. Their example doesn't actually store the value in a record. It relies on the fact that when a transaction is rolled back, the indentity value is still incremented. I personally like it. You can always retrieve the current identity value on a table by running  SELECT IDENT_CURRENT(tableName).

Will be happy to accept the points since the solutions are very similar. I suggest you also attribute some points to ScottPletcher, since he also contributed with very good ideas.
0
 

Accepted Solution

by:
AutomateMyOffice earned 0 total points
Comment Utility
I appreciate the time and effort that went into everyone's responses.  I wish that points could be awarded for effort and educational value.  I have learned a lot!  I must, however, select my own answer (based on the SQL Server Magazine online article, discussed in my 6/23/2008 post, above) as the solution, in fairness to future solution seekers looking for a simple and reusable "NextNumber()" function solution.

While your methods would work, none of them in fact provided a way to create a simple function that could be called from an INSERT query to provide a unique number to use as each row is added to the destination table -- as requested in my initial post.  While staging tables would give me the end result for my current project, I would need to recreate the code to include the specific set of columns for each table in the future.  With the function approach, I simply create a copy of the function (and the little table to store the sequence number) wherever I need it.

I don't feel right about posting a complete solution, because sqlmag.com is the actual source, and the full example I used is copyrighted and requires a subscription.  I can, however, point you to the link to the teaser article (see 6/23 post) that by itself gives enough info to get started, by refering to "a back door that lets [programmers] modify permanent data from within a UDF by using the OPENQUERY() rowset function...to modify data on [a] linked server."  It is the use of the OPENQUERY() function within the UDF, coupled with setting the local server to work as a linked server, that provides the special capability I needed, namely to UPDATE table data from within a function.  It simply cannot be done without the "back door" described at sqlmag.com.

(I must point out an error in the teaser article's code, though, that kept this from working at first.  You will notice that they provide a snippet to show how to make the local server "linked."  They have "EXEC sp_server "on ther first line, continuing with the word "option" at the beginning of the second line of code.  There should be no line break.  Simply remove the line break so that the command refers to sp_serveroption.)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now