How to connect two tables

Transact-SQL
1

Hello Guys,
Can anyone help me with this...
I got two source files A & B for generating ETL Process....
A&B got same pk,A got 10 fields and B got 2 fields,somehow i want to get these two source files into one table ,how can i get do that?

2

It lloks like you need a JOIN.
Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.

3

Thanks spaghettidba for your reply

My main question is related to ETL process not querying and fetching data,

SOURCE FILES.......................
A
id
deal_id
cust_id
booking_ref
.
.
abc

2nd source file
B

id
deal_id
amount

Now i want to load this source files through ETL
SO EXTR A & EXTR B,i already made
i certaiinly want this to go in table C
(Output)
C
id
deal_id
cust_id
booking_ref
.
.
abc
amount(from B)

i have added this column in table already but can't process in ETL,as it overwriting,
Can you help if you got any alternative?

4

I would pull each file intoa Staging Table, designed to "match" the layout of the file. We make all columns VARCHAR(8000) so that whatever rubbish is in the files will be imported :slight_smile: and we add columns for ErrorNo and ErrorMsg. We then import the file into the staging table and then UPDATE the staging table to set the ErrorNo and ErrorMsg for any row that has goofy data - e.g. a column is supposed to be a Number or a Date but is invalid, or a required column is empty

Then once we have the files imported we then update the actual tables. You can choose to abort the import if there are any rows in Staging Tables with ErrorNo, or you can just process the rows that do NOT have an ErrorNo (i.e. "process clean rows only"). You can provide a report for the users to display which rows have Goofy data, and then the User can clean up the source data.

When processing the data you can JOIN the tables for Staging_A and Staging_B to get the data you want:

INSERT INTO MyTable_C
(
    id, deal_id, cust_id, booking_ref, ..., , abc, amount
)
SELECT A.id, A.deal_id, A.cust_id, A.booking_ref, ..., , A.abc,
       B.amount
FROM Staging_A AS A
     LEFT OUTER JOIN Staging_A AS B
         ON B.id = I.id