Problem Building Script with Int

[Mossy]

I am currently moving a table from a msSQL DB to a MySQL DB.

I am trying to build the bones of an insert statement by adding strings
to a select statement.

SELECT '(' + job_referenceno + ', ''' + job_title + ''', ''' +
job_category + ''', ''' + job_type + ''', ''' + job_location + ''', '''
+ job_duaration + ''', ' + job_salary + ', ''' + job_contactpers on +
''', ' FROM JobDetails WHERE job_active = 1;

This works perfectly until I try and pull an int column from the Table.
When I add a column to the string that is INT the query returns 0 rows,
with no errors.

Can anyone help explain / overcome this?

Regards,

Tomas

[Dan Guzman]

Since you are building a string, try casting the numeric values as a string
type when you concatenate the value. Also, consider handling NULLs:

COALESCE(CAST(M yIntColumn AS varchar(10)), 'NULL')

You can find an generalized script to generate insert statements at
http://vyaskn.tripod.com/code.htm#inserts.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mossy" <to*********@gm ail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I am currently moving a table from a msSQL DB to a MySQL DB.

I am trying to build the bones of an insert statement by adding strings
to a select statement.

SELECT '(' + job_referenceno + ', ''' + job_title + ''', ''' +
job_category + ''', ''' + job_type + ''', ''' + job_location + ''', '''
+ job_duaration + ''', ' + job_salary + ', ''' + job_contactpers on +
''', ' FROM JobDetails WHERE job_active = 1;

This works perfectly until I try and pull an int column from the Table.
When I add a column to the string that is INT the query returns 0 rows,
with no errors.

Can anyone help explain / overcome this?

Regards,

Tomas

[Mossy]

Worked perfectly. Thank you.

Tomas