copying tables from one server to another

This is a little complicated, so let me explain what I'm after.
My client runs my app against sql server 2000. I can connect via vpn and
tcp/ip. However, I may need to test from time to time against the current
data. I maintain a copy of the database on my system, but the tables are,
by definition, not up to date. I could of course transfer the database to
my server, but this is practically impossible, as the database is over 3
gigs and via tcp/ip it will take 15 hours.
Is there a way that I can copy individual tables to my server, replacing my
old tables, so I can run tests against current data without having to do so
on the active sql server at my client's location?
Thanks for any help.
Bernie YaegerHi,
You could use either one:-
1. BCP OUT the data, copy the file to destination and in destination use
BCP IN to load it into table
2. USE DTS - Export and Import wizard.
I recommend you to
a. BCP OUT the table data from source server
b. copy the file to your test machine
c. BCP IN the data into your machine
Thanks
Hari
MCDBA
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
> This is a little complicated, so let me explain what I'm after.
> My client runs my app against sql server 2000. I can connect via vpn and
> tcp/ip. However, I may need to test from time to time against the current
> data. I maintain a copy of the database on my system, but the tables are,
> by definition, not up to date. I could of course transfer the database to
> my server, but this is practically impossible, as the database is over 3
> gigs and via tcp/ip it will take 15 hours.
> Is there a way that I can copy individual tables to my server, replacing
my
> old tables, so I can run tests against current data without having to do
so
> on the active sql server at my client's location?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Hari,
Tx for your reply. I did try to work with dts, but I got a bit bogged
down - I think I need to understand its features a bit better. But BCP -
good idea! I'll try it.
Thanks,
Bernie
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uynQ5bMdEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> You could use either one:-
> 1. BCP OUT the data, copy the file to destination and in destination use
> BCP IN to load it into table
> 2. USE DTS - Export and Import wizard.
> I recommend you to
> a. BCP OUT the table data from source server
> b. copy the file to your test machine
> c. BCP IN the data into your machine
>
> Thanks
> Hari
> MCDBA
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
> > This is a little complicated, so let me explain what I'm after.
> >
> > My client runs my app against sql server 2000. I can connect via vpn
and
> > tcp/ip. However, I may need to test from time to time against the
current
> > data. I maintain a copy of the database on my system, but the tables
are,
> > by definition, not up to date. I could of course transfer the database
to
> > my server, but this is practically impossible, as the database is over 3
> > gigs and via tcp/ip it will take 15 hours.
> >
> > Is there a way that I can copy individual tables to my server, replacing
> my
> > old tables, so I can run tests against current data without having to do
> so
> > on the active sql server at my client's location?
> >
> > Thanks for any help.
> >
> > Bernie Yaeger
> >
> >
>

copying tables from one server to another

This is a little complicated, so let me explain what I'm after.
My client runs my app against sql server 2000. I can connect via vpn and
tcp/ip. However, I may need to test from time to time against the current
data. I maintain a copy of the database on my system, but the tables are,
by definition, not up to date. I could of course transfer the database to
my server, but this is practically impossible, as the database is over 3
gigs and via tcp/ip it will take 15 hours.
Is there a way that I can copy individual tables to my server, replacing my
old tables, so I can run tests against current data without having to do so
on the active sql server at my client's location?
Thanks for any help.
Bernie YaegerHi,
You could use either one:-
1. BCP OUT the data, copy the file to destination and in destination use
BCP IN to load it into table
2. USE DTS - Export and Import wizard.
I recommend you to
a. BCP OUT the table data from source server
b. copy the file to your test machine
c. BCP IN the data into your machine
Thanks
Hari
MCDBA
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
> This is a little complicated, so let me explain what I'm after.
> My client runs my app against sql server 2000. I can connect via vpn and
> tcp/ip. However, I may need to test from time to time against the current
> data. I maintain a copy of the database on my system, but the tables are,
> by definition, not up to date. I could of course transfer the database to
> my server, but this is practically impossible, as the database is over 3
> gigs and via tcp/ip it will take 15 hours.
> Is there a way that I can copy individual tables to my server, replacing
my
> old tables, so I can run tests against current data without having to do
so
> on the active sql server at my client's location?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Hari,
Tx for your reply. I did try to work with dts, but I got a bit bogged
down - I think I need to understand its features a bit better. But BCP -
good idea! I'll try it.
Thanks,
Bernie
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uynQ5bMdEHA.2408@.tk2msftngp13.phx.gbl...
> Hi,
> You could use either one:-
> 1. BCP OUT the data, copy the file to destination and in destination use
> BCP IN to load it into table
> 2. USE DTS - Export and Import wizard.
> I recommend you to
> a. BCP OUT the table data from source server
> b. copy the file to your test machine
> c. BCP IN the data into your machine
>
> Thanks
> Hari
> MCDBA
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net...
and[vbcol=seagreen]
current[vbcol=seagreen]
are,[vbcol=seagreen]
to[vbcol=seagreen]
> my
> so
>

copying tables from one server to another

This is a little complicated, so let me explain what I'm after.
My client runs my app against sql server 2000. I can connect via vpn and
tcp/ip. However, I may need to test from time to time against the current
data. I maintain a copy of the database on my system, but the tables are,
by definition, not up to date. I could of course transfer the database to
my server, but this is practically impossible, as the database is over 3
gigs and via tcp/ip it will take 15 hours.
Is there a way that I can copy individual tables to my server, replacing my
old tables, so I can run tests against current data without having to do so
on the active sql server at my client's location?
Thanks for any help.
Bernie Yaeger
Hi,
You could use either one:-
1. BCP OUT the data, copy the file to destination and in destination use
BCP IN to load it into table
2. USE DTS - Export and Import wizard.
I recommend you to
a. BCP OUT the table data from source server
b. copy the file to your test machine
c. BCP IN the data into your machine
Thanks
Hari
MCDBA
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net ...
> This is a little complicated, so let me explain what I'm after.
> My client runs my app against sql server 2000. I can connect via vpn and
> tcp/ip. However, I may need to test from time to time against the current
> data. I maintain a copy of the database on my system, but the tables are,
> by definition, not up to date. I could of course transfer the database to
> my server, but this is practically impossible, as the database is over 3
> gigs and via tcp/ip it will take 15 hours.
> Is there a way that I can copy individual tables to my server, replacing
my
> old tables, so I can run tests against current data without having to do
so
> on the active sql server at my client's location?
> Thanks for any help.
> Bernie Yaeger
>
|||Hi Hari,
Tx for your reply. I did try to work with dts, but I got a bit bogged
down - I think I need to understand its features a bit better. But BCP -
good idea! I'll try it.
Thanks,
Bernie
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uynQ5bMdEHA.2408@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi,
> You could use either one:-
> 1. BCP OUT the data, copy the file to destination and in destination use
> BCP IN to load it into table
> 2. USE DTS - Export and Import wizard.
> I recommend you to
> a. BCP OUT the table data from source server
> b. copy the file to your test machine
> c. BCP IN the data into your machine
>
> Thanks
> Hari
> MCDBA
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:V_QNc.9183$09.1208788@.news4.srv.hcvlny.cv.net ...
and[vbcol=seagreen]
current[vbcol=seagreen]
are,[vbcol=seagreen]
to
> my
> so
>

copying Data Base from one sever to another

Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg

copying Data Base from one sever to another

Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. O
n
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy
a
newer one over from the Prod. server. The tape drive on the production serve
r
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod serve
r
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
GregHi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:

> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
On
> the test server I have a duplicate sql 2000 with a copy of the database fr
om
> the production server. Which all are working fine. Both systems have a tap
e
> drive. It's a very old D.B. copy on the test server so I would like to cop
y a
> newer one over from the Prod. server. The tape drive on the production ser
ver
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod ser
ver
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg

copying Data Base from one sever to another

Hi,
Hope I explain this clearly. I'm a newbie to sql. I currently have 2
servers running sql 2000. One is a Production and the other a test server. On
the test server I have a duplicate sql 2000 with a copy of the database from
the production server. Which all are working fine. Both systems have a tape
drive. It's a very old D.B. copy on the test server so I would like to copy a
newer one over from the Prod. server. The tape drive on the production server
is a newer drive than the one on the test server. So I'm unable to restore
from the Prod. to the test server via tape. I would not like to stop sql
running on the prod. server. Is the a way to load the tape in the Prod server
and restore it to the test server? -- I only need the D.B. copyed. I would
not want to detach the D.B.
Greg
Hi
This sounds like an access issue rather than anything else! If you have disk
space on the production server for a backup to disk, you may then be able to
either use a network share/FTP... etc to move it to the production system.
Failing that maybe a removable disk (but then there is a risk when
introducing new hardware!).
HTH
John
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:ECC06AC5-7FB7-423E-A023-71A3F0D8C102@.microsoft.com...
> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server.
> On
> the test server I have a duplicate sql 2000 with a copy of the database
> from
> the production server. Which all are working fine. Both systems have a
> tape
> drive. It's a very old D.B. copy on the test server so I would like to
> copy a
> newer one over from the Prod. server. The tape drive on the production
> server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod
> server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg
|||Depends what you have available.
Can you transfer anything between the prod and test server? If not then
obviously you can't refresh.
If you can transfer data then backup the prod database copy the backup to
the test server (via tape if necessary) and restore it.
If you can't do that much then you probably need to spend some time getting
into a situation where you can.
"Greg" wrote:

> Hi,
> Hope I explain this clearly. I'm a newbie to sql. I currently have 2
> servers running sql 2000. One is a Production and the other a test server. On
> the test server I have a duplicate sql 2000 with a copy of the database from
> the production server. Which all are working fine. Both systems have a tape
> drive. It's a very old D.B. copy on the test server so I would like to copy a
> newer one over from the Prod. server. The tape drive on the production server
> is a newer drive than the one on the test server. So I'm unable to restore
> from the Prod. to the test server via tape. I would not like to stop sql
> running on the prod. server. Is the a way to load the tape in the Prod server
> and restore it to the test server? -- I only need the D.B. copyed. I would
> not want to detach the D.B.
> Greg

Copy rows from a table to a new one using cursor in PL/SQL

Hi there,

can someone explain me how to use a cursor to retrieve all the rows in one table and then insert some of the records into another table? (PL/SQL) Or maybe tell me where I can find a good tutorial or information about how to do it?

Thanx a lot, Fausto

The real problem is the following:

I have a table SPECS with attributes (among others) ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 what means that the number of INP/OUT is restricted to a max of 3.

In order to have as many INP/OUT as needed for each SPEC, I created a new table (SPECS_INFO) with attributes ID (foreign key from table SPECS) , SEQUENCE_NR, INPUT, OUTPUT where I want to copy the information from a row ID, INP1, INP2, INP3, OUT1, OUT2, OUT3 (at the SPECS table) to three rows of the new SPECS_INFO table:

For example:
ID INP1 INP2 INP3 OUT1 OUT2 OUT3
A1 CALL A CALL B CALL C ANSW A ANSW B ANSW C

TO

ID SEQUENCE_NR INPUT OUTPUT
A1 1 CALL A ANSW A
A1 2 CALL B ANSW B
A1 3 CALL C ANSW CTry something like this:

begin
for r in (select * from specs)
loop
insert into specs_info
values (r.id,1,r.inp1,r.out1);
insert into specs_info
values (r.id,2,r.inp2,r.out2);
insert into specs_info
values (r.id,3.r.inp3,r.out3);
end loop;
end;
/

;)|||Thanx a lot. It works fine :-)