Hello SQL Forum,
I've written a query that bulk inserts some data into a temp table then imports new records into an existing table using NOT EXISTS. My query is as follows:
Code:
Create table #StagingForAccUpdates
(
ISOWk bigint,
SurrID bigint,
CustName nvarchar(200),
CustNum numeric(10,0),
StartDate date,
CustType nvarchar(50),
PaymentTerms nvarchar(10),
PayTermsType nvarchar(1),
WeeklyCreditLimit nvarchar(20),
Segment nvarchar(50),
Book nvarchar(20),
AccStatus nvarchar(50)
)
BULK INSERT #StagingForAccUpdates FROM 'Z:\MyFolder\MyFile.txt' WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', FIRSTROW = 2)
INSERT INTO tblCustomerAccounts(SurrID, CustomerType_ID, AccountNumber, CompanyName, StartDate)
SELECT SurrID, CT.ID, CustNum, CustName, StartDate
FROM #StagingForAccUpdates ST
JOIN tblCustomerTypes CT
ON ST.CustType = CT.CustomerType
WHERE NOT EXISTS(
SELECT *
FROM tblCustomerAccounts CA
WHERE CA.SurrID = ST.SurrID
)
When I ran the script some new records were imported (as expected) but 4 remain. To validate the SurrIDs, I put the values into a spreadsheet and was able to VLOOKUP and identify the IDs in question.
Is there a specific reason why these few rows might not get identified using the method outlined above?
Many thanks