February 24, 2003 at 5:30 am
Hi,
I have to migrate a MySQL DB to SQL Server 2k. Whilst this is a boring and thankless task, it is also pretty error-free. What makes the job difficult is the inability to DTS from MySQL to SQL Server (paranoid DB hosting company refusing to give a login sufficient access to read the schema and such), oh, and the SET column defintion.
Here's an example colum definition:
genres_musicaux set('Dance','Techno','HipHop','Pop','House','Rock','Métal','Classique','Autre') NOT NULL default '',
This column defintion allows 0+ of the above set to be chosen and set. In MySQL internals, these values are stored as bits (1,2,4,8,16,32,64....) and the bitwise pattern is stored in the DB. Output of the MySQL column generates a comma-seperated string of the values (i.e. 'Techno,Pop,House').
How do I convert this to SQL Server? Is there a direct translation that I haven't come across before? If not, then I can think of 4 methods...
1. Convert the column to [n]text, store the comma-seperated string. Problem: cannot be searched or indexed.
2. Convert column to [n]varchar(), store the comma-seperated string. Problem: there are many of these columns and may well overrun SQL Server's maximum row size.
3. Create a sub-table of the values in the SET column definition (with IDs of 1,2,4,8...). For each of the rows in the MySQL table run a query against the values, return the compound values and store the bitwise pattern in the SQL Server table. Problems: Highly complex SQL for each and every query (either select or update/insert) to translate column value 4434 into the correct strings (for instance) and vice-versa.
4. Create a table to hold all the values in the set, and another table to hold the selected values per parent row. Problem: Not as complex as above (3) - but still needs all the scripted INSERT statements (30,000+) to be analysed and modified in T-SQL with complex string manipulation etc.
Anybody ever had a similar problem before, and can point me in the right direction?
Please?
February 24, 2003 at 6:46 am
In the conversion of a SET column, simply do a SELECT Setfield+0 to get the actual lookup value (INT) returned.
February 24, 2003 at 7:09 am
Thanks jpipes - that can sort out a lot of nasty code working out which values are already entered in each row.
Unfortunately I have been given a phpmyadmin table export, with the comma-seperated strings :
INSERT INTO tbx VALUES (..., 'Dance,Techno,House,Rock,Métal', 'Joystick,Autre', 'NRJ,FUN Radio,Europez,FG', 'MTV,Game One,Autre', ...)
for 4 of the SET columns.
February 24, 2003 at 7:18 am
Not quite sure what you mean...Is the data denormalized, meaning that a single column is storing multiple values for a single piece of data?
Also, you can ask the people supplying you with the PHPMyAdmin script to run a separate PHP script that runs a SELECT SetField+0 FROM... statement for each row in the main table, in turn generating an INSERT STATEMENT with valid foreign key values. If you need help with the PHP side, let me know...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply