您当前的位置: 比如 现有 A,B,C,D四张表。里面的字段都是一样的。
就是这四个表 同一个临时表。
简单的过程就是这样的。
CREATE PROCEDURE dbo.zf_sp_web_preauthTemp_query
@tag int
AS
BEGIN
DECLARE @tableNAME VARCHAR(100)
if(@tag=1)
--zf_tb_preauth_rvs_log
SELECT @tableNAME = ‘A’
else if(@tag=2)
SELECT @tableNAME = ‘B’
else if(@tag=3)
SELECT @tableNAME = 'C'
ELSE
SELECT @tableNAME='D'
SELECT * INTO #PreauthRvsx FROM @tableNAME
END
go
EXEC sp_procxmode 'dbo.zf_sp_web_preauthTemp_query','unchained'
go
IF OBJECT_ID('dbo.zf_sp_web_preauthTemp_query') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.zf_sp_web_preauthTemp_query >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.zf_sp_web_preauthTemp_query >>>'
go
------解决方案--------------------
报什么错误？
写的详细点~
------解决方案--------------------
------解决方案--------------------
给你一个存储过程参考
- SQL code
--导入人力资源数据存储过程，用于定期比对人力资源系统人员表(hrmsemp_info)和本系统人员表(t_hremp_info_bas)中
--的人员数据，将发生变化的人员信息，存入临时数据表(imp_emp_info)中，并将导入日期记录到数据表中(t_interface_data_bas)
CREATE PROCEDURE dbo.import_emp_info
@o_err int OUTPUT
AS
BEGIN
declare
@nowtime varchar(100), --用来获取执行时间
@last_date varchar(100) --上次执行时间
--truncate table imp_emp_info
select @nowtime = getdate()
print @nowtime
print '开始导入数据......'
select @last_date = exe_date from t_interface_data_bas --取得上次导入日期
print 'last date'
print @last_date
select @o_err=@@error
if @o_err<>0
begin
return @o_err
end
if(@last_date is null)
begin
--首次导入，将数据进行转换后导入人力信息表中
insert into t_hremp_info_bas(emp_id, emp_code, name, sex, nation, emp_property, work_form, id_card, birthday,
service_start_date, inorg_start_date, degree, service_years, service_years_alter, org_id, dept_id,
admin_official, prof_official, prof_promote_rank, official_rank_salary, official_date, assess_result,
emp_class, alter_mode) select EMP_ID, EMP_CODE, NAME, SEX, NATION, EMP_PROPERTY, WORK_FORM, ID_CARD, BIRTHDAY,
SERVICE_START_DATE, INORG_START_DATE,
case when DEGREE_NAME like '1%' or DEGREE_NAME like '2%' then '2'
when DEGREE_NAME like '3%' then '3'
when DEGREE_NAME like '4%' then '4'
else DEGREE_NAME end,
SERVICE_YEARS, SERVICE_YEARS_ALTER, START_ORG_ID, LOCAL_OFFICE, ADMIN_OFFICIAL,
case when PROF_OFFICIAL not like 'A0%' then null else PROF_OFFICIAL end,
case when PROF_PROMOTE_RANK in ('01', '02') then '01' else PROF_PROMOTE_RANK end,
OFFICIAL_RANK_SALARY, OFFICIAL_DATE, ASSESS_RESULT,
case when EMP_CLASS in ('01', '02', '03', '11', '12', '13') then '03'
when EMP_CLASS = '32' then '31'
when ALTER_MODE in ('0203', '0204', '0205', '0206') then '02'
else EMP_CLASS end ,
ALTER_MODE from hrmsemp_info where EMP_CLASS not in ('36', '37')
select @o_err=@@error
if @o_err<>0
begin
return @o_err
end
end -- end of if
else --后续增量导入， 存入imp_emp_info中，等待用户确认操作
begin
print 'execute else '
delete from imp_emp_info
insert into imp_emp_info(emp_id, emp_code, name, sex, nation, emp_property, work_form, id_card, birthday,
service_start_date, inorg_start_date, degree, service_years, service_years_alter, org_id, dept_id,
admin_official, prof_official, prof_promote_rank, official_rank_salary, official_date, assess_result,
emp_class, alter_mode) select EMP_ID, EMP_CODE, NAME, SEX, NATION, EMP_PROPERTY, WORK_FORM, ID_CARD, BIRTHDAY,
SERVICE_START_DATE, INORG_START_DATE,
case when DEGREE_NAME like '1%' or DEGREE_NAME like '2%' then '2' --学历转换
when DEGREE_NAME like '3%' then '3'
when DEGREE_NAME like '4%' then '4'
else DEGREE_NAME end,
SERVICE_YEARS, SERVICE_YEARS_ALTER, START_ORG_ID, LOCAL_OFFICE, ADMIN_OFFICIAL,
case when PROF_OFFICIAL not like 'A0%' then null else PROF_OFFICIAL end, --工人专技职务
case when PROF_PROMOTE_RANK in ('01', '02') then '01' else PROF_PROMOTE_RANK end, --专技工资职务
OFFICIAL_RANK_SALARY, OFFICIAL_DATE, ASSESS_RESULT,
case when EMP_CLASS in ('01', '02', '03', '11', '12', '13') then '03' --人员类别
when EMP_CLASS = '32' then '31'
when ALTER_MODE in ('0203', '0204', '0205', '0206') then '02'
else EMP_CLASS end ,
ALTER_MODE from hrmsemp_info where OP_DATE > @last_date
select @o_err=@@error
if @o_err<>0
begin
return @o_err
end
end
--更新操作时间记录表，记录本次导入时间
if not exists (select id from t_interface_data_bas) insert into t_interface_data_bas(id, exe_date) values(1, convert(datetime, @nowtime, 110))
else update t_interface_data_bas set exe_date = convert(datetime, @nowtime, 110)
end