Insert row by row using composite primary key

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 2000 Forums

SQL Server Development (2000)

Insert row by row using composite primary key

Author

Topic

mr_blore
Starting Member

7 Posts

Posted - 2004-11-08 : 06:09:37

Hi
I have a table with composite primary key. The values look like this

Visit_No Provider_Code Role_Code
1356 1 2
1500 1 1
1500 1 4
1500 2 1

These 3 form the composite primary keys..so a combination of these 3 should be unique.
I have a table variable populated with these values.
How do i go about looping thro the table and insert one row by row?
Anybody help!!

-Meera

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-08 : 06:43:29

Why would you want to loop through the table and insert only one row by row ???

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 07:09:02

Put an identity on the table variable and loop through that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mr_blore
Starting Member

7 Posts

Posted - 2004-11-08 : 23:48:28

I wanna loop thro it ONE by ONE as i need to capture the visit_no, provider_code and role_code of a particular row in case a row/ or many rows fail. For ex. @MedTemp is the table variable with only 1 primary key (MED_NO).
This is how I loop thro the @MedTemp to capture the error row-wise in case of @@ERROR

SELECT @iLoopCount = COUNT(*) FROM @MedTemp
SELECT @iMedNo = MAX(MED_NO) FROM @MedTemp

WHILE(@iLoopCount > 0)
BEGIN

INSERT INTO Medication (MED_CODE,VST_NO,CHRON ,CONDITION_NO)
SELECT MED_CODE,VST_NO,CHRON ,CONDITION_NO FROM @MedTemp WHERE MED_NO = @iMedNo

SET @err = @@ERROR
IF @err <> 0
BEGIN
SET @errList = @errList + cast(@iMedNo as varchar) + ','
END

SET @iLoopCount = @iLoopCount -1
SELECT @iMedNo = MAX(MED_NO) FROM @MedTemp WHERE MED_NO < @iMedNo

END

So how can this be extended for composite primary key?

Thanks
Meera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources