Help with an Insert Statement

Last Post 02 Oct 2007 03:52 AM by Pologuy67. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Pologuy67
New Member
New Member

--
28 Sep 2007 09:37 AM
I am trying to create a stored Procedure with an INSERT INTO statement
INSERT INTO HB ( ST_ID,ConNum,LvlCode,CustID)
SELECT DISTINCT @ST_ID, (HB1.ConNum + 1) , 22, 1,C.CustID
FROM Customers C
INNER JOIN (SELECT TOP 1 HB.ConNum, HB.ST_ID FROM HB
WHERE HB.ST_ID = @ST_ID ORDER BY HL.HB_ID DESC) HB1 ON HB1.ST_ID = @ST_ID

I am having trouble getting the ConNum to increment properly
HB db

HB_ID|ST_ID|ConNum|LvlCode|CustID
1|1|1|20|NULL
2|2|1|20|NULL

I need to insert into this table all customers from a given ST_ID in this case the ST_ID is 1.
The row to follow should look like this

3|1|2|22|DAS
4|1|3|22|CVG
5|1|4|22|NMK

If the ST_ID was 2 it would be the following
6|2|2|22|POL
7|2|3|22|KJN
8|2|4|22|YER etc..

How would I accomplish this.
SQLUSA
New Member
New Member

--
29 Sep 2007 05:47 AM
Is ConNum setup as an autoincrement IDENTITY (1,1) ?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/ - The Best SQL Server
Training in the World
Pologuy67
New Member
New Member

--
02 Oct 2007 03:52 AM
No it cant be an identity. I am thinking I might have to use a cursor. If anyone can come up with any other ideas it would be really helpful
SQLUSA
New Member
New Member

--
02 Oct 2007 09:59 PM
Here are your choices for sequencing:

1. Identity(1,1)
2. Correlated subquery
3. While loop

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com
SwePeso
New Member
New Member

--
02 Oct 2007 11:23 PM
DECLARE @Target TABLE (RowID INT, Data VARCHAR(12))

INSERT @Target
SELECT 1, 'Peso' UNION ALL
SELECT 2, 'rm' UNION ALL
SELECT 3, 'Nosepicker'

DECLARE @Source TABLE (Data VARCHAR(12))

INSERT @Source
SELECT 'pologuy67' UNION ALL
SELECT 'SQLUSA'

-- Use simple correlated subquery
INSERT @Target
SELECT (SELECT MAX(RowID) + 1 FROM @Target),
Data
FROM @Source

-- Proof that simple correlated subquery will not work
SELECT *
FROM @Target

DELETE
FROM @Target
WHERE RowID > 3

-- Use double correlated subquery
INSERT @Target
SELECT (SELECT MAX(RowID) FROM @Target) + (SELECT COUNT(*) FROM @Source AS s2 WHERE s2.Data <= s1.Data),
s1.Data
FROM @Source AS s1

-- Proof that double correlated subquery will work
SELECT *
FROM @Target
You are not authorized to post a reply.

Acceptable Use Policy