asked on

I need assistance using a query to populate missing data

Hi Experts,
I have a file that I import into a SQL Server table with only one Column that I named RAW_DATA. Here is a sample of the data that gets imported:

Record_NO      Trans_NO      Amount      Date
1000091            80012            10.00      1/5/2014
                        15.00      1/8/2014
                        18.00      1/9/2014

1000095            80145            10.00      1/2/2014
                        25.00      1/7/2014
                        35.00      1/8/2014
                        15.00      1/9/2014

1000155            80806            10.00      1/5/2014
                        12.00      1/8/2014
                        17.00      1/9/2014

The data only shows the Record_NO, and Trans_NO value for the first row, but not for the consecutive rows that belong to the same Record_NO, and Trans_NO.
Is there a query or a function I can run so that it fills in the missing informatio so that the data in the table looks as follows:

Record_NO      Trans_NO      Amount      Date
1000091            80012            10.00      1/5/2014
1000091            80012            15.00      1/8/2014
1000091            80012            18.00      1/9/2014

1000095            80145            10.00      1/2/2014
1000095            80145            25.00      1/7/2014
1000095            80145            35.00      1/8/2014
1000095            80145            15.00      1/9/2014

1000155            80806            10.00      1/5/2014
1000155            80806            12.00      1/8/2014
1000155            80806            17.00      1/9/2014

Thank you very much in advance,
mrotor

sammySeltzer

This can be if and only if there is a primary key on both the excel you are importing from and the table you are importing the excel to.

Let's assume that you have primary key called RecordId on the excel that gets inserted into your table after the import, then this query below can help.

Let's call the table that data was imported to RealTable

First, create a staging table called yes, StagingTable.

Now, dump all the data from Excel into this stagingTable.

Then run the query:

INSERT INTO RealTable
            (RecordId
           ,Record_NO
           ,Trans_NO
           ,Amount
           ,Date)

SELECT     a2.RecordId
           ,a2.Record_NO
           ,a2.Trans_NO
           ,a2.Amount
           ,a2.Date

from StagingTable a2
where exists (
select a1.RecordId
FROM (
select RecordId from StagingTable
except
select RecordId from RealTable) a1 
where a2.RecordId = a1.RecordId)

Open in new window

This is the only way I know how.

Harish Varghese

How do you identify values for different columns (Record_NO, Trans_NO, Amount and Date) from you single column (RAW_DATA) table?

mainrotor

ASKER

Harish Varghese,
I identify the values using the Record_NO column.

ASKER CERTIFIED SOLUTION

Harish Varghese

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial