Professional Web Applications Themes

DTS task - Microsoft SQL / MS SQL Server

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...

  1. #1

    Default DTS task

    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 Guest

  2. #2

    Default Re: DTS task

    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.zupanstud-moderna.si> wrote in message
    news:3f054f2enews.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 Guest

  3. #3

    Default Re: DTS task

    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 tion.


    --


    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.zupanstud-moderna.si> wrote in message
    news:3f054f2enews.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 Guest

  4. #4

    Default Re: DTS task

    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("okladisca") = DTSSource("okladisca")

    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" <allanno-spam.sqldts.com> wrote in message
    news:Oz8HqwiQDHA.2432TK2MSFTNGP10.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 tion.
    >
    >
    > --
    >
    >
    > 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.zupanstud-moderna.si> wrote in message
    > news:3f054f2enews.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 Guest

  5. #5

    Default Re: DTS task

    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.zupanstud-moderna.si> wrote in message
    news:3f059763$1news.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("okladisca") = DTSSource("okladisca")
    >
    > 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" <allanno-spam.sqldts.com> wrote in message
    > news:Oz8HqwiQDHA.2432TK2MSFTNGP10.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 tion.
    > >
    > >
    > > --
    > >
    > >
    > > 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.zupanstud-moderna.si> wrote in message
    > > news:3f054f2enews.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 Guest

Similar Threads

  1. There is not enough memory available for this task
    By automationgroup@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 2
    Last Post: May 17th, 08:35 PM
  2. task bar
    By sqr in forum Windows XP/2000/ME
    Replies: 12
    Last Post: August 3rd, 11:28 PM
  3. Cannot End Task w/Task Manager
    By Gloria Mc in forum Windows XP/2000/ME
    Replies: 0
    Last Post: July 10th, 12:20 AM
  4. block "new task" on task manager
    By ab in forum Windows Setup, Administration & Security
    Replies: 1
    Last Post: July 1st, 01:24 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139