i have an existing sql database with data and i'm trying to migrate the
data to another empty sql database. our software's been updated and the
new database's schema's been changed. i want our existing data inserted
into the new database. i've tried to use dts but am having problems.

first try worked but it copied the objects...meaning the old table
structure and data. not good.

i then chose to use a query as the source. the problem is it seems to
want to make the destination a new table called 'results'. it's giving
an error says 'column names in each table must be unique'. my source
query selects data from several dozen tables. because of foreign keys,
etc the same field id is used multiple times. but why is it making one
destination table?

how do i make sql server select data from one database and insert it
into the exact same table.field in the new database? ...without
altering the structure of the new database.

i'm a complete newbie so i'm sure i'm doing something wrong. i've
looked at the 'transformation' tab but all i see are fields. i don't
see where you define the source and destination tables. the only option
button available is 'create destination table', the 'append rows to
destination table' is disabled. plus, why is it singular? i want data
from multiple tables to be inserted into multiple tables. is this
possible?

hope that makes sense. thanks in advance.
mark

Where it says "Results" should be a drop down box that you can select the
table you want to insert into.

If you want a quick tutorial on DTS or just DTS information in general goto:
www.sqldts.com
The tutorial you probably want to start with is:
http://www.sqldts.com/default.aspx?278

Ryan S.
DBA/Programmer

"mark_s_nospam (AT) ev1 (DOT) net" wrote:

thanks ryan, i'll look at the link you included.

i saw the pulldown but my select query pulls from multiple tables. i'd
like to insert the data into the same (multiple) tables in the new
database. i can't just select 1 destination table. does this mean i
have to split up the job into one query per table so i can choose the
proper destination? i have dozens of tables to migrate. what a mess.

The wizard will create all of these for you. But be very careful, and read
all of what it is saying it is doing, as it can replace things and copy much
more than you want if you have the wrong settings.

If you haven't used it:
Open your database in Enterprise manager goto Tools, Data Transformation
Services, Import Data.

Follow the wizard. Make sure and select copy tables/views. The copy
objects get really specific and if you are this new, you could copy over
things you don't want to.

Select the source tables and then use the drop down boxes to select the
appropriate destination tables.

If you click on the Transform you can select drop down boxes of the source
fields and destination fields that you want to copy.

Hope this helps
Ryan S.
DBA/Programmer

"mark_s_nospam (AT) ev1 (DOT) net" wrote:

thanks. i'll try it again. i tried that (copy objects) first but must
have had some incorrect options selected. that's why i moved on to
trying it from a query.

to confirm, it IS(???) possible to copy ONLY the data from one database
to another using this method? without altering the destination design?

what if there are some fields in the old database that don't exist in
the new one? over time we had modified the 'out of the box' design. we
don't use most of it anymore. i'd like this update on the new database
to get back to an 'out of the box' state. basically, i only want the
data copied where both tables and fields match. i don't want dts to
create/modify anything. just move the data where the tables and field
names match.

thanks again for your help.
mark

It is possible. Just use the copy Tables/Views and select an existing table
to place the data into. For granular control, go into the "Transform" and
select which source field is associated with which destination field.

Ryan S.
DBA/Programmer

"mark_s_nospam (AT) ev1 (DOT) net" wrote:

it worked! thanks.

i never tried the 'copy table' option because i only wanted 'data'
which was called out in the 3rd option. only tried it (copy
object/data) and 'use a query'. not very user friendly
text/descriptions for us newbies. wonder why the copy objects/data
doesn't have the same transform options that the copy tables selection
does.

thanks again!

last question... just tried another test and failed. because of foreign
keys, etc i need to set the order in which tables are copied first. it
seems to be doing it alphabetically(?). is there a place for me to
define this? or do i have to create separate packages? ... first
containing all tables without validating tables, etc. thanks again. m