SQL script:
--Create a linked server (where Db2server is the IP address or server name of DB2)
exec sp_addlinkedserver ' srv_lnk ', ', ' SQLOLEDB ', ' 10.138.60.94 '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' sa ', ' 123456 '
Go
--Data import (table B exists)
--insert into Srv_lnk.sys.Estamp.EstampConfig (field1,field2)
INSERT into Srv_lnk. [Postloan]. [dbo]. [Testreport]
([Dbversion]
, [Codelines]
, [Duration]
, [Lastdate]
, [Summary]
, [Amount]
, [Successamount]
, [Failamount]
, [Bugamount]
, [Remark])
SELECT
[Dbversion]
, [Codelines]
, [Duration]
, [Lastdate]
, [Summary]
, [Amount]
, [Successamount]
, [Failamount]
, [Bugamount]
, [Remark]
from [Postloan]. [dbo]. [Testreport]
--Data import (table B does not exist)
--select value1,value2 into Srv_lnk. Db2.dbo.b
--from DB1.DBO.A
--Close connection
exec sp_dropserver ' srv_lnk ', ' droplogins '
When you execute the above script directly using the Pymssql library in Python, you will get an error: "Incorrect syntax near ' Go '. Db-lib error message 20018, severity 15 ". This is because go is not a keyword in SQL.
You can then use Os.system+sqlcmd in Python to execute the SQL script, using the following method:
Import OS OS. System ("Sqlcmd-s localhost-u sa-p 123456-d training-i \"d:\\sql\\tmp.txt\"")
sqlcmd Command Parameter Description:
-S: Indicates the database server address, such as localhost
-U: User name, such as SA
-P: password, such as 123456
-D: Database name, such as training
-I: File paths, such as files stored in D:\sql\tmp.txt, need to be written like this \ "D:\\sql\\tmp.txt\"
The path writing needs to be noted. The path is enclosed in double quotes "File_path", and there are already double quotes outside, which need to be escaped. Because it is in the Windows system, the path is preceded by a backslash and also needs to be escaped.
Reference Address: https://my.oschina.net/shong/blog/768394
Go statement error when executing SQL script in Python