Generate a unique no - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Generate a unique no

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-10-03 : 08:38:20

Hi,

I have the following tables :

Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) ,  [cityno] int ,  Callid int,  UniqueNo int);
 
Create Table #Call(Callid int,Calltype int,callDetailid int  ) 
// CallType 1=New 2=Change 3=Delete

Create Table #CallDetail(callDetailId int,empid int)

The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on

I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType and insert into #Request table along with the other data. How do I do this?

SAMPLE DATA

[Code]Insert into #CallDetail(12123,1)
Insert into #CallDetail(53423,1)
Insert into #CallDetail(6532,1)
Insert into #CallDetail(62323,1)
Insert into #CallDetail(124235,1)
Insert into #CallDetail(65423,2)
Insert into #CallDetail(56234,2)
Insert into #CallDetail(2364,2)
Insert into #CallDetail(34364,2)
Insert into #CallDetail(85434,2)

Insert Into #Call(111,1,12123)
Insert Into #Call(112,1,53423)
Insert Into #Call(114,1,6532)
Insert Into #Call(123,2,62323)
Insert Into #Call(134,1,124235)
Insert Into #Call(143,2,65423)
Insert Into #Call(145,1,56234)
Insert Into #Call(154,2,2364)
Insert Into #Call(185,1,34364)
Insert Into #Call(195,1,85434)

Insert Into #request Values('324234','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);
Insert Into #request Values('225652','Jim','SA055',67,143,0);
Insert Into #request Values('126756','Jasm','SA055',67,145,0);
Insert Into #request Values('786234','Chuck','SA055',67,154,0);
Insert Into #request Values('66234','Mutuk','SA059',72,185,0);
Insert Into #request Values('2232362','Buck','SA055',67,195,0);
[/code] Plz help. I am stuck from 2 days on this :

lalit

lols
Posting Yak Master

174 Posts

Posted - 2007-10-03 : 08:39:45

EXPECTED OUTPUT will be (See the last column for unique nos). :

[Code]Insert Into #request Values('324234','Jack','SA023',12,111,1);

Insert Into #request Values('223452','Tom','SA023',12,112,2);

Insert Into #request Values('456456','Bobby','SA024',12,143,1); // Calltype = 1 empid= 1, but state is different, hence unique id is 1

Insert Into #request Values('22322362','Guck','SA024',44,114,1);

Insert Into #request Values('22654392','Luck','SA023',12,123,3);

Insert Into #request Values('225652','Jim','SA055',67,143,1);

Insert Into #request Values('126756','Jasm','SA023',69,134,1);

Insert Into #request Values('786234','Chuck','SA023',72,145,2);

Insert Into #request Values('66234','Mutuk','SA059',72,185,1);

Insert Into #request Values('2232362','Buck','SA055',67,195,2);
[/code]

Please note that this will not be run as a batch query, but the no. has to be generated and inserted into #record table in realtime. I have given bulk of records for understanding of the problem

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-10-03 : 09:23:48

how about an insert trigger. do a count of (select Per empid+ Per StateNo+ Per CityNo+ Per CallType ), and insert that count + 1 into your unique ID field

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

lols
Posting Yak Master

174 Posts

Posted - 2007-10-03 : 09:29:35

I was speaking to a friend of mine and he told CTE and partioning would be a solution to it. I just didnt get a word of what he said :).

Triggers cannot be used as there is a procedure in place that inserts all the other fields of the Request table. All that needs to be done is write a query to generate this unique no and then insert it along with the other fields.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 09:42:24

[code]-- Prepare sample data
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30), StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, 1 UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, 2 UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, 1 UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, 1 UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, 3 UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, 1 UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, 1 UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, 2 UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, 1 UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, 2

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 53423 UNION ALL
SELECT 114, 1, 6532 UNION ALL
SELECT 123, 2, 62323 UNION ALL
SELECT 134, 1, 124235 UNION ALL
SELECT 143, 2, 65423 UNION ALL
SELECT 145, 1, 56234 UNION ALL
SELECT 154, 2, 2364 UNION ALL
SELECT 185, 1, 34364 UNION ALL
SELECT 195, 1, 85434

DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT)

