copying a table from one database to another "last" doesnt appear the same

In enterprise manager I am copying a table from one database to
another. I am using the dts wizard to import the data. After I
successfully import the data, I open both tables to compare the
records to make sure they are the same. I right click on a field and
click "last" for both tables. However, the record is different for
both. If I do a query the record is still there but they do not show
up in the same order. Why does'nt the import wizard import the
records in the same order? Any help would be greatly appreciated.Rows in a table are not ordered. The Wizard does an INSERT... SELECT from
one table to another and unless there's a clustered index on the table
there's a good chance that the rows will be physically stored in a different
order.

Don't worry about the physical order - just use SELECT... ORDER BY if you
want to see the rows ordered.

--
David Portas
----
Please reply only to the newsgroup
--|||Great! Thank you very much for your help! After submitting this
question I noticed that microsoft mentions this on their site:

"Because SQL Server can use parallel scans to retrieve data, the data
bulk copied from an instance of SQL Server is not guaranteed to be in
any specific order unless you bulk copy from a query and specify an
ORDER BY clause."

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||But note that even if you manage to insert the rows in a specific order
there is no guarantee that they will be sorted in that order when you view
the data. If you require a specific order then you need to use ORDER BY
every time you want to view the data.

--
David Portas
----
Please reply only to the newsgroup
--

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!