你将接口得来的数据放到临时表中,然后获取临时表中 离职时间 不为空 的数据保存到离职表中 select 字段 from 临时表 into 离职表 where 离职时间<>''离职时间 为空 的该条数据保存到在职表中, select 字段 from 临时表 into 在职表 where 离职时间='' 这个如果是数据库的接口可以这样做,如何是web service 直接在程序里判断
USE [user] GOSET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GOALTER PROCEDURE [dbo].[SAPSynchronizationInPostTable] @FContentXML XML, @FCreationDate DATETIME,
@MessageCode BIGINT OUT AS BEGINSET NOCOUNT ON; IF (@FContentXML IS NULL OR @FCreationDate IS NULL) BEGIN SET @MessageCode = -1 RETURN END
IF (object_id(N'tempdb..#TempTable') IS NULL) BEGIN SELECT V.value('(id)[1]', 'NVARCHAR (50)') AS FUserID, V.value('(name)[1]', 'NVARCHAR (50)') AS FUserName, V.value('(email)[1]', 'NVARCHAR (255)') AS FEmail, V.value('(Position)[1]', 'NVARCHAR (50)') AS FPosition, V.value('(quitpostdate)[1]', 'NVARCHAR (50)') AS FQuitPostDate INTO #TempTable FROM @FContentXML.nodes('/NewDataSet/InPostTable') T(V) END
IF (object_id(N'tempdb..#TempTable') IS NOT NULL) DROP TABLE #TempTable
END 还是没有搞懂 我把源码贴出来 帮我看看该怎么修改
建议不要使用游标,数据处理量不大时,可以使用游标,否则存储过程将会执行得很慢。 个人建议使用 Select into 方法 例如: Select a.MainKey,a.name,a.LeaveTime into table_B(MainKey,name,LeaveTime) from tbale_A where a.leaveTime is not null
Select a.MainKey,a.name,a.LeaveTime into table_B(MainKey,name,LeaveTime) from tbale_A a where a.leaveTime is not null
declare FUserID_cursor cursor for select t.FUserID from #TempTable t --where t.FQuitPostDate <>''//分开处理时用
declare @FUserID_info NVARCHAR (50) open FUserID_cursor fetch next from FUserID_cursor into @FUserID_info while(@@FETCH_STATUS=0) begin declare @FQuitPostDate as NVARCHAR (50)
select @FQuitPostDate = FQuitPostDate from #TempTable where FUserID = @FUserID_info if @FQuitPostDate <>'' begin INSERT INTO FIsSAPData ( FUserID, FUserName, FEmail, FPosition, FQuitPostDate, ) SELECT FUserID, FUserName, FEmail, FPosition, FQuitPostDate, FROM #TempTable where FUserID = @FUserID_info fetch next from FUserID_cursor into @FUserID_info end else begin INSERT INTO FIsNotSAPData ( FUserID, FUserName, FEmail, FPosition, FQuitPostDate, ) SELECT FUserID, FUserName, FEmail, FPosition, FQuitPostDate, FROM #TempTable where FUserID = @FUserID_info fetch next from FUserID_cursor into @FUserID_info end end你说遍历,是这样???游标的话这样。 笨方法,还是一次性全insert比较快
insert into 表 select 要插入的字段 from 表 where 条件
那就两个sql就够了INSERT INTO FIsSAPData ( FUserID, FUserName, FEmail, FPosition, FQuitPostDate, ) SELECT FUserID, FUserName, FEmail, FPosition, FQuitPostDate, FROM #TempTable where FQuitPostDate is not null INSERT INTO FIsNotSAPData ( FUserID, FUserName, FEmail, FPosition, FQuitPostDate, ) SELECT FUserID, FUserName, FEmail, FPosition, FQuitPostDate, FROM #TempTable where FQuitPostDate is null原来的表示全删除直接同步,还是更新你得自己判断
select 字段 from 临时表 into 离职表 where 离职时间<>''离职时间 为空 的该条数据保存到在职表中,
select 字段 from 临时表 into 在职表 where 离职时间=''
这个如果是数据库的接口可以这样做,如何是web service 直接在程序里判断
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[SAPSynchronizationInPostTable] @FContentXML XML,
@FCreationDate DATETIME,
@MessageCode BIGINT OUT
AS
BEGINSET NOCOUNT ON;
IF (@FContentXML IS NULL OR @FCreationDate IS NULL)
BEGIN
SET @MessageCode = -1
RETURN
END
IF (object_id(N'tempdb..#TempTable') IS NULL)
BEGIN
SELECT
V.value('(id)[1]', 'NVARCHAR (50)') AS FUserID,
V.value('(name)[1]', 'NVARCHAR (50)') AS FUserName,
V.value('(email)[1]', 'NVARCHAR (255)') AS FEmail,
V.value('(Position)[1]', 'NVARCHAR (50)') AS FPosition,
V.value('(quitpostdate)[1]', 'NVARCHAR (50)') AS FQuitPostDate
INTO #TempTable
FROM @FContentXML.nodes('/NewDataSet/InPostTable') T(V)
END
DECLARE @TranName VARCHAR(50);
SELECT @TranName = 'SAPSynchronizationInPostTable';
BEGIN TRAN @TranName
INSERT INTO FIsSAPData (
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
)
SELECT NEWID(),
NEWID(),
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
FROM #TempTable
COMMIT TRAN @TranName
IF (object_id(N'tempdb..#TempTable') IS NOT NULL)
DROP TABLE #TempTable
END
还是没有搞懂 我把源码贴出来 帮我看看该怎么修改
个人建议使用 Select into 方法 例如: Select a.MainKey,a.name,a.LeaveTime into table_B(MainKey,name,LeaveTime) from tbale_A where a.leaveTime is not null
select ...from...into
insert into...select8楼这两个语句的用法.配合使用还行哈
declare FUserID_cursor cursor for
select t.FUserID
from #TempTable t
--where t.FQuitPostDate <>''//分开处理时用
declare @FUserID_info NVARCHAR (50)
open FUserID_cursor
fetch next from FUserID_cursor into @FUserID_info
while(@@FETCH_STATUS=0)
begin
declare @FQuitPostDate as NVARCHAR (50)
select @FQuitPostDate = FQuitPostDate from #TempTable where FUserID = @FUserID_info
if @FQuitPostDate <>''
begin
INSERT INTO FIsSAPData (
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
)
SELECT
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
FROM #TempTable
where FUserID = @FUserID_info
fetch next from FUserID_cursor into @FUserID_info
end
else
begin
INSERT INTO FIsNotSAPData (
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
)
SELECT
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
FROM #TempTable
where FUserID = @FUserID_info
fetch next from FUserID_cursor into @FUserID_info
end
end你说遍历,是这样???游标的话这样。
笨方法,还是一次性全insert比较快
select 要插入的字段 from 表 where 条件
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
)
SELECT
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
FROM #TempTable
where FQuitPostDate is not null
INSERT INTO FIsNotSAPData (
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
)
SELECT
FUserID,
FUserName,
FEmail,
FPosition,
FQuitPostDate,
FROM #TempTable
where FQuitPostDate is null原来的表示全删除直接同步,还是更新你得自己判断
还有各种的不爽.
反正我是能不用procedure就不用.
使用C#函数来insert 但是数据量每一次同步都在上万条以上,会不会对程序造成影响
循环游标 insert到离职表中
关闭游标
设定游标 在职 查询在table中 在职的人员id
循环游标 insert到在职表中
关闭游标
循环游标 insert到离职表中
关闭游标
设定游标 在职 查询在table中 在职的人员id
循环游标 insert到在职表中
关闭游标
insert 在职表 (相应字段) select (相应字段)from table where datetime=''