April 10, 2003 at 11:39 am
I have the need to import data from an external source which is in the form a of a comma delimited file. I don't need all the fields in the imported text file and I only want to update records in my current database table that are different than the source file (text file) provides. For example if this were an employee table and the emp# was the primary key but the employee phone number changed, I would need this record to be updated or if a new employee were added. These imports need to be done weekly.
1. Is a DTS package the correct tool?
2. Do I import the data into a different table and compare the two?
3. How many steps and in which order do I need to import the data.
Thanks i'm new to this game!
-GA
April 10, 2003 at 2:40 pm
-Create a temp table that matches your text file structure
-Import the data in from the text file into the temp table
-Delete from the main table where the records exist in the temp table
-Insert all records from the temp table into the main table
-Clear temp table (truncate table statment)
Note, this is assuming that you would always consider the data coming in from the text file to be either as good or better than the data in the main table. If so, this process will be quick and consistent.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 10, 2003 at 2:57 pm
It is good idea to create the Temp table and Drop at the end of the process.
--Create the temp table.
--Populate the data to the temp table.
--Exec The UPdate Statement
--Exec the Insert stmt.
--Drop the Tmp table.
Update Employee
Set
Phone_No = T.Phone_No
From Employee E
Inner Join Tmp_Employee T On
E.Emp_Id = T.Emp_Id
Insert into Employee
(
field....
)
Select
Columns...
From Tmp_Employee T
Where Not Exists (Select * from Employee E Where
E.Emp_Id = T.Emp_Id)
If It helps,Let me know.
April 11, 2003 at 3:46 pm
Hi,
Use lookups to check if the record exist skip if not import into the table.
Don't need temp tables.
Example
http://www.sqldts.com/default.aspx?6,107,277,7,1
JFB
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply