INSERT set of records while incrementing a field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

INSERT set of records while incrementing a field

Author

Topic

joe iacoponi
Starting Member

11 Posts

Posted - 2005-02-01 : 21:18:49

OK, I'm stumped. I've looked for a solution but just haven't found one yet.

I am attempting to perform an INSERT statement using SELECT that will insert a number of new records into a table. I would like one of the fields (item #) to increment for each record that is added (within that order ID). I only need to insert the records for a single OrderID at a given time.

Once inserted, the data would look like this.

OrderID Item# Descr
1 1 Apple
1 2 Orange
2 1 Pear
2 2 Apple
2 3 Grape

I was considering using Identity on the Item # field, but then the identity would just keep incrementing, and not be relative to the OrderID.

Anybody have any thoughts?

All help appreciated.

Joe

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-01 : 21:52:49

There was a similar topic recently:
TOPIC_ID=45410

Be One with the Optimizer
TG

joe iacoponi
Starting Member

11 Posts

Posted - 2005-02-02 : 13:30:56

I did see that and was considering adding to the thread, but thought a new thread might be better. I'd like to do something like what Spirit1 showed on the update, but have it happen on the initial insert instead as I am inserting a set of records. The problem I have is that the incrementing field is part of a unique key, so I can't wait to do an Update.

Here was his solution for an update, which is very slick:

declare @table table (id int, rank int)
insert into @table
select 1, 1 union all
select 1, 3 union all
select 1, 6 union all
select 2, 2 union all
select 2, 4 union all
select 2, 5 union all
select 2, 8 union all
select 3, 2 union all
select 3, 11 union all
select 3, 12 union all
select 3, 14 union all
select 3, 15

select * from @table

declare @cnt int
set @cnt = 0
UPDATE t1
SET @cnt = rank = case when
exists (select top 1 * from @table where id < t1.id)
and not exists (select top 1 * from @table where id=t1.id and rank < t1.rank)
then 1
else @cnt + 1
end
from @table t1

select * from @table

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 13:54:43

why do You need an ordering column for in the first place ?

This will only work for inerting 1 row at time.

create table #t(OrderId int,Item int ,Descr varchar(20))

insert #t(OrderID,Item,Descr)
select 1, 1 + coalesce((select max(Item) from #t where OrderId = 1),0), 'Apple'
insert #t(OrderID,Item,Descr)
select 1, 1 + coalesce((select max(Item) from #t where OrderId = 1),0), 'Orange'
insert #t(OrderID,Item,Descr)
select 1, 1 + coalesce((select max(Item) from #t where OrderId = 1),0), 'Pear'
insert #t(OrderID,Item,Descr)
select 2, 1 + coalesce((select max(Item) from #t where OrderId = 2),0), 'Apple'
insert #t(OrderID,Item,Descr)
select 2, 1 + coalesce((select max(Item) from #t where OrderId = 2),0), 'Grape'

select * from #t

drop table #t

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-02 : 14:00:48

[code]

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myStage99(OrderId int, Descr varchar(255))
CREATE TABLE myTable99(OrderId int, ItemNum int, Descr varchar(255))
GO

INSERT INTO myStage99(OrderID, Descr)
SELECT 1 , 'Apple' UNION ALL
SELECT 1 , 'Orange' UNION ALL
SELECT 2 , 'Pear' UNION ALL
SELECT 2 , 'Apple' UNION ALL
SELECT 2 , 'Grape'
GO

DECLARE @MAX_OrderId int, @OrderId int
SELECT @MAX_OrderId = MAX(OrderId), @OrderId = MIN(OrderId) FROM myStage99

WHILE @OrderId < = @MAX_OrderId
BEGIN
CREATE TABLE #x (OrderID int, Descr varchar(255), ItemNum int IDENTITY(1,1))
INSERT INTO #x(OrderId, Descr)
SELECT OrderID, Descr
FROM myStage99 WHERE OrderId = @OrderId
SELECT @OrderId = MIN(OrderId) FROM myStage99 WHERE OrderId > @OrderId
INSERT INTO myTable99(OrderId, ItemNum, Descr)
SELECT OrderId, ItemNum, Descr
FROM #x
DROP TABLE #x
END

SELECT * FROM myTable99
GO

DROP TABLE myStage99
DROP TABLE myTable99
GO

[/code]

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-02 : 14:08:17

I don't see the value in this design but...will this do it for you?


declare @table table (id int, rank int)
insert into @table
select 1, 1 union all
select 1, 3 union all 
select 1, 6 union all
select 2, 2 union all
select 2, 4 union all
select 2, 5 union all
select 2, 8 union all
select 3, 2 union all
select 3, 11 union all
select 3, 12 union all 
select 3, 14 union all 
select 3, 15 

Select	[id]
	,rank
	,ItemNo = (Select count(*) 
		from @table 
		where rank <= t.rank 
		and [id] = t.[id])
From	@table t

--for 1 order at a time uncomment Where Clause
--Where [id] = 3

Be One with the Optimizer
TG

Edit: Here is the Apple/Orange version:


declare @table table (Orderid int, Descr varchar(15))

INSERT INTO @table(OrderID, Descr)
SELECT 1 , 'Apple'  UNION ALL
SELECT 1 , 'Orange' UNION ALL
SELECT 2 , 'Pear'   UNION ALL
SELECT 2 , 'Apple'  UNION ALL
SELECT 2 , 'Grape'


Select	OrderID
	,Descr
	,ItemNo = (Select count(*) 
		from @table 
		where Descr <= t.descr 
		and [orderid] = t.[orderid])
From	@table t
Order by 1,3

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 14:13:48

quote:

Originally posted by X002548



USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myStage99(OrderId int, Descr varchar(255))
CREATE TABLE myTable99(OrderId int, ItemNum int, Descr varchar(255))
GO

INSERT INTO myStage99(OrderID, Descr)
SELECT 1 , 'Apple'  UNION ALL
SELECT 1 , 'Orange' UNION ALL
SELECT 2 , 'Pear'   UNION ALL
SELECT 2 , 'Apple'  UNION ALL
SELECT 2 , 'Grape'
GO

DECLARE @MAX_OrderId int, @OrderId int
SELECT @MAX_OrderId = MAX(OrderId), @OrderId = MIN(OrderId) FROM myStage99

WHILE @OrderId < = @MAX_OrderId
  BEGIN
	CREATE TABLE #x (OrderID int, Descr varchar(255), ItemNum int IDENTITY(1,1))
	INSERT INTO #x(OrderId, Descr)
	SELECT OrderID, Descr
	FROM myStage99 WHERE OrderId = @OrderId
	SELECT @OrderId = MIN(OrderId) FROM myStage99 WHERE OrderId > @OrderId
	INSERT INTO myTable99(OrderId, ItemNum, Descr)
	SELECT OrderId, ItemNum, Descr
	FROM #x
	DROP TABLE #x
  END


SELECT * FROM myTable99 
GO

DROP TABLE myStage99
DROP TABLE myTable99
GO

Brett

8-)

Is that by chance a loop

I see in that code, Brett?

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 14:21:53

What are You guys doing ?!?!?

Joe's got a table, Joe's table has a column that should autoincrement.
What's with all these table variables, loops, and stuff ?

rockmoose

joe iacoponi
Starting Member

11 Posts

Posted - 2005-02-02 : 14:26:15

Thanks for theses replies. I need to spend some time and digest them now.

RockMoose, you are correct, it should be that simple, but the problem I run into is that the column is autoincrementing only within that OrderID , and must be done on the INSERT, since it is part of the Primary Key.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-02 : 14:27:31

Joe's got a bogus design where he wants his identity column to reseed to after each new value of OrderID.

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 14:35:01

Joe did not check my solution, which will in fact do what he wants.
But you have options...
Write a before trigger on the table.
Write a udf that will get the next nr in sequence for a given OrderId.

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 14:46:27

TG, You may be very correct on the bogus design, but that design is not so uncommon in many systems.
Maybe it could be done better.

This is what I think Joe wants to do:

create table [order](orderid int identity primary key)
create table orderline(orderid int references [order](orderid), line int, primary key(orderid,line))
GO

create function nextline(@orderid int) returns int as
begin
return(coalesce((select 1+max(line) from orderline where orderid = @orderid),1))
end
GO

insert [order] default values
insert [order] default values
insert orderline values(2,dbo.nextline(2))
insert orderline values(2,dbo.nextline(2))
insert orderline values(2,dbo.nextline(2))

select * from orderline

drop function dbo.nextline
drop table orderline
drop table [order]

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-02 : 14:47:11

quote:
Originally posted by rockmoose

What are You guys doing ?!?!?

Joe's got a table, Joe's table has a column that should autoincrement.
What's with all these table variables, loops, and stuff ?

rockmoose

Yeah, I guess he could manualy type out every statement

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-02 : 14:47:34

Joe,
I don't know if a design change is possible for you but if so you may want to consider something like a classic Invoice/Lineitem or PurchaseOrder/Lineitem model:


Order table
	orderid identity       PK
	order Level Data

Item table
	orderID Foreign Key 	PK
	ItemID identity		PK
	Item Level Data

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 14:48:53

TG gdmit

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-02 : 14:49:51

LOL !!

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 14:57:49

I personally thought Brett's

was the best idea.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 14:58:21

Because of the......LOOP

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-02 : 15:25:21

this may sound nuts but what if you disabled the constraint, do the insert, update the column with correct values and reenable the constraint? would that work for you?

Go with the flow & have fun! Else fight the flow

joe iacoponi
Starting Member

11 Posts

Posted - 2005-02-02 : 15:31:35

Wow, now I really need to look at all these. On the face, it appears there are at least a couple good answers, so thank you all.

TG, we actually have our layout with Order Table / Item Table like you mention, but as you can surmise we just happen to have the additional line number field which we now need to manage. I'm not sure that a design change is an option, now that we've gone down this path, but thanks for the thought.

Joe

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-02-02 : 15:32:01

Of course, there's the ROWNUMBER() function in SQL 2005 ;)

Cheers
-b

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources