How To Copy Tables Across Servers?

Hi,
We got a production database that gets
tables added to it daily.
So, every single day we transfer those new
tables via DTS to five (5) other servers!
Can we accomplish the same thing using a
'Select' statement?
I found no way to use the 'select' stmt
across servers.
I'm not conversant with TSQL (yet!) and
would very much appreciate any help you can
give me.
Thank you very much
mike
Mike
*** Sent via Developersdex http://www.codecomments.com ***
"mike" <-nospam@.yahoo.com> wrote in message
news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
Did I understand you correctly? Your database schema changes DAILY? I know
nothing of your application obviously but new tables daily sounds like you
have some major issues that ought to be better fixed by a different
architecture or a better change control process.
The best way to replicate schema changes is to script them and then apply
the scripts. Or invest in a tool like RedGate SQL Compare
(http://www.red-gate.com/). Don't you do that anyway in order to test the
changes?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I agree with David - that many new tables every day seems like a lot.
If you're just doing data transfer across servers, and need no schema
changes, try checking out Linked Servers. You can link serverA to
serverB and then do cross-server queries, such as ....
INSERT TableA
SELECT *
FROM Server2.dbo.TableA
(I think this is correct).
David Portas wrote:
> "mike" <-nospam@.yahoo.com> wrote in message
> news:eDQGbbiQGHA.1728@.TK2MSFTNGP11.phx.gbl...
>
> Did I understand you correctly? Your database schema changes DAILY? I know
> nothing of your application obviously but new tables daily sounds like you
> have some major issues that ought to be better fixed by a different
> architecture or a better change control process.
> The best way to replicate schema changes is to script them and then apply
> the scripts. Or invest in a tool like RedGate SQL Compare
> (http://www.red-gate.com/). Don't you do that anyway in order to test the
> changes?
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
|||Again, it sounds like you have bigger issues.. But if you must do this, try
the following approach:
1. link your servers.
2. run some script like this to determine new tables
select * from sysobjects where type = 'u' and datediff(d, crdate, getdate())
< 1
3. then grab the new tables and run something like this.
select * into server2.dbname.dbo.newtable1 from server1.dbname.dbo.newtable1
You could wrap that all up in a sp or something and automate it.. Obviously
it needs a bit of refinement
"CoreyB" wrote:

> I agree with David - that many new tables every day seems like a lot.
> If you're just doing data transfer across servers, and need no schema
> changes, try checking out Linked Servers. You can link serverA to
> serverB and then do cross-server queries, such as ....
> INSERT TableA
> SELECT *
> FROM Server2.dbo.TableA
> (I think this is correct).
>
> David Portas wrote:
>
|||Hi Mike,
What are the other 5 servers used for? Would Log Shipping be an option for
you as this also handles schema changes.
Adam J Warne, MCDBA
"mike" wrote:

> Hi,
> We got a production database that gets
> tables added to it daily.
> So, every single day we transfer those new
> tables via DTS to five (5) other servers!
> Can we accomplish the same thing using a
> 'Select' statement?
> I found no way to use the 'select' stmt
> across servers.
> I'm not conversant with TSQL (yet!) and
> would very much appreciate any help you can
> give me.
> Thank you very much
> mike
>
> Mike
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||David, There is no schema changes. The tables added are of the same
structure as the previous type tables.
Adding those tables is , unfortunately, is a requirement of an
application.
The other servers to where I need to copy to (daily); represent a
replica of the original server. They are used by other entities.
Each server got two databases. One database is being replicated to the
other 5 servers.
Could not find a way to replicate the second database since new tables
are added daily.
Thank you
Mike
*** Sent via Developersdex http://www.codecomments.com ***
|||Thank you very much Corey.....LInking servers to transfer tables across
servers would resolve this situation...as long as the current date is
always accessible in TSQL!
We are using SQL 2000 on Windows03 platform.
T
Mike
*** Sent via Developersdex http://www.codecomments.com ***