Access .adp :How to INSERT all but KEY violations

I am trying to append records from one table to another in a db running on
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.

In a regular Access mdb, an INSERT statement (append query) would do just
that. Of course it warns you of the violation but a DoCmd.SetWarnings FALSE
takes care of that.

Any ideas as to what I need to do to achieve that same thing?For example:

INSERT INTO TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

(where key_col is the primary key).

--
David Portas
SQL Server MVP
--

About join

I have Created two tables to test join key in sql server,but the result is
not what I thougt.Why?Wish all your explains.Thanks.I give all the sql code
in comment.
--The table name is first for tesing fulljoin
/*
create table testfulljoin
(a char(4),
b char(4),
c char(4))
insert testfulljoin(a,b,c)
select 'a','b','c' union all
select 'b','a','d' union all
select 'c','d','e' union all
select 'd','f','g'
*/

/*
create table testfulljoin2
(b char(4),
c char(4),
d char(4))
insert testfulljoin2(b,c,d)
select 'b','c','d' union all
select 'd','e','g' union all
select 'f','d','g' union all
select 'd','e','c'
*/

When testfulljoin2 left join testfulljoin the result is:
a b c d
c d e g
NULLNULLNULLg
c d e c

Why result is not like what I' v aways told:
a b c d
c d e g
null f d g
c d e c

If not why testfulljoin2 right join testfulljoin the result is
a b c d
b a d NULL
c d e g
c d e c
d f g NULLI found the answer myself
Use :

select a.a ,b.* from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;|||

Quote:

Originally Posted by shenliang1985

I found the answer myself
Use :

select a.a ,b.* from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;

Thas good that you found the solution by yourself. Self learning is the best way to learn new things.

MODERATOR

about foreign key conflicts in merge replication

PLS HELP ME. SOS!
Our customer has two fatctories and a marketing center with long distance.
So we have to syncronize data through sql server replication. Because both
publisher and subscriber can modify data, we use merge replication. But when
we tested, foreign key conflicts occured even we only modified data from
subscriber side.
Pls help me, thanks a lot.
Titanicliu,
in merge replication, it can be the case that parent and child generations
are in separate generation batches.
To avoid the error you could set the foreign key to have the NOT FOR
REPLICATION attribute.
You can also increase the -UploadGenerationsPerBatch and
the -DownloadGenerationsPerBatch parameters to their max of 2000 to 'ensure'
the PK and FK rows are processed together.
HTH,
Paul Ibison
|||Hi,Paul
Thanks for your kindly help. I will try it. Thanks again!
BR,
Titanicliu
"Paul Ibison" wrote:

> Titanicliu,
> in merge replication, it can be the case that parent and child generations
> are in separate generation batches.
> To avoid the error you could set the foreign key to have the NOT FOR
> REPLICATION attribute.
> You can also increase the -UploadGenerationsPerBatch and
> the -DownloadGenerationsPerBatch parameters to their max of 2000 to 'ensure'
> the PK and FK rows are processed together.
> HTH,
> Paul Ibison
>
>