循环插入一条数据的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
最新文章
- 使用KRPano资源分析工具还原全景图片
- 高级sql注入
- select 选中 option的问题
- To follow the path
- JPages分页插件的使用
- 关于键盘冲突那点事（3键冲突/7键冲突/PS2/USB的各种原理）
- 营配数据质量核查，关于营销mis系统与配电gis系统里面的sql语句查询，做为积累使用，下次就不用重复写同样的语句了。
- php跨服务器传递对象
- Qt QToolTip 控件背景的 QSS 设置方法(摘抄)
- uva 156 （map）
- 1045-access denied for user 'root'@
- [java]配置java环境
- OS + linux proxy
- 机器学习实战（Machine Learning in Action）学习笔记————10.奇异值分解(SVD)原理、基于协同过滤的推荐引擎、数据降维
- WinSockets编程（六）select模式
- 使用CocoaPods来做iOS程序的包依赖管理
- perf之record
- Tomcat7并发和线程数
- 02 - Unit07：显示笔记下拉菜单、笔记的分享功能、笔记的删除功能
- 6 spark 存储体系