循环插入一条数据的sql写法
DECLARE @i INT
SET @i = 1
WHILE @i > 0
BEGIN
DECLARE @TransportFormMstID BIGINT;
DECLARE @TradeOrderID BIGINT;
DECLARE @TransportFormMstID_T BIGINT;
DECLARE @DeliveryCode VARCHAR(40);
SELECT TOP 1
@TransportFormMstID_T = TransportFormMstID ,
@TradeOrderID = TradeOrderID ,
@DeliveryCode = DeliveryCode
FROM dbo.TWX_TransportFromMst_T WITH ( NOLOCK )
WHERE DeleteUser = 1;
IF NOT EXISTS ( SELECT * FROM dbo.TWX_TransportFromMst WITH ( NOLOCK )WHERE DeliveryCode = @DeliveryCode )
BEGIN
INSERT INTO dbo.TWX_TransportFromMst
( TransportFormMstCode
)
SELECT TransportFormMstCode ,
FROM dbo.TWX_TransportFromMst_T WITH ( NOLOCK )
WHERE TransportFormMstID = @TransportFormMstID_T
SELECT @TransportFormMstID = @@IDENTITY;
END
ELSE
BEGIN
SELECT TOP 1
@TransportFormMstID = TransportFormMstID
FROM dbo.TWX_TransportFromMst WITH ( NOLOCK )
WHERE DeliveryCode = @DeliveryCode;
END
PRINT @DeliveryCode + '-正在执行'
IF NOT EXISTS ( SELECT *
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID
AND ProductID IN (
SELECT ProductID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID ) )
BEGIN
INSERT INTO dbo.TWX_TransportFromDtl
( TradeOrderID ,
ProductID ,
ProductCatalogID ,
ProductCatalogIDPath ,
TotalItemActualPrice ,
ItemDetailUrl ,
TransportFormMstID ,
ProductMaterial ,
ProductMaterialEn
)
SELECT TradeOrderID ,
TotalItemActualPrice ,
ItemDetailUrl ,
@TransportFormMstID ,
ProductMaterial ,
ProductMaterialEn
FROM dbo.TWX_TransportFromDtl_T WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID
AND ProductID IN (
SELECT ProductID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID )
END
ELSE
BEGIN
UPDATE dbo.TWX_TransportFromDtl
SET TransportFormMstID = @TransportFormMstID
WHERE TransportFormDtlID IN (
SELECT TransportFormDtlID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID
AND ProductID IN (
SELECT ProductID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID ) )
END
UPDATE dbo.TWX_TransportFromMst_T
SET deleteUser = 2
WHERE TransportFormMstID = @TransportFormMstID_T
END
----------------------------------------比较完整的写法
DECLARE @i INT
SET @i = 1
WHILE @i > 0
BEGIN
DECLARE @TransportFormMstID BIGINT;
DECLARE @TradeOrderID BIGINT;
DECLARE @TransportFormMstID_T BIGINT;
DECLARE @DeliveryCode VARCHAR(40);
SELECT TOP 1
@TransportFormMstID_T = TransportFormMstID ,
@TradeOrderID = TradeOrderID ,
@DeliveryCode = DeliveryCode
FROM dbo.TWX_TransportFromMst_T WITH ( NOLOCK )
WHERE DeleteUser = 1;
IF NOT EXISTS ( SELECT *
FROM dbo.TWX_TransportFromMst WITH ( NOLOCK )
WHERE DeliveryCode = @DeliveryCode )
BEGIN
INSERT INTO dbo.TWX_TransportFromMst
( TransportFormMstCode ,
TransportFormMstGuid ,
TransportFormName ,
ComeFrom
)
SELECT TransportFormMstCode ,
TransportFormMstGuid ,
TransportFormName ,
ComeFrom
FROM dbo.TWX_TransportFromMst_T WITH ( NOLOCK )
WHERE TransportFormMstID = @TransportFormMstID_T
SELECT @TransportFormMstID = @@IDENTITY;
END
ELSE
BEGIN
SELECT TOP 1
@TransportFormMstID = TransportFormMstID
FROM dbo.TWX_TransportFromMst WITH ( NOLOCK )
WHERE DeliveryCode = @DeliveryCode;
END
PRINT @DeliveryCode + '-正在执行'
IF NOT EXISTS ( SELECT *
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID
AND ProductID IN (
SELECT ProductID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID ) )
BEGIN
INSERT INTO dbo.TWX_TransportFromDtl
( TradeOrderID ,
ProductID ,
ProductCatalogID
)
SELECT TradeOrderID ,
ProductID ,
ProductCatalogID ,
@TransportFormMstID ,
ProductMaterial ,
ProductMaterialEn
FROM dbo.TWX_TransportFromDtl_T WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID
AND ProductID IN (
SELECT ProductID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID )
END
ELSE
BEGIN
UPDATE dbo.TWX_TransportFromDtl
SET TransportFormMstID = @TransportFormMstID
WHERE TransportFormDtlID IN (
SELECT TransportFormDtlID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID
AND ProductID IN (
SELECT ProductID
FROM dbo.TWX_TransportFromDtl WITH ( NOLOCK )
WHERE TradeOrderID = @TradeOrderID ) )
END
UPDATE dbo.TWX_TransportFromMst_T
SET deleteUser = 2
WHERE TransportFormMstID = @TransportFormMstID_T
END
posted on 2017-11-16 10:31 chengjunde 阅读(1057) 评论(0) 编辑 收藏 举报