DTS task

[Simon]

I create DTS task to copy data from one table from one database to another
table in other database every day.

But for the data, which was copied in the past days, it create error:
Violation of primary key. Cannot insert duplicate key in object 'table2'.
Is it possible to update old data and copy only the new one. I didn't found
this option anywhere.

Does anybody know how to do that?

Thank you,
Simon

[SriSamp]

You can probably write an ActiveX script task that uses a lookup query task
to check for the existence of the record and if so, fail the insert and
continue with the next record.
--
HTH,
SriSamp
Please reply to the whole group only!
[url]http://www32.brinkster.com/srisamp[/url]

"Simon" <simon.zupan@stud-moderna.si> wrote in message
news:3f054f2e@news.s5.net...

> I create DTS task to copy data from one table from one database to another
> table in other database every day.
>
> But for the data, which was copied in the past days, it create error:
> Violation of primary key. Cannot insert duplicate key in object 'table2'.
> Is it possible to update old data and copy only the new one. I didn't

found

> this option anywhere.
>
> Does anybody know how to do that?
>
> Thank you,
> Simon
>
>

[Allan Mitchell]

The way I would do it is this

Pump the Data into the database with the table that you are going to change.


You need to plan for 3 event

1. New Data
2. Updated Data
3. Deleted data


1. Do a match on the primary key. If the record is in the table you just
pumped into but not the Destination then issue an INSERT
2. Do a match on the primary keys. If the record is in the table you just
pumped into and in the destination then issue an UPDATE
3. Do a match on the Primary Keys. If the record is in the destination but
not in the table you just pumped into then issue a DELETE

An easier way of doing this might be to set up replication.


--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
[url]www.SQLDTS.com[/url]
I support PASS - the definitive, global community
for SQL Server professionals - [url]http://www.sqlpass.org[/url]

"Simon" <simon.zupan@stud-moderna.si> wrote in message
news:3f054f2e@news.s5.net...

> I create DTS task to copy data from one table from one database to another
> table in other database every day.
>
> But for the data, which was copied in the past days, it create error:
> Violation of primary key. Cannot insert duplicate key in object 'table2'.
> Is it possible to update old data and copy only the new one. I didn't

found

> this option anywhere.
>
> Does anybody know how to do that?
>
> Thank you,
> Simon
>
>

[Simon]

I know how to create a match on PK AND insert only the new rows:

Function Main()

Dim strExists

'I check, if this row exists in my destination table

strExists = DTSLookups("PreveriZapis").Execute
(DTSSource("idDrzave"),DTSSource("idSkladisca"))

If (strExists <> "") Then

Main = DTSTransformStat_SkipRow

Else

DTSDestination("idDrzave") = DTSSource("idDrzave")

DTSDestination("idSkladisca") = DTSSource("idSkladisca")

DTSDestination("nazivSkladisca") = DTSSource("nazivSkladisca")

DTSDestination("opisSkladisca") = DTSSource("opisSkladisca")

DTSDestination("namenSkladisca") = 2

Main = DTSTransformStat_OK

End If

End Function

But I don't know how can I include the DELETE or UPDATE?

Do you have some example?

Thank you,

Simon

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:Oz8HqwiQDHA.2432@TK2MSFTNGP10.phx.gbl...

> The way I would do it is this
>
> Pump the Data into the database with the table that you are going to

change.

>
>
> You need to plan for 3 event
>
> 1. New Data
> 2. Updated Data
> 3. Deleted data
>
>
> 1. Do a match on the primary key. If the record is in the table you just
> pumped into but not the Destination then issue an INSERT
> 2. Do a match on the primary keys. If the record is in the table you

just

> pumped into and in the destination then issue an UPDATE
> 3. Do a match on the Primary Keys. If the record is in the destination

but

> not in the table you just pumped into then issue a DELETE
>
> An easier way of doing this might be to set up replication.
>
>
> --
>
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> [url]www.SQLDTS.com[/url]
> I support PASS - the definitive, global community
> for SQL Server professionals - [url]http://www.sqlpass.org[/url]
>
> "Simon" <simon.zupan@stud-moderna.si> wrote in message
> news:3f054f2e@news.s5.net...

> > I create DTS task to copy data from one table from one database to

another

> > table in other database every day.
> >
> > But for the data, which was copied in the past days, it create error:
> > Violation of primary key. Cannot insert duplicate key in object

'table2'.

> > Is it possible to update old data and copy only the new one. I didn't

> found

> > this option anywhere.
> >
> > Does anybody know how to do that?
> >
> > Thank you,
> > Simon
> >
> >

>
>

[SriSamp]

To include DELETE and UPDATE, you do not have a direct option in the
transform script, since it does only inserts. To have finer control over the
whole process, I recommend that you look at the Data Driven Query Task, that
lets you call procedures/T-SQL statements based on what your ActiveX script
returns as values.
--
HTH,
SriSamp
Please reply to the whole group only!
[url]http://www32.brinkster.com/srisamp[/url]

"Simon" <simon.zupan@stud-moderna.si> wrote in message
news:3f059763$1@news.s5.net...

> I know how to create a match on PK AND insert only the new rows:
>
> Function Main()
>
> Dim strExists
>
> 'I check, if this row exists in my destination table
>
> strExists = DTSLookups("PreveriZapis").Execute
> (DTSSource("idDrzave"),DTSSource("idSkladisca"))
>
> If (strExists <> "") Then
>
> Main = DTSTransformStat_SkipRow
>
> Else
>
> DTSDestination("idDrzave") = DTSSource("idDrzave")
>
> DTSDestination("idSkladisca") = DTSSource("idSkladisca")
>
> DTSDestination("nazivSkladisca") = DTSSource("nazivSkladisca")
>
> DTSDestination("opisSkladisca") = DTSSource("opisSkladisca")
>
> DTSDestination("namenSkladisca") = 2
>
> Main = DTSTransformStat_OK
>
> End If
>
> End Function
>
> But I don't know how can I include the DELETE or UPDATE?
>
> Do you have some example?
>
> Thank you,
>
> Simon
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:Oz8HqwiQDHA.2432@TK2MSFTNGP10.phx.gbl...

> > The way I would do it is this
> >
> > Pump the Data into the database with the table that you are going to

> change.

> >
> >
> > You need to plan for 3 event
> >
> > 1. New Data
> > 2. Updated Data
> > 3. Deleted data
> >
> >
> > 1. Do a match on the primary key. If the record is in the table you

just

> > pumped into but not the Destination then issue an INSERT
> > 2. Do a match on the primary keys. If the record is in the table you

> just

> > pumped into and in the destination then issue an UPDATE
> > 3. Do a match on the Primary Keys. If the record is in the destination

> but

> > not in the table you just pumped into then issue a DELETE
> >
> > An easier way of doing this might be to set up replication.
> >
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > [url]www.SQLDTS.com[/url]
> > I support PASS - the definitive, global community
> > for SQL Server professionals - [url]http://www.sqlpass.org[/url]
> >
> > "Simon" <simon.zupan@stud-moderna.si> wrote in message
> > news:3f054f2e@news.s5.net...

> > > I create DTS task to copy data from one table from one database to

> another

> > > table in other database every day.
> > >
> > > But for the data, which was copied in the past days, it create error:
> > > Violation of primary key. Cannot insert duplicate key in object

> 'table2'.

> > > Is it possible to update old data and copy only the new one. I didn't

> > found

> > > this option anywhere.
> > >
> > > Does anybody know how to do that?
> > >
> > > Thank you,
> > > Simon
> > >
> > >

> >
> >

>
>