I am executing a stored procedure with below set of sql statements in the following order but getting this weird error : "Column name or number of supplied values does not match table definition."
I am having a stage table "staging_Table" which is getting loaded with incoming data files and doesn't have given columns "id","Name","dob","age_diffrence";
staging_Table_1 has same columns as staging_Table including four extra columns "id","Name","dob","age_diffrence" which is being calculated using CTE.
My stored procedure has given set of statements and are defined in following order:
--Dropping below four extra columns from staging_Table_1 so that it can be loaded from initial staging_Table.
Alter Table staging_Table_1
Drop Column "id","Name","dob","age_diffrence";
--now both the insert and select table has same set of columns
insert into staging_Table_1 select * from staging_Table;
--creating a backup and truncate the staging_Table_1
select * into staging_Table_2 from staging_Table_1;
Truncate Table staging_Table_1;
--Adding extra columns to staging_Table_1
Alter Table staging_Table_1
Add [id] [varchar] (20),[Name] [varchar] (100),[DOB] [Date],[Age_diffrence] [int];
with reporting_group_id as
(
select a.*,b.acc_num as id
from staging_Table_2 a left join (select distinct id,acct_num from sometable) as b
on a.id=b.id
)
,reporting_group_name as
(
select a.*,b.group_name as Name
from reporting_group_id a left join from sometable b
on a.id=b.id
)
,reporting_group_age as
(
select a.*,b.dob as dob, datediff(year,b.dob,b.start_date) -
( case when dateadd(year, datediff(year,b.dob,b.start_date),b.b.dob)
> b.start_date then 1 else 0 end
) as age_diffrence
from reporting_group_name a left join(select dob,start_date from some table)b
on a.id=b.id
)
Insert into staging_table_1
select * from reporting_group_age;
end
)
I am not sure what's happening here though both the insert and select has same column structures and are executing fine as individually.
Update-: I tried different approach as listing of too many columns on select and insert was making stored procedure code lengthy. since it has been marked as duplicate so cannot answer it. Updating the question with solution works in my case. I break my code in two stored procedures and replaced CTE with temp tables.
Sub Stored procedure code:
use somedb
GO
Create procedure [dbo].[sp_group_1]
as
begin
Alter Table staging_Table_1
Drop Column "id","Name","dob","age_diffrence";
insert into staging_Table_1 select * from staging_Table;
select * into staging_Table_2 from staging_Table_1;
Truncate Table staging_Table_1;
Alter Table staging_Table_1
Add [id] [varchar] (20),[Name] [varchar] (100),[DOB] [Date],[Age_diffrence] [int];
end
Main Stored procedure code:
use somedb;
GO
create procedure [dbo].[sp_group_2]
as
begin
Exec [dbo].[sp_group_1];
select * into #reporting_group_id from
(
select a.*,b.acc_num as id
from staging_Table_2 a left join (select distinct id,acct_num from sometable) as b
on a.id=b.id
) as M
select * into #reporting_group_name from
(
select a.*,b.group_name as Name
from #reporting_group_id a left join from sometable b
on a.id=b.id
) as M
select * into #reporting_group_age from
(
select a.*,b.dob as dob, datediff(year,b.dob,b.start_date) -
( case when dateadd(year, datediff(year,b.dob,b.start_date),b.b.dob)
> b.start_date then 1 else 0 end
) as age_diffrence
from #reporting_group_name a left join(select dob,start_date from sometable)b
on a.id=b.id
)
)as M
Insert into staging_table_1
select * from #reporting_group_age;
end
