Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, PIn SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P
In SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

COPY & schema change

Hi,

I need to copy a DB into another changing the schema at the destination DB, I try to do it by exporting the DB and change the schema at the table selection screen but with alot of tables is to complex do it manualy, any idea on how can i do it?.

Thanks.

You have two choices.

a. The way you are finding is "to (sic) complex"

b. Create scripts for the database, and alter the scripts, then run the scripts.

|||As suggested you can take help of generate scripts from SSMS and then use that file by changing relevant schemas then apply it on destination database, if you need data then you can take help of SSIS in this case.|||

Thanks for youy help, I created the new DB changing the schema as you said with SSMS, but I need to take the data into the new DB also but I don't know anything about SSIS that you mention, do you have any documentation or something to help me?.

Thanks again.

|||

To move data between databases/servers, you can use the DTSWizard. (It is a version of the previous Import/Export Wizard.) And does not have the learning curve of SSIS.

Find it at {installedLocation}\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

It should execute from a command prompt.

|||

That's the way I was copying the DB in the beginning but I have to change the schema manually for each table and that's what is very annoying to change a thousand lines, thanks anyway.