looping inserts

[zippynz]

Hi I am trying to loop through a comma delimited string of values and insert them into my sql 2000 database.
I got some help for this from this thread

http://www.sqlteam.com/forums/link.asp?TOPIC_ID=9921,

but they dont seem to want to help me anymore!

Ok this sproc is used to take all the details entered from a form and insert it into the appropriate tables in the database and where there is more than one value loop through and insert them all.

Here is my Procedure:

CREATE PROCEDURE ocUpdateSystem
@UserID INT,
@CPUID INT,
@DFSB INT,
@OFSB INT,
@DMultiplier INT,
@OMultiplier INT,

@MotherBoardManufacturerID INT,
@MotherBoardChipsetID INT,
@MotherBoardModel VARCHAR(20),

@MemoryManufacturerID VARCHAR(30),
@MemoryTypeID VARCHAR(30),
@MemoryCapacity VARCHAR(100),

@VideoManufacturerID VARCHAR(30),
@VideoChipsetID VARCHAR(30),
@VideoModel VARCHAR(250),
@VideoCapacity VARCHAR(250),
@VideoCoreSpeed VARCHAR(250),
@VideoMemorySpeed VARCHAR(250),


@Cooling VARCHAR(200) = NULL,
@Comments VARCHAR(200) = NULL,
@SystemID INT = NULL
AS
IF @SystemID IS NULL
BEGIN
INSERT INTO ocSystems
(UserID,CPUID,DFSB,OFSB,DMultiplier,OMultiplier,Cooling,Comments)
VALUES (@UserID,@CPUID,@DFSB,@OFSB,@DMultiplier,@OMultiplier,@Cooling,@Comments)
SELECT @SystemID = @@IDENTITY

INSERT INTO ocSystemsMotherBoard
(SystemID,ManufacturerID,ChipsetID,Model)
VALUES (@SystemID,@MotherBoardManufacturerID,@MotherBoardChipsetID,@MotherBoardModel)

INSERT INTO ocSystemsMemory
(SystemID,ManufacturerID,TypeID,Capacity)
VALUES (@SystemID,@MemoryManufacturerID,@MemoryTypeID,@MemoryCapacity)

END

DELETE FROM ocSystemsVideo
WHERE SystemID = @SystemID
INSERT INTO ocSystemsVideo (SystemID,ManufacturerID,ChipsetID,Model,Capacity,CoreSpeed,MemorySpeed)
Select
@SystemID,
Cast(
Substring(',' + @VideoManufacturerID + ',',seq,
CharIndex(',' , ',' + @VideoManufacturerID + ',' , seq) - seq) AS INT) SepValue

FROM SEQUENCE
WHERE
seq <= len(',' + @VideoManufacturerID + ',') and
Substring(',' + @VideoManufacturerID + ',', seq - 1, 1) = ',' and
CharIndex(',' , ',' + @VideoManufacturerID + ',' , seq) - seq > 0

GO



This is the part I am interested in:

INSERT INTO ocSystemsVideo (SystemID,ManufacturerID,ChipsetID,Model,Capacity,CoreSpeed,MemorySpeed)
Select
@SystemID,
Cast(
Substring(',' + @VideoManufacturerID + ',',seq,
CharIndex(',' , ',' + @VideoManufacturerID + ',' , seq) - seq) AS INT) SepValue

FROM SEQUENCE
WHERE
seq <= len(',' + @VideoManufacturerID + ',') and
Substring(',' + @VideoManufacturerID + ',', seq - 1, 1) = ',' and
CharIndex(',' , ',' + @VideoManufacturerID + ',' , seq) - seq > 0

If I change this line:
INSERT INTO ocSystemsVideo (SystemID,ManufacturerID,ChipsetID,Model,Capacity,CoreSpeed,MemorySpeed)
to this
INSERT INTO ocSystemsVideo (SystemID,ManufacturerID)
the procedure will work fine, that is it will insert the SystemID and ManufacturerID to the tableocSystemsVideo and will loop through all the values in the string.
However this isnt good enough for me!
I need to insert these other values aswell (ChipsetID,Model,Capacity,CoreSpeed,MemorySpeed)

Has anyone seen anything like this done before, I know it can be done, I just cant seem to do it!
Please help,
Thanks

 

[sguslan]

Hi,,

Your [select list] contains fewer items than the [insert list]...

So, in the select statement, you have to provide 5 more values to match the number of columns specified in insert list,namely, [ChipsetID,Model,Capacity,CoreSpeed,MemorySpeed]

Good luck

Salim