Hello All,
I have my database in SQL 2000. I managed to import a csv
file and save the DTS to create a staging table (as
described by Allan many times)and now I am ready to
delete,update and insert data in my final table (in the
same server). I keep reading about this and I think I
need to use TSQL to do it. I just have no idea how to
work it out. All links, articles and books that I found
about it are quite cryptic and brief and basically just
tell me to do a UID using TSQL with no further examples.
Can anyone please explain the process to me like I was
a "6 year old"? or at least send me a link with a better
explanation than the one in the books online?
Thanks a lot.
Gustavo

Ok so we have our data in a staging table and we have our real table

We now need to know

What to UPDATE
What to DELETE
What to INSERT


UPDATE = all raows that match on key values between the staging area and the
real table
DELETE = Where there are key rows in the real table that are not in the
staging table
INSERT = Key rows in the Staging atble that are not in the real table


You can do this in *3 ExecuteSQL tasks.





--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Gustavo" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks for the response Allan,
I think your response was a bit short or I am just very
ignorant about SQL features.
I looked around to try to find out by "*3 ExecuteSQL
tasks" and if I did this correctly I found this feature
in the DTS packages.
I tried to build an update query there but it won't let
me add another table to it. I need to:

a)Update the values with the same 4 key fields in my real
table with the values from my staging table
b)insert the new records from my staging table to the
real table.
c)Actually I do not want to delete any record from my
real table because I need to keep historical data and my
staging table is going to drop old records periodically.

Do I need to manually write the queries to do this or is
there another way to do it?
Thanks again
Gustavo

You are nearly there.

I would write the queries myself so

UPDATE RealTable
SET........................................
FROM RealTable JOIN StagingTable
ON................................................ ..

Your Inserts would be

INSERT INTO RealTable(.........)
SELECT .................................. FROM StagingTable LEFT OUTER JOIN
RealTable ON............................
WHERE <REalTable key cols > IS NULL


Make more sense.?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Gustavo" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks a lot Allan!!
You did it again
Gustavo