February 9, 2011 at 8:07 am
I thought this was simple and I guess not, missing a piece maybe:
There is a file that is continously appended to during the day, every few minutes my SSIS package goes out uploads the data into a temp table from the temp table I wana put data into a perm table but only the data that is new, this can be determined by the "DateRan" field which contains date and time.
This is what I wrote:
insert into LapLine_InString
(DateRan
,GearSetSN
,GearSetPN
,MntgDimPinion
,MntgDimGear
,ProdOrderPinion
,ProdOrderGear
,PartNumberPinion
,PartNumberGear
,HighHeadPinion
,RevLevel
,StationID)
select
DateRan
,GearSetSN
,GearSetPN
,MntgDimPinion
,MntgDimGear
,ProdOrderPinion
,ProdOrderGear
,PartNumberPinion
,PartNumberGear
,HighHeadPinion
,RevLevel
,StationID
from tmp_LapLineData_InString
where tmp_LapLineData_InString.DateRan != LapLine_InString.DateRan
Now I get the error :
The multi-part identifier "LapLine_InString.DateRan" could not be bound.
Is there more I need to add? Am I missing a piece?
Thanks!!
February 9, 2011 at 8:14 am
You need to use a NOT EXISTS clause (see below)
insert into LapLine_InString
(DateRan
,GearSetSN
,GearSetPN
,MntgDimPinion
,MntgDimGear
,ProdOrderPinion
,ProdOrderGear
,PartNumberPinion
,PartNumberGear
,HighHeadPinion
,RevLevel
,StationID)
select
DateRan
,GearSetSN
,GearSetPN
,MntgDimPinion
,MntgDimGear
,ProdOrderPinion
,ProdOrderGear
,PartNumberPinion
,PartNumberGear
,HighHeadPinion
,RevLevel
,StationID
from tmp_LapLineData_InString
where NOT EXISTS (Select 1 From LapLineData_InString
Where LapLineData_InString.DateRan = tmp_ LapLine_InString.DateRan)
That should only load data from the tmp table where the DateRan is not in the Permenant table.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 9, 2011 at 8:33 am
:w00t: Thank you, thank you!!
I tried to use the EXISTS command but the couple ways I did it were wrong.
Appreciate it!! 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply