比如 现有 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 OUTPUTAS 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