copying a table from one database to another "last" doesn't appear the same

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jason
    #1

    copying a table from one database to another "last" doesn't 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.
    Tags: None
    • David Portas
      #2
      Re: copying a table from one database to another "last&quot ; doesn't appear the same

      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
      --


        Comment

        • Bill
          #3
          Re: copying a table from one database to another "last&quot ; doesn't appear the same

          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!

            Comment

            • David Portas
              #4
              Re: copying a table from one database to another "last&quot ; doesn't appear the same

              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
              --


                Comment

                Previous template Next
                Working...