• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1803
  • Last Modified:

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?
0
netadmin2004
Asked:
netadmin2004
3 Solutions
 
elec1celeCommented:
if you make the dts create tables identical to yours then you should be able to just copy the data straight from one to the other with simple statements like:

insert into YourTable
select * from dtsTable

you will need to make sure you do the tables in the right order so you won't violate any constraints you may have setup
0
 
LowfatspreadCommented:
not sure i understand your problem...

you're already using views to access the data...

just create a view of the old data in the style of the new data (table)

and use that as the source for the update?

I don't in principle agree with your merging of the two tables into the one new...
the whole point surely is to be able to track... the sale and client.. shipping details
which should be stored "once" and referenced many times... (even if you expect to only make single sales).. Short row lengyhs will in general repay you many times over in performance/maintence terms...
0
 
netadmin2004Author Commented:
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
elec1celeCommented:
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
0
 
twintaiCommented:
i too am not familiar with SalesLogix  however if it is any thing like ms sql, the what you need is data transformation. This will transfer data from one table to another in any structure you set it. You can join two columns in source table into one column on the destination table. Just look in the DTS portion.
0
 
netadmin2004Author Commented:
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now