SQL SERVER临时表问题

adoue00

在使用组件UPS数据库连接.执行SQL (, )中，如果用语句 CREATE TABLE #Tmp_Yb_track 创建一个临时表，怎么样能保证在下一个语句中能用到而不被自动销毁？

laihobglei

可以拼接
SQL语句 = "CREATE TABLE #Tmp_Yb_track ("
SQL语句 = SQL语句 +""ID INT IDENTITY(1,1),""
SQL语句 = SQL语句 +.........................................

laihobglei

##Tmp_Yb_track 两个#号 创建后立即使用!不要分开执行.

adoue00

laihobglei 发表于 2025-7-11 10:08
##Tmp_Yb_track 两个#号 创建后立即使用!不要分开执行.

易语言好像只能单行执行SQL语句，有好的方法吗？语句太长了有点不方便维护

深水ADC

adoue00 发表于 2025-7-11 10:13
易语言好像只能单行执行SQL语句，有好的方法吗？语句太长了有点不方便维护 ...

是你代码问题 , 任何语言都不存在只能单行执行sql语句的问题,  获取到数据库连接以后, 开始事务.

adoue00

深水ADC 发表于 2025-7-11 10:16
是你代码问题 , 任何语言都不存在只能单行执行sql语句的问题,  获取到数据库连接以后, 开始事务.  ...

CREATE TABLE #Tmp_Yb_track (
    siShipmentID varchar(30),
    pkgTrackingNumber varchar(30) PRIMARY KEY,
    place varchar(100),
    sdate varchar(50),
    docdate varchar(50),
    code varchar(5),
    type varchar(5),
    intro varchar(300),
    simplifiedTextDescription varchar(300),
    payType varchar(30),
    payID varchar(100),
    payAmount DECIMAL(18,3),
    payCurrency varchar(10),
    payPaid BIT,
    packageCount int,
    serviceCode varchar(5),
    serviceLevelCode varchar(5),
    serviceDescription varchar(50)
);

INSERT INTO #Tmp_Yb_track VALUES (
    '54585947', '54585947', 'HK', '20250711121200', '20250711121200',
    '010', 'X', '在途中', '支fu期限。。',
    'ICOD', '1SXD3X', 300.000, 'PHP', 0,
    1, '566', '065', 'SA'
);

MERGE INTO Yb_track AS Target
USING #Tmp_Yb_track AS Source
ON Target.pkgTrackingNumber = Source.pkgTrackingNumber
WHEN MATCHED AND (
    ISNULL(Target.place, '') <> ISNULL(Source.place, '')
    OR ISNULL(Target.sdate, '') <> ISNULL(Source.sdate, '')
    OR ISNULL(Target.intro, '') <> ISNULL(Source.intro, '')
    OR ISNULL(Target.code, '') <> ISNULL(Source.code, '')
)
THEN
UPDATE SET
    siShipmentID = Source.siShipmentID,
    place = Source.place,
    sdate = Source.sdate,
    docdate = Source.docdate,
    code = Source.code,
    type = Source.type,
    intro = Source.intro,
    simplifiedTextDescription = Source.simplifiedTextDescription,
    payType = Source.payType,
    payID = Source.payID,
    payAmount = Source.payAmount,
    payCurrency = Source.payCurrency,
    payPaid = Source.payPaid,
    packageCount = Source.packageCount,
    serviceCode = Source.serviceCode,
    serviceLevelCode = Source.serviceLevelCode,
    serviceDescription = Source.serviceDescription
WHEN NOT MATCHED
THEN
INSERT (
    siShipmentID, pkgTrackingNumber, place, sdate, docdate, code, type, intro,
    simplifiedTextDescription, payType, payID, payAmount, payCurrency, payPaid,
    packageCount, serviceCode, serviceLevelCode, serviceDescription
)
VALUES (
    Source.siShipmentID, Source.pkgTrackingNumber, Source.place, Source.sdate, Source.docdate, Source.code, Source.type, Source.intro,
    Source.simplifiedTextDescription, Source.payType, Source.payID, Source.payAmount, Source.payCurrency, Source.payPaid,
    Source.packageCount, Source.serviceCode, Source.serviceLevelCode, Source.serviceDescription
);

DROP TABLE #Tmp_Yb_track;
===应用的是超长SQL场景

adoue00

laihobglei 发表于 2025-7-11 10:15
可以拼接
SQL语句 = "CREATE TABLE #Tmp_Yb_track ("
SQL语句 = SQL语句 +""ID INT IDENTITY(1,1),""

感谢大佬，SQL语句很长，每小时处理15万+数据只能用 临时表 + MERGE

独孤求胜

既然下次还要用，直接创建表就好了，不用的时候再删掉。如果是当次使用，其它连接不使用，创建一个存储过程就行

一瓶矿泉水

用存储过程不香吗？

adoue00

一瓶矿泉水 发表于 2025-7-11 12:08
用存储过程不香吗？

大佬有例子吗，没接触过存储过程