Target laod when having diffrent number of column in source tables

Question

Target laod when having diffrent number of column in source tables

vipin_jha123

Hall of Fame

Points: 3085
More actions
December 15, 2017 at 5:17 am

#409864

Hi ,
In my scenario , I am having last 24 month data in 24 diffrent table .
In my target table there are 20 column , but in source table column number id variant .
In some source table column count is 19 in some 17 and in some 18 .
I have to load all 24 table for history load in my target table .
I am thinking to load using stored procedure , but dont know hows to handle it with diffrent number of source column.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246978 More actions · Answer 1

vipin_jha123 - Friday, December 15, 2017 5:17 AM
Hi ,
In my scenario , I am having last 24 month data in 24 diffrent table .
In my target table there are 20 column , but in source table column number id variant .
In some source table column count is 19 in some 17 and in some 18 .
I have to load all 24 table for history load in my target table .
I am thinking to load using stored procedure , but dont know hows to handle it with diffrent number of source column.

Handle it in code.
And that's about as specific as I can be, since you did not provide DDL, consumable sample data and desired output.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 2

You can use below table script and can insert any test value for test
cREATE TABLE TAB1
(

URN    bigint,
eno    varchar(MAX),
Schoolname    varchar(MAX),
phase    varchar(MAX),
)

cREATE TABLE TAB2
(

URN    bigint,
eno    varchar(MAX),
Schoolname    varchar(MAX),
Region    varchar(MAX)
)

cREATE TABLE TABTARGET
(

URN    bigint,
eno    varchar(MAX),
Schoolname    varchar(MAX),
dphase    varchar(MAX),
Region    varchar(MAX)
)

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

vipin_jha123 - Friday, December 15, 2017 6:10 AM
You can use below table script and can insert any test value for test
cREATE TABLE TAB1
(
URN    bigint,
eno    varchar(MAX),
Schoolname    varchar(MAX),
phase    varchar(MAX),
)
cREATE TABLE TAB2
(
URN    bigint,
eno    varchar(MAX),
Schoolname    varchar(MAX),
Region    varchar(MAX)
)
cREATE TABLE TABTARGET
(
URN    bigint,
eno    varchar(MAX),
Schoolname    varchar(MAX),
dphase    varchar(MAX),
Region    varchar(MAX)
)

What is the goal here? Load all of TAB1 into TABTARGET, then load all of TAB2 so you have nulls is some rows for phase and region? Join TAB1 & TAB2 then insert the product into TABTARGET? If the latter what is the relationship between TAB1 & TAB2