i got the following statements.
the line
SQL.Add('If object_id('''+'tempdb..#POSSales'+''') IS NOT NULL DROP TABLE #POSSales')
does not seems to work during execution. it does not detect the #POSSales table in the object_id. This result a error; #possales already exist in the subsequent loop.
Please advise. i am using I am using windows 7, delphi xe.
regards and thanks in advance.
rashid.
Code: Select all
repeat
WITH MSScriptPOSIm DO
BEGIN
SQL.Add('If object_id('''+'tempdb..#POSSales'+''') IS NOT NULL DROP TABLE #POSSales');
SQL.Add('CREATE TABLE #POSSales (InvoiceNumber varChar(11), SalesDate varchar(11), CustomerNumber varchar(20), ItemNumber varchar(30), Qty real, [Description] varcHar(255),');
SQL.Add('ExTaxPrice Money Default 0 Not Null, IncTaxPrice Money Default 0 Not Null, TaxCode Varchar(3), Inclusive Varchar(1), Location Varchar(10), ExportDate VarcHar(11),');
SQL.Add('TotalInvAmt Money Default 0 Not Null)');
SQL.Add('BULK INSERT #POSSales FROM '''+ DumpFileLocation+SearchResult.Name + '''WITH (FIRSTROW = 1)');
SQL.ADD('INSERT INTO [POSSales] ([InvoiceNumber],[SalesDate],[CustomerNumber],[ItemNumber],[Qty],[Description],[ExTaxPrice],');
SQL.ADD('[IncTaxPrice],[TaxCode],[Inclusive],[Location],[ExportDate],[TotalInvAmt],[ImpFileName],[EnteredDate])');
SQL.ADD('SELECT [InvoiceNumber],[SalesDate],[CustomerNumber],[ItemNumber],[Qty],[Description],[ExTaxPrice],');
SQL.ADD('[IncTaxPrice],[TaxCode],[Inclusive],[Location],[ExportDate],[TotalInvAmt],'''+SearchResult.Name+''',GETDATE() FROM [#POSSales]');
Execute;
END;
until FindNext(searchresult) <> 0;
FindClose(searchResult);
end;