insert into mysql from mssql

• boredmind

  SSC Veteran

  Points: 201

  More actions

  December 1, 2014 at 2:23 pm

  #295516

  Hello, I have 2 tables on 2 databases. The 1st is on a MSSQL server and the second is on a Linked Server MySQL database server.

  I want to insert all table data from MSSQL to the Mysql linked server

  i am using this query

  INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product] select * FROM [homeputer8].[eshop].[dbo].[oc_product]

  and I get the following result:

  Msg 7313, Level 16, State 1, Line 1

  An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "SQLTEST".

  I am sorry if I do not phrase it exactly but i am a newbie.

  the select, update or delete openquery works fine. only the insert does not.

  Thank you,

  Tasos

• g.britton

  SSChampion

  Points: 13863

  More actions

  December 1, 2014 at 2:24 pm

  #1761874

  if you run the select part of your query on the mysql instance, does it work?

  Gerald Britton, Pluralsight courses

• boredmind

  SSC Veteran

  Points: 201

  More actions

  December 1, 2014 at 2:28 pm

  #1761877

  yes it works,

  for example these query s works

  delete from openquery(SQLTEST, 'select * from oc_product')

  or this

  select * from openquery(SQLTEST, 'select * from oc_product')

  are working just fine

• Lowell

  SSC Guru

  Points: 323522

  More actions

  December 1, 2014 at 3:02 pm

  #1761885

  could it be related to implied/deferred schema? SQL lets you be lazy with not explicitly identifying columns, but it gets a bit tighter when you work with other linked server types:

  if you explicitly identify the columns, does it work?

  ie:

  INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product](Column1,Column2,Column3)

  select SomeColumn1,SomeColumn2,SomeColumn3 FROM [homeputer8].[eshop].[dbo].[oc_product]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• boredmind

  SSC Veteran

  Points: 201

  More actions

  December 2, 2014 at 12:46 pm

  #1762207

  thank you for the reply Lowell

  no it does not work also.

  Lowell (12/1/2014)

  ---

  could it be related to implied/deferred schema? SQL lets you be lazy with not explicitly identifying columns, but it gets a bit tighter when you work with other linked server types:

  if you explicitly identify the columns, does it work?

  ie:

  INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product](Column1,Column2,Column3)

  select SomeColumn1,SomeColumn2,SomeColumn3 FROM [homeputer8].[eshop].[dbo].[oc_product]

  the code in this that it does not work also is :

  INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product](product_id

  ,model

  ,sku

  ,upc

  ,ean

  ,jan

  ,isbn

  ,mpn

  ,location

  ,quantity

  ,stock_status_id

  ,image

  ,manufacturer_id

  ,shipping

  ,price

  ,points

  ,tax_class_id

  ,date_available

  ,weight

  ,weight_class_id

  ,length

  ,width

  ,height

  ,length_class_id

  ,subtract

  ,minimum

  ,sort_order

  ,status

  ,date_added

  ,date_modified

  ,viewed)

  select product_id

  ,model

  ,sku

  ,upc

  ,ean

  ,jan

  ,isbn

  ,mpn

  ,location

  ,quantity

  ,stock_status_id

  ,image

  ,manufacturer_id

  ,shipping

  ,price

  ,points

  ,tax_class_id

  ,date_available

  ,weight

  ,weight_class_id

  ,length

  ,width

  ,height

  ,length_class_id

  ,subtract

  ,minimum

  ,sort_order

  ,status

  ,date_added

  ,date_modified

  ,viewed FROM [homeputer8].[eshop].[dbo].[oc_product]