Automated Dataimport

Hi everyone,
i'm new to ado.net and need some help. My problem:
got one DB with real bad tablestructure
and one new DB (structured!)
i need do do an automated import from the old to the new DB, with check if the data row is new or updated.
I try to do it this way-> 2 datasets merged together ->Problem: new entries in destination dataset have row state unchanged
other way i tryed add rows from source to destination with add funktion -> problem no check if data exist and maybe is changed
So how can i get the merge working or how can i implemt an effective funktion to solve this problem.
Thanks in advance!

Is this something you need to do just once, or on an ongoing basis?
|||

I have to do it aprox. once per week.
My idea is to write my own merge function. But if there is a faster and simplier way please show me!
Thx!

|||I'd do this completely in SQL Server, with a stored procedure, and use SQL Server Agent to schedule it to run once/week.
The query in the stored procedure would look something like this:
IF NOT EXISTS(SELECT * FROM NewTable WHERE NewTable.Key = OldTable.Key)
BEGIN
INSERT INTO
NewTable
(
Column1,
Column2,
Column3
)
SELECT
Column1,
Column2,
Column3
FROM
OldTable
END
ELSE
UPDATE
NewTable
SET
NewTable.Column1 = OldTable.Column1,
NewTable.Column2 = OldTable.Column2,
NewTable.Column3 = OldTable.Column3,
NewTable.DateModified = GETDATE()
FROM
NewTable
INNER JOIN
OldTable ON NewTable.Key = OldTable.Key
WHERE
NewTable.Column1 <> OldTable.Column1 OR
NewTable.Column2 <> OldTable.Column2 OR
NewTable.Column3 <> OldTable.Column3



|||Thx for your help,
i think thats the better solution. Last Question i have, what is the code to call an other Database on the same Server with Stored Procedures.
|||

Iso wrote:

what is the code to call an other Database on the same Server with Stored Procedures.

Use the 3-part qualified name: database.owner.table. For example:
yourDatabase.dbo.OldTable