How to add records from one table to another without duplicating.

[Niki_S]

I have a cursor as below.

Use In Select('Missing')

SELECT _DelDtl
SCAN 
	TEXT TO stra NOSHOW
	SELECT * FROM vMER_Master_LotColors WHERE nStylecode=?_DelDtl.nStylecode and clotname=?_DelDtl.cLotName
	ENDTEXT
	SQLExec(hndOps,stra,'_TempMissing')
	If Not Used('Missing')
		Select * From  _TempMissing Into Cursor Missing Readwrite
	Else
		Select Missing
		Append From Dbf('_TempMissing')
	Endif
ENDSCAN

And now I want to add these records into my _table1. Both tables have same records but, in Missing cursor there have some additional records. So I need to add those into _table1 without duplicating. How can I do this?

Thank you

 

[Chris Miller]

First off, how many stylecodes and lotnames are inn _DelDtl? All?
Then you could shorten this and not need to go record by record. Simply select all data from v_Mer_MasterColors.

Now for the main question , assuming _table1 hs the same fields as the cursor Missong:

1. DELETE FROM Misssing INNER JOIN _table1 ON _table1.nStylecode = Missing.Stylecode And _table1.cLotName = Missing.cLotname
That deletes all records that don't deserve to be called missing, because they are found. The rest are miswsing
2. SELECT _table1
3. APPEND FROM DBF('Missing')

Chriss

 

[Chris Miller]

That said, again I'm not sure about your logical planning capabilities, sorry.

As you get Missing by scanning through _DelDtl, you couly also check at that time, which _DelDtl combinations of stylecode and lotnumber are already in _table1 and don't query them into Missing at all. You don't need to remove things you didn't put in.

Solve problems as early as you can, or you have to do a load of things that are unnecessary.

Chriss

 

[Niki_S]

OK, I did it like this but it says file 'missing.dbf' does not exist. But there already created my cursor.

Use In Select('Missing')

SELECT _DelDtl
SCAN 
	TEXT TO stra NOSHOW
	SELECT nStylecode,cAccountNo,cContract,cStyleNumber as Style,cLotName,cCategory,nCiQty,cShipFty,cColorName FROM vMER_Master_LotColors WHERE nStylecode=?_DelDtl.nStylecode and clotname=?_DelDtl.cLotName and cStyleNumber=?_DelDtl.Style
	ENDTEXT
	SQLExec(hndOps,stra,'_TempMissing')
	If Not Used('Missing')
		Select * From  _TempMissing Into Cursor Missing Readwrite
	Else
		Select Missing
		Append From Dbf('_TempMissing')
	Endif
ENDSCAN

SELECT * FROM Misssing INTO dbf C:\Tempfiles\_Missing   && the error message displayed in this row
DELETE * FROM C:\Tempfiles\_Missing INNER JOIN _DelDtl ON _DelDtl.nStylecode = _Missing.Stylecode And _DelDtl.cLotName = _Missing.cLotname AND _DelDtl.Style= _Missing.Style

How can I fix this?

 

[Niki_S]

I fixed it by doing like this.

Use In Select('Missing')

SELECT _DelDtl
SCAN 
	TEXT TO stra NOSHOW
	SELECT nStylecode,cAccountNo,cContract,cStyleNumber as Style,cLotName,cCategory,nCiQty,cShipFty,cColorName FROM vMER_Master_LotColors  WHERE cColorName NOT IN  (SELECT cColorName FROM MIS.dbo.vInvFinal WHERE cLotName=?_DelDtl.cLotName AND nStyleCD=?_DelDtl.nStylecode) AND nStylecode=?_DelDtl.nStylecode and clotname=?_DelDtl.cLotName and cStyleNumber=?_DelDtl.Style 
	ENDTEXT
	SQLExec(hndOps,stra,'_TempMissing')
	If Not Used('Missing')
		Select * From  _TempMissing Into Cursor Missing Readwrite
	Else
		Select Missing
		Append From Dbf('_TempMissing')
	Endif
ENDSCAN


SELECT _DelDtl 
APPEND FROM DBF('Missing')

Thanks for the help

 

[Chris Miller]

It's DELETE FROM, not DELETE * FROM, besides that Missing is an alias name, not a file name. If you look at DBF('Missing') you get a TMP filename that looks like a value SYS(2015) creates.

SQL can work on alias names and file names, APPEND only appends from files, therefore you already learned and make frequent use of the method to accumulate a result by appending from DBF('TempMissong') instead of APPEND FROM _TEmpMissing, which wouldn't work.

I see you now select fewer records from vMER_Master_LotColors with the additional WHERE... NOT IN.... clause. Maybe you could even already delete or skip records of _DelDtl.

Your query uses the fields cLotName, nStylecode, and Style. And that means if two _DelDtl records have the same values in these 3 fields, you still introduce double records. They are missing due to the WHERE... NOT IN.. clause, but you then append them twice (or even more times).

What I would do to avoid that is not iterate over _DelDtl. but over the "concentrated" list you first create with

Select Distinct Style, nStylecode, cLotname  From _DelDtl Into Cursor _DelDtlUnique

or

Select Style, nStylecode, cLotname  From _DelDtl Group By Style, nStylecode, cLotname Into Cursor _DelDtlUnique

Remark: SQL purists avoid using Distinct. I think DISTINCT is a very good use case here.
After that scan/endscan over _DelDtlUnique instead of _DelDtl and adjust the query, of course.

Chriss

 

[Niki_S]

DELETE FROM _Missing INNER JOIN _DelDtl ON _DelDtl.nStylecode = _Missing.Stylecode And _DelDtl.cLotName = _Missing.cLotname AND _DelDtl.Style= _Missing.Style

This statement is not working. It says "syntax error".

 

[Chris Miller]

See the help, please:

https://www.vfphelp.com/help/_5wn12pdbu.htm

The syntax of DELETE does not allow DELETE *, by the nature of DLETE you always delete whole records, there is no place for a field list.

If you use my suggestion you don't need the DELETE SQL at all, because you don't double records in the first place.
Don't scan..endscan over _DelDtl, first create _DelDtlUnique and loop over that.

Chriss