The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
New customizations to DB.. Now need help on SQL on how to move old data to new tables.
Posted: 02 Oct 07 3:34 PM
|
This problem has been solved Thanks for all your help.
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. I am fairly sure I am going to have to write SQL statements for this import (don't have money for Scribe).
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)
-Repeat Mailings
-Tracking Info
So you can see I consolidated the Shipping and Mailing tables into the Sale table. How would I even go about starting to write a script to Copy the data over to my new tables? I have also tried the importer. |
|
|
|
Re: New customizations to DB.. Now need help on SQL on how to move old data to new tables.
Posted: 02 Oct 07 7:51 PM
|
We need a bit more info before we can help much on this.
If you are looking only for a fancy piece of SQL, you'll need to provide table structures, fields etc and intended data movements.
If you are looking for something more, please describe in more detail what you want. Do the data movements need to be synchronised out to remotes?
Phil |
|
|
|
Re: New customizations to DB.. Now need help on SQL on how to move old data to new tables.
Posted: 03 Oct 07 12:31 AM
|
Mario,
It appears that you want to move existing rows from the Mailing and Shipping tables into the Sales table which has been expanded to included the appropriate columns. Does this need to sync?
Sync or not, I believe we can help you with the SQL. Since the Sales records exist in the remote database, so should the Mailing and Shipping records. Because they do, you can issue an update statement that utilizes this assumption to your benefit. Its not a "best practice" to assume that sibling rows exist but since you are rolling out a new feature, it is acceptable to make this one time leap of faith 
Of course, I could be misunderstanding your needs. My apologies if that is the case...
Timmus |
|
|
|
Re: New customizations to DB.. Now need help on SQL on how to move old data to new tables.
Posted: 03 Oct 07 9:43 AM
|
Hi I'm RJ, Mario and I are working on this together. I may have some more helpfull info to try and clear this up.
There will be some merging of certain fields. Some column names have changed and other new colums may be accepting up to 3 fields of data from the old table. Basically we just need an easy way to get started as I have no clue where to begin.
I tried using the Import tool in SLX yesterday and thought I was on the right path though when viewing the import it was trying to import Contacts and Accounts as well.
It seems my best way is to use SQL. |
|
|
| |
| |
|
Re: New customizations to DB.. Now need help on SQL on how to move old data to new tables.
Posted: 03 Oct 07 10:51 AM
|
Without remotes, the version of SLX becomes a moot point .
Here is an example of moving the Account field from the Account into its children Contact records. Hopefully the syntax example will help you figure out the rest.
UPDATE
C
SET
C.Account = A.Account
FROM
Contact C
INNER JOIN Account A ON A.AccountID = C.AccountID
For concatenating fields you will want to use + and Coalesce. Use Coalesce because Anything + NULL = NULL. I would also Trim the new values to remove leading and trailing spaces. For example:
UPDATE
T1
SET
T1.TargetColumn = LTRIM(RTRIM(LEFT(Coalesce(T2.SourceColumn1,'') + ' ' + Coalesce(T2.SourceColumn2,''), [Length of Target Column])))
FROM
TargetTable T1
INNER JOIN SourceTable T2 ON T2.ID = T1.ID
I hope this helps!
Timmus |
|
|
| |
|
Re: New customizations to DB.. Now need help on SQL on how to move old data to new tables.
Posted: 09 Oct 07 9:29 AM
|
If you can afford it, I would highly recommend Scribe Insight. I know it's a decent chunk of money but it's well worth it.
We initially got it for data migration but are now using it multiple integration processes between our e-commerce system/ERP system/website and of course SLX. It's great to know it's handling all of the data transactions, allowing me to get on with better things. |
|
|
|
Re: New customizations to DB.. Now need help on SQL on how to move old data to new tables.
Posted: 10 Oct 07 2:53 PM
|
| In the past I have made DTS (Data Transformation Services) packages to move the data between old and new tables. They give you a little more flexibility if the data needs to be manipulated in some way. Also you can create a lookup to call the Saleslogix stored procedure to create new ID's for your tables. If you are using SQL 2005 the name DTS changed to Integration Services. |
|
|
| |
|