Copy table between SQL servers without logging

Does anyone have a non-DTS method for copying large tables between SQL
servers without logging? bcp and BULK INSERT appear to be for files only.
Is there a way to use them with other SQL tables?Mark
DECLARE @.tablename AS SYSNAME
SET @.tablename = 'pubs..titles'
EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
@.tablename + ''') AS T')
Note: I assume I have created linked server , may I ask you why not using
DTS?
Also ,this appoach will fail if you have already test table ,what's more it
doesnt transfer pk to the new table.
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||If the destination table doesn't have to already exist, you can use SELECT *
INTO new_table_name to minimize, but not entirely eliminate, logging.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi,
Make the recovery model for the database as "Simple". After that create a
linked server to connect to remote server.
1. Create the table strucute
2. Use insert into select * from server.db.dbo.tablename
Thanks
Hari
MCDBA
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vtecr7qe5d1o7c@.corp.supernews.com...
> Does anyone have a non-DTS method for copying large tables between SQL
> servers without logging? bcp and BULK INSERT appear to be for files only.
> Is there a way to use them with other SQL tables?
>|||Hi Aaron,
I feel we cannot use the command "select * into server.dbname.dbo.temptable
from table " between servers.
Thanks
Hari
MCDBA
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uW3NpDzvDHA.3140@.TK2MSFTNGP11.phx.gbl...
> If the destination table doesn't have to already exist, you can use SELECT
*
> INTO new_table_name to minimize, but not entirely eliminate, logging.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Uri,
Thanks for your reply. I am not using DTS because the tables being copied
may change, I am using a cursor to loop through the tables to copy, then
executing code within the cursor to copy the data. I am running two
parallel cursors to maximize transfer speed. My issue is that the log file
is growing to 4+Gig during the copy operation on a 56Gig database. We can
not just backup and restore or copy the physical files in our case. As far
as keys, indexes, etc. they are all scripted and added at the appropriate
times. Database recovery model is Simple.
Previously, I have tried breaking up the copy into chunks and performing a
BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
that was incredibly slower. However, it did use minimal log space.
Obviously, disk space concerns are driving this effort. It's unnecessary,
in our situation, to log the data, why do it? I feel like I am forced to do
so.
Mark
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Mark
> DECLARE @.tablename AS SYSNAME
> SET @.tablename = 'pubs..titles'
> EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM ' +
> @.tablename + ''') AS T')
> Note: I assume I have created linked server , may I ask you why not using
> DTS?
> Also ,this appoach will fail if you have already test table ,what's more
it
> doesnt transfer pk to the new table.
>
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vtecr7qe5d1o7c@.corp.supernews.com...
> > Does anyone have a non-DTS method for copying large tables between SQL
> > servers without logging? bcp and BULK INSERT appear to be for files
only.
> > Is there a way to use them with other SQL tables?
> >
> >
>|||Mark
Well ,perhaps you need to use Replications , did you think about it?
"Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
news:vteg11mhsao054@.corp.supernews.com...
> Uri,
> Thanks for your reply. I am not using DTS because the tables being copied
> may change, I am using a cursor to loop through the tables to copy, then
> executing code within the cursor to copy the data. I am running two
> parallel cursors to maximize transfer speed. My issue is that the log
file
> is growing to 4+Gig during the copy operation on a 56Gig database. We can
> not just backup and restore or copy the physical files in our case. As
far
> as keys, indexes, etc. they are all scripted and added at the appropriate
> times. Database recovery model is Simple.
> Previously, I have tried breaking up the copy into chunks and performing a
> BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> that was incredibly slower. However, it did use minimal log space.
> Obviously, disk space concerns are driving this effort. It's unnecessary,
> in our situation, to log the data, why do it? I feel like I am forced to
do
> so.
> Mark
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > Mark
> > DECLARE @.tablename AS SYSNAME
> > SET @.tablename = 'pubs..titles'
> >
> > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM '
+
> > @.tablename + ''') AS T')
> >
> > Note: I assume I have created linked server , may I ask you why not
using
> > DTS?
> > Also ,this appoach will fail if you have already test table ,what's more
> it
> > doesnt transfer pk to the new table.
> >
> >
> >
> >
> > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > Does anyone have a non-DTS method for copying large tables between SQL
> > > servers without logging? bcp and BULK INSERT appear to be for files
> only.
> > > Is there a way to use them with other SQL tables?
> > >
> > >
> >
> >
>|||Yes... and then again, a lot of overhead and additional database limitations
to perform a simple copy. I wish I just had the cash for larger drives.
Thanks for your comments.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#RjlOczvDHA.2712@.TK2MSFTNGP11.phx.gbl...
> Mark
> Well ,perhaps you need to use Replications , did you think about it?
>
> "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> news:vteg11mhsao054@.corp.supernews.com...
> > Uri,
> >
> > Thanks for your reply. I am not using DTS because the tables being
copied
> > may change, I am using a cursor to loop through the tables to copy, then
> > executing code within the cursor to copy the data. I am running two
> > parallel cursors to maximize transfer speed. My issue is that the log
> file
> > is growing to 4+Gig during the copy operation on a 56Gig database. We
can
> > not just backup and restore or copy the physical files in our case. As
> far
> > as keys, indexes, etc. they are all scripted and added at the
appropriate
> > times. Database recovery model is Simple.
> >
> > Previously, I have tried breaking up the copy into chunks and performing
a
> > BACKUP LOG WITH TRUNCATE ONLY between chunks, but with all the overhead,
> > that was incredibly slower. However, it did use minimal log space.
> >
> > Obviously, disk space concerns are driving this effort. It's
unnecessary,
> > in our situation, to log the data, why do it? I feel like I am forced
to
> do
> > so.
> >
> > Mark
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:#U0usDzvDHA.1996@.TK2MSFTNGP12.phx.gbl...
> > > Mark
> > > DECLARE @.tablename AS SYSNAME
> > > SET @.tablename = 'pubs..titles'
> > >
> > > EXECUTE ('SELECT * INTO TEST FROM OPENQUERY([server], ''SELECT * FROM
'
> +
> > > @.tablename + ''') AS T')
> > >
> > > Note: I assume I have created linked server , may I ask you why not
> using
> > > DTS?
> > > Also ,this appoach will fail if you have already test table ,what's
more
> > it
> > > doesnt transfer pk to the new table.
> > >
> > >
> > >
> > >
> > > "Mark Nelson" <mnelson@.pomcoplus.com> wrote in message
> > > news:vtecr7qe5d1o7c@.corp.supernews.com...
> > > > Does anyone have a non-DTS method for copying large tables between
SQL
> > > > servers without logging? bcp and BULK INSERT appear to be for files
> > only.
> > > > Is there a way to use them with other SQL tables?
> > > >
> > > >
> > >
> > >
> >
> >
>|||Select * into [New Table name]
With the new name being fully qualified
Will
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!