September 19, 2005 at 6:38 am
I am pulling some data from a progress database via ODBC drivers using DTS into SQL 2000 db this is working fine,
However rather than pulling all the data every day i wanted to look at the last transaction number that has been pulled across the previous day i can get that from using the MAX function on that field.
I have been trying to use lookups to only add records with a higher transaction number that i have in the destination table.
Any help would be appreciated..
Ian Paskin
September 19, 2005 at 6:49 am
You can do that in an sp using a linked server like so :
P.S. This basicaly downloads the new bills since the last transfer
CREATE PROCEDURE [dbo].[SPNFacturationTransfererNouvellesFactures]
AS
SET NOCOUNT ON
DECLARE @NouvellesFactures AS INT
DECLARE @LastFacture AS VARCHAR(10)
SET @LastFacture = (SELECT MAX(FFNOFACT) FROM dbo.AVT_FACTMA)
--transfère les entêtes
INSERT INTO dbo.AVT_FACTMA (FFCADR, FFCCOD, FFCNOM, FFCNUM, FFCOMCLI, FFCOMM, FFCREDIT, FFCVIL, FFDATE, FFGTOT, FFLCADR,
FFLCCOD, FFLCNOM, FFLCVIL, FFNOFACT, FFPRINTED, FFSS_TAX, FFTAXF, FFTAXP, FFTRANS, FFNOFACT_INT, FFCONT, Solde)
SELECT FFCADR, FFCCOD, FFCNOM, FFCNUM, FFCOMCLI, FFCOMM, FFCREDIT, FFCVIL, FFDATE, FFGTOT, FFLCADR,
FFLCCOD, FFLCNOM, FFLCVIL, FFNOFACT, 0 AS FFPRINTED, FFSS_TAX, FFTAXF, FFTAXP, FFTRANS , CAST(FFNOFACT AS INT) AS FFNOFACT_INT, FFCONT, FFGTOT
FROM Test...FACTMA
WHERE FFNOFACT > @LastFacture AND ISNUMERIC(FFNOFACT) = 1
SET @NouvellesFactures = @@ROWCOUNT
IF @NouvellesFactures > 0
BEGIN
--transfère les items
INSERT INTO AVT_FACTIT (FINOFACT, FILIGNE, FIPNOM, FIPNUM, FIPPRIX, FIPQTE, FIPVENT, FINOFACT_INT, FILIGNE_TINT)
SELECT FINOFACT, FILIGNE, FIPNOM, FIPNUM, FIPPRIX, FIPQTE, FIPVENT, CAST(FINOFACT AS INT) AS FINOFACT_INT, CAST(FILIGNE as tinyint) as FILIGNE_TINT
FROM Test...FACTIT
WHERE FINOFACT > @LastFacture AND ISNUMERIC(FINOFACT) = 1
--Update les bon de travails avec les # de facture
EXEC dbo.SPNTransfererNoFacture
END
RETURN @NouvellesFactures
SET NOCOUNT OFF
GO
September 20, 2005 at 6:17 am
Since you only need to get the current Max value one time prior to performing your inserts, I find it difficult to understand why you would use a lookup in your data pump. Maybe use a SQL Task to call your MAX query and return the value into a globalvariable.. then in your data pump transform; Increment the GlobalVariable's value by one prior to the Insert?
This would be a much better performing design than a lookup. Lookups are usually designed for returning a list of Unique ID values into a cached set from a lookup table when your insert table has a reference to the lookup table.
Hope this helps,
-Mike Gercevich
September 20, 2005 at 6:28 am
Mike,
I was thinking of adding the MAX Transaction to a GV last night when i got home i have just read your reply and it prompted me to have another look at it.
I have added a SQL task that ouputs to a GV then on sucsess it pulls the data across..
Its working great thanks for the input..
Ian Paskin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply