The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
COURSE of the MONTH
15 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Insert into MySQL table with an autoincrement ID using SQL Server Linked Server Openquery
Posted on 2016-07-15
Hello I'm trying to insert records into a MySQL DB table, and i get the following error message.
The MySQL db table has an auto-increment ID column
OLE DB provider "MSDASQL" for linked server "test_db" returned message "Query cannot be updated because it contains no searchable columns to use as a key.".
Msg 7343, Level 16, State 2, Line 5
The OLE DB provider "MSDASQL" for linked server "test_db" could not INSERT INTO table "[MSDASQL]".
The query im using is:
can anyone help?
The MySQL db table has an auto-increment ID column
OLE DB provider "MSDASQL" for linked server "test_db" returned message "Query cannot be updated because it contains no searchable columns to use as a key.".
Msg 7343, Level 16, State 2, Line 5
The OLE DB provider "MSDASQL" for linked server "test_db" could not INSERT INTO table "[MSDASQL]".
The query im using is:
INSERT INTO OPENQUERY (test_db, 'SELECT id, name, create_date, create_user_id FROM testers')
VALUES (0, 'tester_a', getdate(), 'sqlagent')
can anyone help?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
Do not use values for auto-increment fields, they are filled automatically by mysql.
If "id" is the auto-increment field you could put:
Why using 0 as value? By default, mysql cannot insert an autoincrement value of 0.
Try changing to a different value (1) if you nned to put a fixed value, but cant be duplicated
If "id" is the auto-increment field you could put:
INSERT INTO OPENQUERY (test_db, 'SELECT name, create_date, create_user_id FROM testers')
VALUES ( 'tester_a', getdate(), 'sqlagent')
Why using 0 as value? By default, mysql cannot insert an autoincrement value of 0.
Try changing to a different value (1) if you nned to put a fixed value, but cant be duplicated
Things to try and check:
Some field in your sql data could be in a unexpected format,for example the date field "create_date", the format from getdate() must match the format mysql expects to receive as a date field, try using a date format function (output formay 'yyyy-mm-dd' instead of raw getdate() result.
The mysql table probably needs a unique key to let the sql provider check if that row exists before insert (Is any of the fields, or a couple of them, a unique combination?, create an unique index for this columns, eg: name and create_date)
Some field in your sql data could be in a unexpected format,for example the date field "create_date", the format from getdate() must match the format mysql expects to receive as a date field, try using a date format function (output formay 'yyyy-mm-dd' instead of raw getdate() result.
The mysql table probably needs a unique key to let the sql provider check if that row exists before insert (Is any of the fields, or a couple of them, a unique combination?, create an unique index for this columns, eg: name and create_date)
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses
Course of the Month15 days, 6 hours left to enroll
741 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.