CREATE PROCEDURE sp_ImportData_30
(
@table VARCHAR(40), /*表名*/
@keyfield VARCHAR(100),/*主键字段名,多个主键时按';'分割*/
@srcsql VARCHAR(400) /*从源表检索数据的语句,如果该语句中包含"'"单引号要用两个单引号表示*/
)
/*根据条件导入数据*/
AS
DECLARE @sql VARCHAR(2000)
DECLARE @rtnError INT
DECLARE @updateDybz VARCHAR(1000)
SET @rtnError = 0
/*从数据源中取出数据,并插入目的表中没有的记录*/
SET @sql = 'INSERT INTO '+@table+ ' SELECT * FROM OPENROWSET(''SQLOLEDB'',''172.16.99.136'';''sa'';''sybasepb'',''' + @srcsql
SET @sql = @sql + ''' ) AS SOURCE WHERE NOT EXISTS (SELECT 1 FROM ' + @table + ' WHERE 1=1 ' /*取出多个主键,构造SQL语句*/
SET @keyfield = LTRIM(RTRIM(@keyfield))
WHILE(CHARINDEX(';',@keyfield) > 0)
BEGIN
SET @sql = @sql + ' AND ' + @table + '.' + SUBSTRING(@keyfield, 1,CHARINDEX(';',@keyfield)-1) + ' = ' + 'SOURCE.'+SUBSTRING(@keyfield, 1,CHARINDEX(';',@keyfield)-1)
SET @keyfield = STUFF(@keyfield, 1, CHARINDEX(';',@keyfield), '')
END
SET @sql =@sql + ' AND ' + @table + '.' +@keyfield + ' = ' + 'SOURCE.'+@keyfield + ')'
EXEC (@sql)
IF( @@ERROR<> 0)
SET @rtnError = @@ERROR
--IF(@table = 'L_PATIENTINFO')/*更新样本打印标志*/
--BEGIN
--SET @updateDybz = REPLACE (@srcsql ,SUBSTRING(@srcsql, 0, CHARINDEX('WHERE', @srcsql)) , 'UPDATE L_PATIENTINFO SET RESULTSTATUS=4 ' )
--EXEC(@updateDybz)
--END
--PRINT(@sql)
--PRINT(@rtnError)
RETURN @rtnError
GO执行过程错误:
EXEC sp_ImportData_30 'L_SAMPLETYPE', 'SAMPLETYPE','SELECT * FROM lis.dbo.L_SAMPLETYPE' 服务器: 消息 446,级别 16,状态 9,行 1
无法解决 equal to 操作的排序规则冲突。另外一个就没问题:
EXEC sp_ImportData_30 'GY_KSDM', 'KSDM', 'SELECT * FROM lis.dbo.GY_KSDM'
/*从数据源中取出数据,并插入目的表中没有的记录*/
SET @sql = 'INSERT INTO '+@table+ ' SELECT * FROM OPENROWSET(''SQLOLEDB'',''172.16.99.136'';''sa'';''sybasepb'',''' + @srcsql
SET @sql = @sql + ''' ) AS SOURCE WHERE NOT EXISTS (SELECT 1 FROM ' + @table + ' WHERE 1=1 '
进行collation 的强制转换即可。