INSERT @CallDetail
SELECT 12123, 1 UNION ALL
SELECT 53423, 1 UNION ALL
SELECT 6532, 1 UNION ALL
SELECT 62323, 1 UNION ALL
SELECT 124235, 1 UNION ALL
SELECT 65423, 2 UNION ALL
SELECT 56234, 2 UNION ALL
SELECT 2364, 2 UNION ALL
SELECT 34364, 2 UNION ALL
SELECT 85434, 2

-- Show original data together with "new" SequenceNumber
SELECT r.*,
p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallType ORDER BY r.RequestID) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
ORDER BY r.RequestID[/code]Why are there discrepancies?

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-03 : 10:17:42

Thanks peso. I am sorry. Could you please point the discrepancy

Just one small question. I know its a silly one but my mind is blocked now. The query allots a unique id for all the rows. But what do i do if i need to generate this query realtime for that particular row only.

Eg: Earlier in my stored proc, i used to insert this

Insert Into #request Values('324234','Jack','SA023',12,111); -- note that unique no is not there

But now since this new column has been added, i want to generate it and store it real time based on the unique no logic. How will i do this.

Declare @uniqueno as int
select @uniqueno = -- unique code logic (what to write here?)
Insert Into #request Values('324234','Jack','SA023',12,111,@uniqueno);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-03 : 10:23:38

[code]UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallType ORDER BY r.RequestID) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-03 : 10:56:47

thanks again..i tried out a small test

DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, 1 UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, 2 UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, 1 UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, 1 UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, 3 UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, 1 UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, 1 UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, 2 UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, 1 UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, 2

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 53423 UNION ALL
SELECT 114, 1, 6532 UNION ALL
SELECT 123, 2, 62323 UNION ALL
SELECT 134, 1, 124235 UNION ALL
SELECT 143, 2, 65423 UNION ALL
SELECT 145, 1, 56234 UNION ALL
SELECT 154, 2, 2364 UNION ALL
SELECT 185, 1, 34364 UNION ALL
SELECT 195, 1, 85434 UNION ALL
SELECT 200, 1, 22222

DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT)

INSERT @CallDetail
SELECT 12123, 1 UNION ALL
SELECT 53423, 1 UNION ALL
SELECT 6532, 1 UNION ALL
SELECT 62323, 1 UNION ALL
SELECT 124235, 1 UNION ALL
SELECT 65423, 2 UNION ALL
SELECT 56234, 2 UNION ALL
SELECT 2364, 2 UNION ALL
SELECT 34364, 2 UNION ALL
SELECT 85434, 2 UNION ALL
SELECT 22222,2

INSERT @Request
SELECT '55555', 'JSUP', 'SA055', 67, 200, NULL

UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallType ORDER BY r.RequestID) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL

SELECT r.*,
p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallType ORDER BY r.RequestID) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
ORDER BY r.RequestID

For JSUP, it show the uniqueid as 1, whereas it needs to be 2 as 'Jasm' who has the same empid,state, city and calltype already has unique id as 1. So as per the condition, JSUP should get uniqueid as 2. ANy clues?

lols
Posting Yak Master

174 Posts

Posted - 2007-10-03 : 23:12:41

a jar full of thanks. :) i figured it out.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-10-03 : 23:16:31

This is a really funny requirement. Why do you want it?

lols
Posting Yak Master

174 Posts

Posted - 2007-10-03 : 23:35:07

:) requirements are always given by clients and they are always funny..especially when they want an exact replica of their manual system :)

btw, i am doing a oil tender reservation system and this unique no is used to track results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 01:47:14

Great!
What did you figure out?

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-04 : 02:42:01

I thought i had but i am wrong after carrying out a few more tests. Please read this :

i tried out a small test

DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, 1 UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, 2 UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, 1 UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, 1 UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, 3 UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, 1 UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, 1 UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, 2 UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, 1 UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, 2

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 53423 UNION ALL
SELECT 114, 1, 6532 UNION ALL
SELECT 123, 2, 62323 UNION ALL
SELECT 134, 1, 124235 UNION ALL
SELECT 143, 2, 65423 UNION ALL
SELECT 145, 1, 56234 UNION ALL
SELECT 154, 2, 2364 UNION ALL
SELECT 185, 1, 34364 UNION ALL
SELECT 195, 1, 85434 UNION ALL
SELECT 200, 1, 22222 -- New row

DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT)

INSERT @CallDetail
SELECT 12123, 1 UNION ALL
SELECT 53423, 1 UNION ALL
SELECT 6532, 1 UNION ALL
SELECT 62323, 1 UNION ALL
SELECT 124235, 1 UNION ALL
SELECT 65423, 2 UNION ALL
SELECT 56234, 2 UNION ALL
SELECT 2364, 2 UNION ALL
SELECT 34364, 2 UNION ALL
SELECT 85434, 2 UNION ALL
SELECT 22222,2 -- new row

INSERT @Request
SELECT '55555', 'JSUP', 'SA055', 67, 200, NULL - New row

UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallType ORDER BY r.RequestID) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL

SELECT r.*,
p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallType ORDER BY r.RequestID) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
ORDER BY r.RequestID

For JSUP, it shows the uniqueid as 1, whereas it needs to be 3 as 'Jasm' and 'Buck' who have the 'same' empid,state, city and calltype already has unique id as 1 & 2. So as per the condition, JSUP should get uniqueid as 2. ANy clues?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 02:47:54

Remove cd.EmpID from the partitioning?

-- Prepare sample data
DECLARE	@Request TABLE(RequestID INT, CustomerName VARCHAR(30), StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT	@Request
SELECT	'324234', 'Jack', 'SA023', 12, 111, 1 UNION ALL
SELECT	'223452', 'Tom', 'SA023', 12, 112, 2 UNION ALL
SELECT	'456456', 'Bobby', 'SA024', 12, 114, 1 UNION ALL
SELECT	'22322362', 'Guck', 'SA024', 44, 123, 1 UNION ALL
SELECT	'22654392', 'Luck', 'SA023', 12, 134, 3 UNION ALL
SELECT	'225652', 'Jim', 'SA055', 67, 143, 1 UNION ALL
SELECT	'126756', 'Jasm', 'SA055', 67, 145, 1 UNION ALL
SELECT	'786234', 'Chuck', 'SA055', 67, 154, 2 UNION ALL
SELECT	'66234', 'Mutuk', 'SA059', 72, 185, 1 UNION ALL
SELECT	'2232362', 'Buck', 'SA055', 67, 195, 2

DECLARE	@Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT	@Call
SELECT	111, 1, 12123 UNION ALL
SELECT	112, 1, 53423 UNION ALL
SELECT	114, 1, 6532 UNION ALL
SELECT	123, 2, 62323 UNION ALL
SELECT	134, 1, 124235 UNION ALL
SELECT	143, 2, 65423 UNION ALL
SELECT	145, 1, 56234 UNION ALL
SELECT	154, 2, 2364 UNION ALL
SELECT	185, 1, 34364 UNION ALL
SELECT	195, 1, 85434

DECLARE	@CallDetail TABLE(CallDetailID INT, EmpID INT)

INSERT	@CallDetail
SELECT	12123, 1 UNION ALL
SELECT	53423, 1 UNION ALL
SELECT	6532, 1 UNION ALL
SELECT	62323, 1 UNION ALL
SELECT	124235, 1 UNION ALL
SELECT	65423, 2 UNION ALL
SELECT	56234, 2 UNION ALL
SELECT	2364, 2 UNION ALL
SELECT	34364, 2 UNION ALL
SELECT	85434, 2

-- Show original data together with "new" SequenceNumber
SELECT		r.*,
		p.RecID
FROM		@Request AS r
INNER JOIN	(
			SELECT		r.RequestID,
					ROW_NUMBER() OVER (PARTITION BY r.StateNo, r.CityNo, c.CallType ORDER BY r.RequestID) AS RecID
			FROM		@Request AS r
			INNER JOIN	@Call AS c ON c.CallID = r.CallID
			INNER JOIN	@CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
		) AS p ON p.RequestID = r.RequestID
ORDER BY	r.RequestID

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-04 : 03:01:55

thanks.
I still get unique no. of JSUP as 1. It should be 3.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 03:14:51

Who is JSUP? Can't see in sample data.

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-04 : 03:42:41

JSUP is a new record i am trying to insert. It is there in my previous post. Here it is :

INSERT @Request
SELECT '55555', 'JSUP', 'SA055', 67, 200, NULL -- New row

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 03:56:58

How would you know what CALLTYPE this new record has?
There is no corresponding record in Call table.

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-04 : 04:01:29

Hi,

thanks for being patient with me.

There is a record. In my post, i have marked the record as "--New Row"

CallDetail - SELECT 22222,2 -- new row
Call - SELECT 200, 1, 22222 -- New row
REquest - SELECT '55555', 'JSUP', 'SA055', 67, 200, NULL -- New row

To repeat, in my first post i had mentioned that the tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 04:06:30

When I insert the THREE new rows you recently posted, I still get correct result

-- Prepare sample data
DECLARE	@Request TABLE(RowID INT IDENTITY(1, 1), RequestID INT, CustomerName VARCHAR(30), StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT	@Request
SELECT	'324234', 'Jack', 'SA023', 12, 111, 1 UNION ALL
SELECT	'223452', 'Tom', 'SA023', 12, 112, 2 UNION ALL
SELECT	'456456', 'Bobby', 'SA024', 12, 114, 1 UNION ALL
SELECT	'22322362', 'Guck', 'SA024', 44, 123, 1 UNION ALL
SELECT	'22654392', 'Luck', 'SA023', 12, 134, 3 UNION ALL
SELECT	'225652', 'Jim', 'SA055', 67, 143, 1 UNION ALL
SELECT	'126756', 'Jasm', 'SA055', 67, 145, 1 UNION ALL
SELECT	'786234', 'Chuck', 'SA055', 67, 154, 2 UNION ALL
SELECT	'66234', 'Mutuk', 'SA059', 72, 185, 1 UNION ALL
SELECT	'2232362', 'Buck', 'SA055', 67, 195, 2 UNION ALL
SELECT	'55555', 'JSUP', 'SA055', 67, 200, 3			-- New row

DECLARE	@Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT	@Call
SELECT	111, 1, 12123 UNION ALL
SELECT	112, 1, 53423 UNION ALL
SELECT	114, 1, 6532 UNION ALL
SELECT	123, 2, 62323 UNION ALL
SELECT	134, 1, 124235 UNION ALL
SELECT	143, 2, 65423 UNION ALL
SELECT	145, 1, 56234 UNION ALL
SELECT	154, 2, 2364 UNION ALL
SELECT	185, 1, 34364 UNION ALL
SELECT	200, 1, 22222 UNION ALL		-- New row
SELECT	195, 1, 85434

DECLARE	@CallDetail TABLE(CallDetailID INT, EmpID INT)

INSERT	@CallDetail
SELECT	12123, 1 UNION ALL
SELECT	53423, 1 UNION ALL
SELECT	6532, 1 UNION ALL
SELECT	62323, 1 UNION ALL
SELECT	124235, 1 UNION ALL
SELECT	65423, 2 UNION ALL
SELECT	56234, 2 UNION ALL
SELECT	2364, 2 UNION ALL
SELECT	34364, 2 UNION ALL
SELECT	22222, 2 UNION ALL	-- New row
SELECT	85434, 2

-- Show original data together with "new" SequenceNumber
SELECT		r.*,
		p.RecID
FROM		@Request AS r
INNER JOIN	(
			SELECT		r.RequestID,
					ROW_NUMBER() OVER (PARTITION BY r.StateNo, r.CityNo, c.CallType ORDER BY r.RowID) AS RecID
			FROM		@Request AS r
			INNER JOIN	@Call AS c ON c.CallID = r.CallID
			INNER JOIN	@CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
		) AS p ON p.RequestID = r.RequestID
ORDER BY	r.RowID

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 04:09:54

Insert all records you want, and then do this

UPDATE		r
SET		r.UniqueNo = p.RecID
FROM		@Request AS r
INNER JOIN	(
			SELECT		r.RequestID,
					ROW_NUMBER() OVER (PARTITION BY r.StateNo, r.CityNo, c.CallType ORDER BY r.RowID) AS RecID
			FROM		@Request AS r
			INNER JOIN	@Call AS c ON c.CallID = r.CallID
			INNER JOIN	@CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
		) AS p ON p.RequestID = r.RequestID
WHERE		r.UniqueNo IS NULL

Please notice that there is an IDENTITY on the Request table. Otherwise you will not know how many records "of same kind" has been inserted before.

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources