Import Filter And Replace

[jfreak53]

I have a lot, I mean a lot of Data to transfer from a MSSQL DB to a MySQL DB. The last import I did I found many many errors that I am now having to go back and fix, and this next DB is much much larger.

What I have and need is this. There are a lot of tables that have columns in them that are defined in MSSQL as decimals with lenghts of 18,0 and 19,255. Well I get major errors when I try to import these and it never finishes importing because of them, last time I had to resort to ignoring errors and this caused the problems. So I need to filter the import so that when the source table's column is a decimal, convert it to a double and the length for that column alone always make it X amount.

Is there a way to filter like this when importing from another DB? Or another program for importing between these two DB's that might do this?

This needs to be an all over filter, not a table per table that I have to enter since there are over 600 tables to import, I would be here forever ha ha

Any ideas? Thanks in advance.

[peterlaursen]

Are you using the SQLyog 'Import External Data Tool'?

What were the errors you had?

We will need to understand the problem before we can give an answer.

[jfreak53]

'peterlaursen' wrote:

Are you using the SQLyog 'Import External Data Tool'?

What were the errors you had?

We will need to understand the problem before we can give an answer.

I understand. Yes I am using the 'Import External Data Tool' to do my imports. Since I didn't save my log files last time when I got the errors I just ran a test to find out one for you and post it. But the table I ran the test on didn't give me any errors. It imported it, the decimal columns and all, it just didn't import the right data. In most fields it imported for instance 0.0000 instead of the 0.40 that was there or the 8.20 that was there.

So this is an example of I would like SQLYog to automatically change all decimal columns it imports into double columns automatically. When I import this same table and change those decimal columns myself manually to double's it works and the data is imported.

[peterlaursen]

OK ..

I understand that data get truncated to ZERO on import when importing SQL Server 'decimal' type using SQLyog default mapping. I have no SQL Server to test with here but we will check this on Monday. But please tell the exact versions you use of

1) SQLyog

2) ODBC-driver for MS SQL

3) MS SQL itself

[jfreak53]

'peterlaursen' wrote:

OK ..

I understand that data get truncated to ZERO on import when importing SQL Server 'decimal' type using SQLyog default mapping. I have no SQL Server to test with here but we will check this on Monday. But please tell the exact versions you use of

1) SQLyog

2) ODBC-driver for MS SQL

3) MS SQL itself

1. 8.21

2. SQL Server – 2000.85.1117.00

3. MSSQL Server 2005 Express

[Aparna]

Hi,

Quote:

It imported it, the decimal columns and all, it just didn't import the right data. In most fields it imported for instance 0.0000 instead of the 0.40 that was there

The data type you have used is decimal(18,0) where the “18” stands for the Precision value and “0” stands for the scale value. Scale value indicates the number of digits that can be stored after the decimal point (to the right side of the decimal point). Since you have given the scale value as “0” it does not store any values after the decimal point. So in you case the values that are below 0.5 get rounded off to the previous closest integer which is 0 and the values ranging from 0.5 to 0.9 get rounded off to the next closest integer that is 1. Please read this link for more information on scale and Precision: http://msdn.microsoft.com/en-us/library/ms187746%28v=SQL.90%29.aspx

After entering the values in the decimal(18,0) field and saving it, request you to re-check the values to be sure that the values are stored just as you have entered them. (in your case make sure 0.4 gets stored as 0.4 itself in decimal(18,0) after saving it)

With the right precision and scale value SQLyog imports data successfully. (You can make use of the above link to determine the appropriate precision and scale values for your data)

Quote:

This needs to be an all over filter, not a table per table that I have to enter since there are over 600 tables to import

There is no over all mapping option(Is that what you are referring to as filter?). Please correct me if I have misunderstood.

Thanks and regards,

Aparna.

[jfreak53]

'Aparna' wrote:

Hi,

The data type you have used is decimal(18,0) where the “18” stands for the Precision value and “0” stands for the scale value. Scale value indicates the number of digits that can be stored after the decimal point (to the right side of the decimal point). Since you have given the scale value as “0” it does not store any values after the decimal point. So in you case the values that are below 0.5 get rounded off to the previous closest integer which is 0 and the values ranging from 0.5 to 0.9 get rounded off to the next closest integer that is 1. Please read this link for more information on scale and Precision: http://msdn.microsoft.com/en-us/library/ms187746%28v=SQL.90%29.aspx

After entering the values in the decimal(18,0) field and saving it, request you to re-check the values to be sure that the values are stored just as you have entered them. (in your case make sure 0.4 gets stored as 0.4 itself in decimal(18,0) after saving it)

With the right precision and scale value SQLyog imports data successfully. (You can make use of the above link to determine the appropriate precision and scale values for your data)

There is no over all mapping option(Is that what you are referring to as filter?). Please correct me if I have misunderstood.

Thanks and regards,

Aparna.

Yes I understand what the decimal values are for, before and after decimal. That is the problem, I cannot do this by hand there are over 600 tables with at least 3 per each one that needs changing. So I need, as you stated at the end, an overall mapping option that states (if it's decimal, change to double and change size to X) so that I can change all tables together.

[Aparna]

Hi,

Request you to do the following.

Create a table in MSSQL with decimal(18,0) as the data type and enter 0.4 and 0.5 as values in it. Save the table, re-open the table and please confirm if you see 0.4 and 0.5 in the table as you had entered earlier.

Thanks and Regards,

Aparna.

[Author]

Posts