Hi,
I am relatively new to writing SQL so I apologise if this is something I should know.
I have the following statement;
declare @prod varchar(25)
set @prod = (select top 1 prodref from dba.proditem where prodcateg = '84' and created >= today() and ((charindex('/W/',proddesc) > 0)) or (charindex('/G/',proddesc) > 0))
declare @cnt INT
set @cnt = 1
while @cnt <= (select count(prodref) from dba.proditem where prodcateg = '84' and created >= today() and ((charindex('/W/',proddesc) > 0)) or (charindex('/G/',proddesc) > 0))
BEGIN
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601210GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601215GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601220GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601230GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601240GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601250GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601260GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601270GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601280GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'601190GST',100,1,3)
INSERT INTO dba.proditemlinks(opco_code, prodref, linked_prodref,defaultquantity,ratioquantity,linktype)
VALUES ('C',@prod,'6012299GST',100,1,3)
set @cnt = @cnt+1
END
My code seems to do as I expected, but I get the following error appear;
Could not execute statement.
No primary key value for foreign key 'proditem001' in table
'proditemlinks'
My first questions is what is proditem001? It doesn't exist in our system. Also what is the error in my code?
Here is a SQL example to accompany Mark's answer...