Link to home
Start Free TrialLog in
Avatar of netadmin2004
netadmin2004Flag for United States of America

asked on

Need to move data from one table to another newly created table with some different fields easily

I am using SalesLogix (on SQL db ) and am about to push out a new sales engine I created. My only problem now is I have created a new table structure and need to move all the old data into the new tables. Some columns have changed and some data will be merged intio newly created fields. This shouldn't be too hard. I've tried using DTS in enterprise manager however I can't quite figure out how to get it to do what I want. It wants to create brand new tables when all I want to do is copy data to my already created tables. I'd like to do this without having to spend my (like buying scribe). You can see how non-complicated my table srtucture is below:

P.S. SQL statments would take forever if I had to write a statement for each field as there is at least 40 fields in the sales table alone.

Here are the tables I currently have in a sort of tree view based on joins.
'OLD TABLE LAYOUT
-SALE
      -Mailing (there is really only one record per sale) <-- I know it was pointless but...
              -Repeat Mailing
      -Shipping (Same with the mailing - only one shipping record)
               -Tracking Info

'NEW TABLE LAYOUT
-SALE (which includes mailing and shipping now plus some concatination on some fields)
        -Repeat Mailings
        -Tracking Info

So you can see I consolidated the Shipping and Mailing tables into the Sale table. Is there some free utility out there that allows you to simply map fields between two or even the same database?
SOLUTION
Avatar of elec1cele
elec1cele

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of netadmin2004

ASKER

Lowfatspread,
I agree with you, but what I have created is what I was asked to create. I of course made many sugestions, but this is what they need. I've created all the views and tables already and they are working great. The company tested them and this is what they want... Only problem is getting the old data into the new tables. The two tables are different however the new table has many fields that hold the old data and many more new fields.

elec1cele,
Not really sure I'm understanding you though my tables have to be created through SalesLogix as many other entries occur in other tables based on what is created through their own Architect program. They strongly recommend not creating custom tables through any other means.

Again, this shouldn't be tough. I could have an "old sales" section and a "new sles", but that would become confusing to the sales staff. With  out buying Scribe there has to be an easy way odf doing this. I can't imagine I'd be the first.

Basically, if I had Scribe my problem wouldn't be on here, but I can't see paying $4000 for a program when I'm only doing this once.
Avatar of elec1cele
elec1cele

What i was suggesting was using dts to create a temporary set of tables that had the same field structure as the ones created by Saleslogix so then you could do a simple copy of the data after you had it in the correct table structure.  

I am not familar with SalesLogix my suggestion was based on the table info you provided.  If their are other dependent tables other then what you have talked about here it could complicate the copy depending on the relationships.

 If you don't want to create more tables LowFatspreads idea of creating some new views on your old data would work to provide the a way to do a straight copy
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Even easier all I did was copy the info from all three tables into CSV files, make my changes and rename the fields and add the fields that my new tables had then use DTS to import. It was a sinch.

Thanks for the help guys.

-RJ