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

Posted on 2007-10-04
Last Modified: 2013-11-24
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.
      -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

-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?
Question by:netadmin2004
    LVL 1

    Assisted Solution

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

    Accepted Solution

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

    Author Comment

    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.

    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.
    LVL 1

    Expert Comment

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

    Assisted Solution

    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.

    Author Comment

    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.


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Read about achieving the basic levels of HRIS security in the workplace.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now