Inserting Duplicate Data

[John Steen]

This is probably a silly question to most of you, but I'm in the process
of splitting off years from a large DB to several smaller ones. Some of
the existing smaller DBs already have most of the data for their
respective years. But some of the same data is also on the source DB.

If I simply do an insert keying on the year column, and a row being
inserted from the source DB already exists in the target DB, will a
duplicate row be created?

And if so, how can I avoid that?

Thanks,
John Steen



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Lyndon Hills]

Re: Inserting Duplicate Data

On 01 Oct 2003 18:51:51 GMT, John Steen <moderndads@hot mail.com>
wrote:
[color=blue]
>This is probably a silly question to most of you, but I'm in the process
>of splitting off years from a large DB to several smaller ones. Some of
>the existing smaller DBs already have most of the data for their
>respective years. But some of the same data is also on the source DB.
>
>If I simply do an insert keying on the year column, and a row being
>inserted from the source DB already exists in the target DB, will a
>duplicate row be created?
>
>And if so, how can I avoid that?
>
>Thanks,
>John Steen
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***[/color]
It depends what constraints exist on the inserted table. If for
example there is a unique key then you will not be able to insert a
row with the same key. Unfortunately a simple insert will not just
insert the rows that are not duplicates, the whole insert will fail.

If there are no constraints then duplicates will be created.

One approach would be to insert all the rows to a temporary table.
Then delete the rows that exist in the second table and finally insert
all the remaining rows from temp to table two. This gives you a half
way stage to check your work.

Otherwise you simply put an AND clause on your initial select that
excludes rows that would be duplicated. You need to understand the
data in order to be able to identify what constitutes a duplicate.

I don't think there's much more to say without knowing the tables and
the data.[color=blue]
>Don't just participate in USENET...get rewarded for it![/color]

[Erland Sommarskog]

Re: Inserting Duplicate Data

John Steen (moderndads@hot mail.com) writes:[color=blue]
> If I simply do an insert keying on the year column, and a row being
> inserted from the source DB already exists in the target DB, will a
> duplicate row be created?[/color]

Not if the database is properly set up with a primary key constraint.
[color=blue]
> And if so, how can I avoid that?[/color]

Whatever, you need to do something like:

INSERT tbl (....)
SELECT ...
FROM src
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.keycol = src.keycol)

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp