插入语句期间链接服务器出现错误的情况 [英] Error With Linked Server during the insert statement in where condition
问题描述
代码: -
使用MYSQL;
声明@Max_Id数字
set @ Max_Id = convert(数字,(从流量中选择max(id));
插入MYSQL.dbo.traffic(id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID)
选择id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID
来自openquery(MYSQL,'select id,date,time,ip,来自myvar.traffic的resultCode,bytes,url,authuser,sitesID,usersID,其中id>''+ @ Max_Id +'''')
@Max_Id充当字符串,但它是一个变量。我将它声明为变量如何在上面的语句中给出变量
有您的查询有问题。尝试修改后的查询。
使用 MYSQL;
声明 @ Max_Id 整数
设置 @Max_Id =(选择 max(id)来自流量);
插入 进入 MYSQL.dbo.traffic(id, date , time ,ip,resultCode,bytes,url,authuser,sitesID,usersID)
选择 id,日期,时间,ip,resultCode,bytes,url,authuser ,sitesID,usersID
来自 openquery (MYSQL,' 从myvar.traffic中选择id,date,url,ip,resultCode,bytes,url,authuser,sitesID,usersID,其中id>''+ @ Max_Id +' ''')
通过使用内连接,问题可以解决。这有效....
插入 进入 MYSQL.dbo。流量(ID,日期,时间,ip,resultCode,bytes,url,authuser,sitesID,usersId)
SELECT t1.id,t1。 date ,t1。 time ,t1.ip,t1.resultCode,t1.bytes,t1.url,t1.authuser,t1.sitesID, t1.usersID FROM
OPENQUERY (MYSQL, ' SELECT * FROM mysar.traffic')T1
INNER JOIN
MYSQL.DBO.traffic T2 ON T1.id> T2.id
code:-
use MYSQL;
Declare @Max_Id numeric
set @Max_Id=convert(numeric,(select max(id) from traffic));
insert into MYSQL.dbo.traffic(id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID)
select id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID
From openquery(MYSQL,'select id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID from myvar.traffic where id > ''+@Max_Id+'''')
@Max_Id is acting as a string but it is a variable.i declare it as variable how can i give the variable in the above statement
There is a issue with your query. Try the modified query.
use MYSQL; Declare @Max_Id Integer set @Max_Id=(select max(id) from traffic); insert into MYSQL.dbo.traffic(id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID) select id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID From openquery(MYSQL,'select id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersID from myvar.traffic where id > ''+@Max_Id+'''')
By using inner join the problem can be solved .this works....
insert into MYSQL.dbo.traffic(id,date,time,ip,resultCode,bytes,url,authuser,sitesID,usersId) SELECT t1.id,t1.date,t1.time,t1.ip,t1.resultCode,t1.bytes,t1.url,t1.authuser,t1.sitesID,t1.usersID FROM OPENQUERY(MYSQL, 'SELECT * FROM mysar.traffic') T1 INNER JOIN MYSQL.DBO.traffic T2 ON T1.id > T2.id
这篇关于插入语句期间链接服务器出现错误的情况的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!