Temp Tables or Something Else ??
-
Hello,
First, I read the post with "Temp Table in DTS" subject line, but didn't find what I am looking for... the only reply to that post said, "...there are two ways to work around this." and that's all. What’s the work around??
My scenario:
Text/CSV file as a SOURCE of data.
SQL Server table as DESTINATION of data.
Data in SOURCE needs to be either IMPORTED into the DESTINATION table or UPDATE the existing data in the DESTINATION table based on key field values that match between SOURCE and existing DESTINATION data. So if the record doesn't exist then create a new record, but if the record exists then just update it with the new values coming from the SOURCE CSV.
What would be the best way to accomplish this? I was thinking to dump the data in a temp table in SQL Server then write a store procedure that looks at the data from this temp table and either inserts the data in the destination table or updates it if records already exist. But as the user of other related thread noted, you can't select a temp table as destination or reference it as source so what would be the work around to this? Is there a different approach or method to do what I need to without the use of temp table?
Any suggestions are greatly appreciated.
Thanks.
JN
-
Your going down the right track. Instead of using a temp table in tempdb, use a permanent table in your database, or if need be, a seperate staging database. Just truncate the table before importing new data and you're stored procedure approach works just fine
EG: Your tasks would be like this,
ExecuteSQL - Truncate work table
Datapump - Import from CSV into work table
ExecuteSQL - Exec SP to update existing records
ExecuteSQL - Exec SP to insert new records
--------------------
Colt 45 - the original point and click interface
-
Thanks, Phil, for your response and suggestion.
If there are different ways to do this, please also post it here otherwise, I will use the way I was already thinking about and which Phil also suggested.
-
Given your requirements I would have to agree with Phil, you appear to be on the right track and the flow Phil outlined should do the trick.
Good Luck,
Darrell
-
I can think of 2 ways to do it :
1. Use a stored procedure and a "permanent" temp table, that is a table you create in advance and truncate before the transaform task using a query task (pre processing). The transformation should be followed by another query task that executes your stored procedure (post precessing).
2. Use a data driven task. For each source record, it checks for existence in the destination table. If exists it fires the update query, otherwise it fires the insert query.
My preference goes to the first method. It is cheaper and allows you to do more work (i.e. checking for integrity, computing counts, etc.) during the post processing phase and before commiting data to the datawarehouse.
HABIB.
-
I have been doing it for the past two years using the method described by Phil. It's fast and very effecient. Just remember to either truncate the loader table first (I actually run a truncate before and after the loads just in case)
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc -
Instead of Truncating can't I just DROP the table after I am done prossesing the values out of it? This way I create the table/indexes each time I impor the data and drop it once the import is complete. Just wondering if there is an advantage of using Truncase over DROP?
Thanks.
JN
-
>>Just wondering if there is an advantage of using Truncate over DROP?
The advantage is not having to code for exceptions. What happens if your package fails before completion and doesn't execute the DROP ? Now you have to code defensively at the start of your package, otherwise your CREATE step will cause an error if the previous run didn't complete the DROP.
-
What happens if your package fails before completion and doesn't execute the DROP ?
Solution is simple. I DROP the table in the same script that creates the table so every time the import package is run if the table already exist then it's dropped then the script moves on to creating it again.
-
Hello,
I am trying the SP method and facing an INSERT issue...
Why does the following Insert INTO statement inserts NO records?
***********************************************
INSERT INTO Prop
(ListingID, Address, City, Zip, ListPrice, PropCID, PropTID)
SELECT
LSID, B.Address, B.City, B.Zip, B.Price,
PropCID = (CASE B.PropID WHEN 'A' THEN 'AA' WHEN 'B' THEN 'BB' WHEN 'C THEN 'CC' WHEN 'D' THEN 'DD' WHEN 'E' THEN 'EE'
WHEN 'F' THEN 'FF' ELSE 'GG' END),
PropTID = (CASE B.PropTID WHEN 'A' THEN 'A1' WHEN 'B' THEN 'B2' WHEN 'C' THEN 'C2' WHEN 'D' THEN 'D2' WHEN 'E' THEN 'E2'
WHEN 'F' THEN 'F2' ELSE 'G2' END)
FROM PropMaster A, tmp_Listings B
WHERE A.ListingID <> B.LSID
*********************************
If I remove the PropMaster A reference in FROM clause and remove the WHERE condition then it inserts all the records, but not if I leave it as it is above. Does anyone know why this happens or how I can rewrite it to achieve what I am looking for? Basically, I want to insert records if they are not found in the table to avoid duplicates. If there is a different way of doing this, please suggest.
Thanks.
JN
-
Use a LEFT JOIN.
INSERT INTO Prop (ListingID, Address, City, Zip, ListPrice, PropCID, PropTID) SELECT LSID, B.Address, B.City, B.Zip, B.Price, PropCID = (CASE B.PropID WHEN 'A' THEN 'AA' WHEN 'B' THEN 'BB' WHEN 'C THEN 'CC' WHEN 'D' THEN 'DD' WHEN 'E' THEN 'EE' WHEN 'F' THEN 'FF' ELSE 'GG' END), PropTID = (CASE B.PropTID WHEN 'A' THEN 'A1' WHEN 'B' THEN 'B2' WHEN 'C' THEN 'C2' WHEN 'D' THEN 'D2' WHEN 'E' THEN 'E2' WHEN 'F' THEN 'F2' ELSE 'G2' END) FROM tmp_Listings B LEFT JOIN PropMaster A ON A.ListingID = B.LSID WHERE A.ListingID IS NULL
--------------------
Colt 45 - the original point and click interface -
Hello!
Just to mention... Of course you can use "normal" temporary tables in DTS.
Just create it and use it as normal. The tricky part is that it will not exist during debugtime. Which means you have to be cleaver to debug it and use Disconnected Edit to change Destination tablename to the Temporary table.
Happy hunting, Hanslindgren!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply