Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I need assistance using a query to populate missing data

Posted on 2014-02-11
4
Medium Priority
?
386 Views
Last Modified: 2014-02-24
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
0
Comment
Question by:mainrotor
  • 2
4 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 39852300
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.
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39852585
How do you identify values for different columns (Record_NO, Trans_NO, Amount and Date) from you single column (RAW_DATA) table?
0
 

Author Comment

by:mainrotor
ID: 39854882
Harish Varghese,
I identify the values using the Record_NO column.
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 2000 total points
ID: 39855526
I think I did not understand fully. You have a table with single column named RAW_DATA. And the values for this column will be:
Record_NO      Trans_NO      Amount      Date
1000091            80012            10.00      1/5/2014
                        15.00      1/8/2014
                        18.00      1/9/2014

Open in new window

So I assume all values in row#2 above will go into single column RAW_DATA. And same case with all other rows. If that is the case how do you identify that 1000091 is Record_NO and 80012 is Trans_No?

Okay, and if you have hanedled your data and somehow brought to a table with four columns as below:
Record_NO  Trans_NO  Amount     Date
1000091    80012      10.00     1/5/2014
NULL       NULL       15.00     1/8/2014
NULL       NULL       18.00     1/9/2014
1000095    80145      10.00     1/2/2014
NULL       NULL       25.00     1/7/2014
NULL       NULL       35.00     1/8/2014
NULL       NULL       15.00     1/9/2014
1000155    80806      10.00     1/5/2014
NULL       NULL       12.00     1/8/2014
NULL       NULL       17.00     1/9/2014

Open in new window

Then you can use below statement to update missing data, assuming your table name is TransData:
declare @PrevRecordNo int, @PrevTransNo int
Update T
Set @PrevRecordNo = case when T.Record_No Is not null then T.Record_No else @PrevRecordNo end,
	@PrevTransNo = case when T.Trans_No Is not null then T.Trans_No else @PrevTransNo end,
	T.Record_No = case when T.Record_No Is not null then T.Record_No else @PrevRecordNo end,
	T.Trans_No = case when T.Trans_No Is not null then T.Trans_No else @PrevTransNo end
From TransData T

Open in new window

Note:

For this solution to work, retrieval from this table should display the data in the same order as you want - all records with same Record_No and Trans_No should come together. This will happen if you do not have any indexes or primary key on your table (SELECT will retrieve in the order you inserted). OR you can add a column, say SerialNumber, as Primary Key and set it as IDENTITY, as below:
alter table TransData add SerialNumber int identity Primary Key

Open in new window

-Harish
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question