Hi,

What's the best way to import data from a csv file into a table in which
some of the rows already exist in the table?

Assume the primary key value is in the csv file. So I'm basically looking
for a "replace" operation on that row.

thanks,
Deac

deacdb,
I would like to suggest you import it to another table and use T-SQL to do
the update/insert.

Ed

"deacdb" wrote:

Without doubt the fastest and easiest way is to import to staging table
first. You can then use TSQL to do the manipulation.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"deacdb" <deacdb2 (AT) hotmail (DOT) com> wrote

Allan and Ed,

Shucks, no shortcuts I guess.

Two questions:
1. To choose rows in the staging table that already exist in the
target table I could use a predicate such as
NOT IN ( SELECT id FROM target_table)
Any other ideas here? Would you go row by row through a cursor?

2. Once I have these rows, I'd have to manually update every column
one by one, yes?
Any suggestions on how?

thanks much,
-deac



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

1. Use NOT EXISTS (usually performs better than NOT IN) or use an outer
join:

INSERT INTO T (...)
SELECT S. ...
FROM S
LEFT JOIN T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

2. UPDATE with a join. Do the UPDATE before the INSERT so that the
target table is smaller and you don't hit the same rows you just
inserted.

--
David Portas
SQL Server MVP
--

Thanks very much for the form of the INSERT using either NOT EXISTS or the
outer join that you demonstrated.

The UPDATE looks harder - I think I'd need to look at one row at a time,
like with a cursor, to see that S.key is present in T and then construct by
hand the UPDATE statement for each column one by one. Is there an easier
way? If not, would you use a cursor or other way to look at each row?

Thanks very much,
Deac



"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote