Professional Web Applications Themes

Problem: Copy data from server 1 to server 2 - Microsoft SQL / MS SQL Server

Hi together ! I have a problem: I "just" wan't to copy some records from server 1 to server 2 (same table). But here are the problems: 1. The servers are not connected (can't use linked servers or tion). 2. The target table already contains records, so i only wan't to insert the new records, and update the existing records. 3. I only wan't to copy some fields and not all records from the source table, not all (so BCP isn't really suitable). What is the best way to do that ? Regards Andy...

  1. #1

    Default Problem: Copy data from server 1 to server 2

    Hi together !

    I have a problem:
    I "just" wan't to copy some records from server 1 to
    server 2 (same table).

    But here are the problems:
    1. The servers are not connected (can't use linked servers
    or tion).
    2. The target table already contains records, so i only
    wan't to insert the new records, and update the existing
    records.
    3. I only wan't to copy some fields and not all records
    from the source table, not all (so BCP isn't really
    suitable).

    What is the best way to do that ?

    Regards

    Andy

    Andy Guest

  2. #2

    Default Re: Problem: Copy data from server 1 to server 2

    Andy,

    This sounds like a job for DTS. The servers do not have to be linked since
    DTS runs as a client application. Look into some of DTS's simpler tools and
    this should be fairly straightforward.

    ----

    Regarding point 2: Of course, bcp only inserts data into a target table, so
    you could use it if you had the rows copied from the source server.

    Regarding point 3: If you create a view in your source database that
    defines the rows and columns that you want, you can then use bcp to get that
    data set out of the source server.

    Russell Fields

    "Andy" <infoaxxis24.de> wrote in message
    news:8cd101c3457f$f12ac120$a401280aphx.gbl...
    > Hi together !
    >
    > I have a problem:
    > I "just" wan't to copy some records from server 1 to
    > server 2 (same table).
    >
    > But here are the problems:
    > 1. The servers are not connected (can't use linked servers
    > or tion).
    > 2. The target table already contains records, so i only
    > wan't to insert the new records, and update the existing
    > records.
    > 3. I only wan't to copy some fields and not all records
    > from the source table, not all (so BCP isn't really
    > suitable).
    >
    > What is the best way to do that ?
    >
    > Regards
    >
    > Andy
    >

    Russell Fields Guest

  3. #3

    Default Re: Problem: Copy data from server 1 to server 2

    Russell,

    thank you for your answer.

    But i guess i have still a problem, the 2 servers are at 2
    different locations. There's no connection, so i have to
    send the "result" per mail, the correspondig DBA has
    to "exectue" something. So in my experience, DTS contains
    the "schema" what to do ... but not the data, or i'm
    wrong ?!

    To use a view as source for BCP is of course a good idea,
    but with this solution i can only insert records.
    And how will you check if the record is really new in the
    target table ? BCP just pumps data without checks, or ?!

    What we do now:
    I write

    IF EXISTS (...)
    UPDATE (...)
    ELSE
    INSERT(...)

    statements by myself, but that's only "possible" with less
    data, otherwise it's just to much work !

    Can Enterprise Manager autom. script SQL statements like
    this ? I never heard something like that. Or maybe a 3'
    party tool ?

    Regards,
    Andy
    >-----Original Message-----
    >Andy,
    >
    >This sounds like a job for DTS. The servers do not have
    to be linked since
    >DTS runs as a client application. Look into some of
    DTS's simpler tools and
    >this should be fairly straightforward.
    >
    >----
    >
    >Regarding point 2: Of course, bcp only inserts data into
    a target table, so
    >you could use it if you had the rows copied from the
    source server.
    >
    >Regarding point 3: If you create a view in your source
    database that
    >defines the rows and columns that you want, you can then
    use bcp to get that
    >data set out of the source server.
    >
    >Russell Fields
    >
    >"Andy" <infoaxxis24.de> wrote in message
    >news:8cd101c3457f$f12ac120$a401280aphx.gbl...
    >> Hi together !
    >>
    >> I have a problem:
    >> I "just" wan't to copy some records from server 1 to
    >> server 2 (same table).
    >>
    >> But here are the problems:
    >> 1. The servers are not connected (can't use linked
    servers
    >> or tion).
    >> 2. The target table already contains records, so i only
    >> wan't to insert the new records, and update the existing
    >> records.
    >> 3. I only wan't to copy some fields and not all records
    >> from the source table, not all (so BCP isn't really
    >> suitable).
    >>
    >> What is the best way to do that ?
    >>
    >> Regards
    >>
    >> Andy
    >>
    >
    >
    >.
    >
    Andy Guest

  4. #4

    Default Problem: Copy data from server 1 to server 2

    Hi Andy,

    there's a tool which offers the functioanlity.

    [url]http://www.sqlscripter.com[/url]

    Enjoy.


    >-----Original Message-----
    >Hi together !
    >
    >I have a problem:
    >I "just" wan't to copy some records from server 1 to
    >server 2 (same table).
    >
    >But here are the problems:
    >1. The servers are not connected (can't use linked
    servers
    >or tion).
    >2. The target table already contains records, so i only
    >wan't to insert the new records, and update the existing
    >records.
    >3. I only wan't to copy some fields and not all records
    >from the source table, not all (so BCP isn't really
    >suitable).
    >
    >What is the best way to do that ?
    >
    >Regards
    >
    >Andy
    >
    >.
    >
    SQL Scripter Guest

  5. #5

    Default Re: Problem: Copy data from server 1 to server 2

    Andy,

    Going back to the DTS solution (if you pursue it) you will need two
    packages.

    Package 1: On the source machine extracts from your table to (for example) a
    tab-delimited file (or anything OLE DB can talk to such as an Excell file)

    Email the file

    Package 2: Reads from the emailed file and supports the logic check for
    existence and insert of the row is new.

    Russell Fields

    "Andy" <infoaxxis24.de> wrote in message
    news:042801c3459b$130cf6d0$a501280aphx.gbl...
    > Russell,
    >
    > thank you for your answer.
    >
    > But i guess i have still a problem, the 2 servers are at 2
    > different locations. There's no connection, so i have to
    > send the "result" per mail, the correspondig DBA has
    > to "exectue" something. So in my experience, DTS contains
    > the "schema" what to do ... but not the data, or i'm
    > wrong ?!
    >
    > To use a view as source for BCP is of course a good idea,
    > but with this solution i can only insert records.
    > And how will you check if the record is really new in the
    > target table ? BCP just pumps data without checks, or ?!
    >
    > What we do now:
    > I write
    >
    > IF EXISTS (...)
    > UPDATE (...)
    > ELSE
    > INSERT(...)
    >
    > statements by myself, but that's only "possible" with less
    > data, otherwise it's just to much work !
    >
    > Can Enterprise Manager autom. script SQL statements like
    > this ? I never heard something like that. Or maybe a 3'
    > party tool ?
    >
    > Regards,
    > Andy
    >
    > >-----Original Message-----
    > >Andy,
    > >
    > >This sounds like a job for DTS. The servers do not have
    > to be linked since
    > >DTS runs as a client application. Look into some of
    > DTS's simpler tools and
    > >this should be fairly straightforward.
    > >
    > >----
    > >
    > >Regarding point 2: Of course, bcp only inserts data into
    > a target table, so
    > >you could use it if you had the rows copied from the
    > source server.
    > >
    > >Regarding point 3: If you create a view in your source
    > database that
    > >defines the rows and columns that you want, you can then
    > use bcp to get that
    > >data set out of the source server.
    > >
    > >Russell Fields
    > >
    > >"Andy" <infoaxxis24.de> wrote in message
    > >news:8cd101c3457f$f12ac120$a401280aphx.gbl...
    > >> Hi together !
    > >>
    > >> I have a problem:
    > >> I "just" wan't to copy some records from server 1 to
    > >> server 2 (same table).
    > >>
    > >> But here are the problems:
    > >> 1. The servers are not connected (can't use linked
    > servers
    > >> or tion).
    > >> 2. The target table already contains records, so i only
    > >> wan't to insert the new records, and update the existing
    > >> records.
    > >> 3. I only wan't to copy some fields and not all records
    > >> from the source table, not all (so BCP isn't really
    > >> suitable).
    > >>
    > >> What is the best way to do that ?
    > >>
    > >> Regards
    > >>
    > >> Andy
    > >>
    > >
    > >
    > >.
    > >

    Russell Fields Guest

  6. #6

    Default Re: Problem: Copy data from server 1 to server 2

    Russell,

    thank's again for you advice.

    Meanwhile i tested a recommended tool, and it exactly
    offers what i need :-) Wow !

    ([url]http://www.sqlscripter.com[/url])

    Of course, i think your solution should work too.
    Thanks !

    Bye
    Andy



    >-----Original Message-----
    >Andy,
    >
    >Going back to the DTS solution (if you pursue it) you
    will need two
    >packages.
    >
    >Package 1: On the source machine extracts from your
    table to (for example) a
    >tab-delimited file (or anything OLE DB can talk to such
    as an Excell file)
    >
    >Email the file
    >
    >Package 2: Reads from the emailed file and supports the
    logic check for
    >existence and insert of the row is new.
    >
    >Russell Fields
    >
    >"Andy" <infoaxxis24.de> wrote in message
    >news:042801c3459b$130cf6d0$a501280aphx.gbl...
    >> Russell,
    >>
    >> thank you for your answer.
    >>
    >> But i guess i have still a problem, the 2 servers are
    at 2
    >> different locations. There's no connection, so i have
    to
    >> send the "result" per mail, the correspondig DBA has
    >> to "exectue" something. So in my experience, DTS
    contains
    >> the "schema" what to do ... but not the data, or i'm
    >> wrong ?!
    >>
    >> To use a view as source for BCP is of course a good
    idea,
    >> but with this solution i can only insert records.
    >> And how will you check if the record is really new in
    the
    >> target table ? BCP just pumps data without checks,
    or ?!
    >>
    >> What we do now:
    >> I write
    >>
    >> IF EXISTS (...)
    >> UPDATE (...)
    >> ELSE
    >> INSERT(...)
    >>
    >> statements by myself, but that's only "possible" with
    less
    >> data, otherwise it's just to much work !
    >>
    >> Can Enterprise Manager autom. script SQL statements
    like
    >> this ? I never heard something like that. Or maybe a 3'
    >> party tool ?
    >>
    >> Regards,
    >> Andy
    >>
    >> >-----Original Message-----
    >> >Andy,
    >> >
    >> >This sounds like a job for DTS. The servers do not
    have
    >> to be linked since
    >> >DTS runs as a client application. Look into some of
    >> DTS's simpler tools and
    >> >this should be fairly straightforward.
    >> >
    >> >----
    >> >
    >> >Regarding point 2: Of course, bcp only inserts data
    into
    >> a target table, so
    >> >you could use it if you had the rows copied from the
    >> source server.
    >> >
    >> >Regarding point 3: If you create a view in your
    source
    >> database that
    >> >defines the rows and columns that you want, you can
    then
    >> use bcp to get that
    >> >data set out of the source server.
    >> >
    >> >Russell Fields
    >> >
    >> >"Andy" <infoaxxis24.de> wrote in message
    >> >news:8cd101c3457f$f12ac120$a401280aphx.gbl...
    >> >> Hi together !
    >> >>
    >> >> I have a problem:
    >> >> I "just" wan't to copy some records from server 1 to
    >> >> server 2 (same table).
    >> >>
    >> >> But here are the problems:
    >> >> 1. The servers are not connected (can't use linked
    >> servers
    >> >> or tion).
    >> >> 2. The target table already contains records, so i
    only
    >> >> wan't to insert the new records, and update the
    existing
    >> >> records.
    >> >> 3. I only wan't to copy some fields and not all
    records
    >> >> from the source table, not all (so BCP isn't really
    >> >> suitable).
    >> >>
    >> >> What is the best way to do that ?
    >> >>
    >> >> Regards
    >> >>
    >> >> Andy
    >> >>
    >> >
    >> >
    >> >.
    >> >
    >
    >
    >.
    >
    Andy Guest

Similar Threads

  1. Replies: 0
    Last Post: November 27th, 06:14 PM
  2. Testing Server problem: Server name or address couldnot be resolved!
    By fwilkinson in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: November 9th, 08:03 PM
  3. cfoutputing MYSQL data from server A to server B, is itpossible?
    By cesarmejia in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: July 5th, 04:05 PM
  4. Problem retrieving data from SQL Server
    By Altman in forum ASP.NET Web Services
    Replies: 1
    Last Post: May 2nd, 08:07 PM
  5. Transfering data from one DB server to another DB server using ASP
    By Manohar Kamath [MVP] in forum ASP Database
    Replies: 1
    Last Post: August 1st, 01:24 PM

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