Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert an incremented value in SQL?

Posted on 2011-03-16
4
Medium Priority
?
372 Views
Last Modified: 2012-08-13
Hi.  I have a stored procedure that returns a recordset of customers from two customer tables.  I gather the records into a single temp table, as shown in the attached code.

First, the final resultset must be ordered by customer name.  Next, I have a column called 'GroupID'.  'GroupID' needs to be set to an incremented integer --  however, it needs increment every 25 rows.  So, 'GroupID' for the first 25 rows returned needs to be set to 1, the next 25 rows need to be set to 2, and so on for the next 25000+ rows.

I hope I am being clear ... I have simplified my existing procedure to a bare minimum in the example attached.

Any suggestions about how to do this in MS SQL?
Thanks.

create table #Temp1
   (TEMP1_Id int IDENTITY (1, 1) NOT NULL,
    Cust_Name  varchar(50),
    GroupID    integer null)
		 
insert into #Temp1 (Cust_Name)
    Select Cust_Name from CustTable1

insert into #Temp1 (Cust_Name)
    Select Cust_Name from CustTable2
	
------------------------------------------
-- need some UPDATE #Temp1 code in here ...
-- probably need to 'order by Cust_Name' here 
------------------------------------------

-- return the final resultset ...
select * from #Temp1
order by Cust_Name

Open in new window

0
Comment
Question by:Rob Rudloff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 35149151
you cannot do it using IDENTITY. You can create a stored procedure for inserting records. In the stored procedure, count the number of records and set the value of groupid based upon the total number of rows/25.
0
 
LVL 4

Assisted Solution

by:LeDaouk
LeDaouk earned 100 total points
ID: 35149201
also before the last select you can add the attached code to update the group ID field
DECLARE DECLARE  @Min  numeric,@Max  numeric, @Count  numeric,@Seq  numeric, @TempSeq  numeric
	SET @Count = (Select count(TEMP1_Id) From #Temp1)
	SET @Min = (SELECT MIN TEMP1_Id FROM  #Temp1)
	SET @Max = (SELECT MIN TEMP1_Id FROM  #Temp1)
	SET @Seq = 1
	SET @TempSeq = 1
	WHILE @Min <= @Max
		BEGIN
			UPDATE #TEMP1 SET GroupID = @Seq WHERE Temp1_ID = @Min
			SET @Seq = 1
			SET @TempSeq = @TempSeq +1
			IF @TempSeq  = 26
			BEGIN
				SET @TempSeq = 1
				SET @Seq = @Seq +1 
			END
			Set @Min = @Min+1

		End

Open in new window

0
 
LVL 59

Accepted Solution

by:
HainKurt earned 900 total points
ID: 35149236
no need for identity, temptable etc etc... try this

with Cust as (
Select 1 ord, Cust_Name from CustTable1
union
Select 2 ord, Cust_Name from CustTable2
)
select rn, rn/25 as GroupID, cust_name
from (select *, ROW_NUMBER() over (Order by ord, cust_name) rn from cust) x
0
 

Author Comment

by:Rob Rudloff
ID: 35150147
Thanks -- I started with LaDaouk's suggestions, and ended up using HainKurt's idea.

With HainKurts code, I started with:  
select *, ((ROW_NUMBER() over (Order by cust_name))/25) as GroupID    from #temp1

Open in new window

This presented rows 1-24 in group 0, and 25-49 in group 1.

After that I adjusted it a bit, so that rows 1-25 were in group 1, rows 26-50 in group 2:
select *, (((ROW_NUMBER() over (Order by cust_name))- 1)/25) + 1 as GroupID    from #temp1

Open in new window


Thanks everyone.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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