用户经常需要把Excel的数据导入SQL中,只有这样先导入到一个表中,然后再对这个表进行处理。由于目标表的数据不能清空。数据存在的,需要作更新动作,不存在的,需要做插入动作。
代码
DECLARE @N INT = 1
DECLARE @ROWS INT = ( SELECT MAX ( [ ID ] ) FROM [ dbo ] . [ Loose04 ] )
WHILE @N <= @ROWS
BEGIN
-- 宣告临时变量,存储每笔记录需要异动的字段信息。
DECLARE @ItemCode NVARCHAR ( 100 ), @ItemGeneralName NVARCHAR ( 200 ), @Specification NVARCHAR ( 200 )
-- 把异动的字段信息存入变量中。
SELECT @ItemCode = [ Partno ] , @ItemGeneralName = [ Partname ] , @Specification = [ Specification ] FROM [ dbo ] . [ Loose04 ] WHERE [ Id ] = @N
IF EXISTS ( SELECT [ ItemCode ] FROM [ dbo ] . [ ItemGeneral ] WHERE [ ItemCode ] = @ItemCode )
-- 如果存在记录,作更新[ItemGeneralName]和[Specification]字段。
UPDATE [ dbo ] . [ ItemGeneral ] SET [ ItemGeneralName ] = @ItemGeneralName , [ Specification ] = @Specification WHERE [ ItemCode ] = @ItemCode
ELSE
-- 如果不存在,作插入动作。
INSERT INTO [ dbo ] . [ ItemGeneral ] ( [ ItemCode ] , [ ItemGeneralName ] , [ Specification ] ) VALUES ( @ItemCode , @ItemGeneralName , @Specification )
SET @N = @N + 1
END
DECLARE @N INT = 1
DECLARE @ROWS INT = ( SELECT MAX ( [ ID ] ) FROM [ dbo ] . [ Loose04 ] )
WHILE @N <= @ROWS
BEGIN
-- 宣告临时变量,存储每笔记录需要异动的字段信息。
DECLARE @ItemCode NVARCHAR ( 100 ), @ItemGeneralName NVARCHAR ( 200 ), @Specification NVARCHAR ( 200 )
-- 把异动的字段信息存入变量中。
SELECT @ItemCode = [ Partno ] , @ItemGeneralName = [ Partname ] , @Specification = [ Specification ] FROM [ dbo ] . [ Loose04 ] WHERE [ Id ] = @N
IF EXISTS ( SELECT [ ItemCode ] FROM [ dbo ] . [ ItemGeneral ] WHERE [ ItemCode ] = @ItemCode )
-- 如果存在记录,作更新[ItemGeneralName]和[Specification]字段。
UPDATE [ dbo ] . [ ItemGeneral ] SET [ ItemGeneralName ] = @ItemGeneralName , [ Specification ] = @Specification WHERE [ ItemCode ] = @ItemCode
ELSE
-- 如果不存在,作插入动作。
INSERT INTO [ dbo ] . [ ItemGeneral ] ( [ ItemCode ] , [ ItemGeneralName ] , [ Specification ] ) VALUES ( @ItemCode , @ItemGeneralName , @Specification )
SET @N = @N + 1
END