Multiple tables to 1 record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple tables to 1 record

Author

Topic

skinto28
Starting Member

6 Posts

Posted - 2010-02-11 : 06:43:09

Hello to all!!

Got a quick question with regards to a buidling a flat table from multiple tables. I'm using .net and sql server 2005.

I have a member table that will have thousands of records. I need to do a seach for particular fields, and from this i will know if there are corresponding records in the other tables.

Now my problem is that in some of these other tables there are multiple records per parent record. I have been asked to build all values from all tables into one flat table (1 record per member) for completed records.

I know i can do this by looping through the records in .net and hitting multiple stored procedures for each record (per table) but i know this is not performance friendly.

Is there an easy way to do this in sql server?? So in short what i need to do is:

Do an insert into the flat table from my main table (parent table)

Go through the other tables and run an update statement on the inserted values from above, connecting these updates using the foreign key

The update statement will also need to take into account that multiple records per member will be in some tables and i will need to build these into 1 row/record.

Which is the best performance based solution in the scenario?

Apreciate any feedback

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 06:46:53

you write a single query which returns data from master table as well as aggregated measures from child to get one row per record and then transfer it to flat file using export import wizard or bcp or similar methods

if you need more detailed help post some sample data to illustrate your exact scenario

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skinto28
Starting Member

6 Posts

Posted - 2010-02-11 : 07:31:17

Ok heres the query i use to find out that a record has data in each of the tables in .net

string queryString2 = "SELECT ( SELECT COUNT(*) FROM ConsentForms where CCHINumber='" + reader["PCHINumber"].ToString() + "' and CAdmissionDate='" + reader["PAdmissionAdmitted"].ToString() + "' and CAgreeYesNo=1) AS AdmissionFound,( SELECT COUNT(*) FROM ConsentForms where CCHINumber='" + reader["PCHINumber"].ToString() + "' and CDischargeDate='" + reader["PDischarge_date"].ToString() + "' and CAgreeYesNo=1) AS DischargeFound,( SELECT COUNT(*) FROM RiskAssessmentForms where RCHINumber='" + reader["PCHINumber"].ToString() + "' and RAdmission_date='" + reader["PAdmissionAdmitted"].ToString() + "') AS RiskAssessFound," +
"( SELECT COUNT(*) FROM ViralLabData where VCHINumber='" + reader["PCHINumber"].ToString() + "' and VSwabDate=" + reader["PAdmission_SwabTaken"].ToString() + ") AS ViralLabFound,( SELECT COUNT(*) FROM ViralLabData where VCHINumber='" + reader["PCHINumber"].ToString() + "' and VSwabDate='" + reader["PAdmission_SwabTaken"].ToString() + "'" +
" and (LOWER(VMRADAX) LIKE 'm%' or LOWER(VMRADNS) LIKE 'm%' or LOWER(VMRADPE) LIKE 'm%' or LOWER(VMRADTS) LIKE 'm%' or LOWER(VMRADWS) LIKE 'm%' or LOWER(VMRBABROTH) LIKE 'm%' or LOWER(VMRBDBROTH) LIKE 'm%' or LOWER(VMRDIAX) LIKE 'm%' or LOWER(VMRDINS) LIKE 'm%' or LOWER(VMRDIPE) LIKE 'm%' or LOWER(VMRDITS) LIKE 'm%' or LOWER(VMRDIWS) LIKE 'm%')) AS MRSAFound," +
"( SELECT COUNT(*) FROM ReferenceLabData where RefCHINumber='" + reader["PCHINumber"].ToString() + "' and RefSwabDate=" + reader["PAdmission_SwabTaken"].ToString() + ") AS ReferenceLabFound";

Please excuse the formatting of this as there's no easy way to make it look pretty it seems!!

As you can see in .net i'm rotating through a reader for each record in the parent table and using these values i'm running a count on each table to make sure a record exists for the parent record.

The reader values are the foreign keys that links these tables together.

The table that holds multiple records per parent record is virallabdata (marked green. don't worry about the like statement as this is used to find out if there should be a record in the reference lab table)

So in short the flat table statement that i need to build will be identical to the data retrieved from the above statement except that it will involve an update statement and actual values instead of returning counts.

You can assume i have already inserted the parent records using the reader above as i know a record exists in each of these tables. The only part i need to do is to run an update statement using the above tables and values on these parent records in the flat table record now created. The tricky part is the viral lab data as i said there is multiple records per parent record.

Ta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 07:48:12

why do you use inline query? isnt it better to create a procedure and call it from .net?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skinto28
Starting Member

6 Posts

Posted - 2010-02-11 : 08:02:22

Yes i totally agree. I just done the above to get a list of counts and it doesn't take a performance hit. If all these counts are greater than 0 then i will call the sotred priocedure that will update the flat table with the values from thse tables. So in short i'll be doing this:

myCommand.CommandText = "usp_BuildFlatTable";
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters["ChiNumber"].Value = reader["PCHINumber"].ToString();
myCommand.Parameters["AdmissionAdmitted"].Value = reader["PAdmissionAdmitted"].ToString();
myCommand.Parameters["DischargeDate"].Value = reader["PDischarge_date"].ToString();
myCommand.Parameters["AdmissionSwabTaken"].Value = reader["PAdmission_SwabTaken"].ToString();

I can then do the simple updates needed for flat table using a select update statement using the above paramteres but my problem is how do i build multiple virallabdata records into one then run an update on the flat table??

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 08:32:06

[code]
SELECT AdmissionFound,DischargeFound,ViralLabFound,MRSAFound,RiskAssessFound,ReferenceLabFound
FROM
(
SELECT COUNT(CASE WHEN CAdmissionDate=@PAdmissionAdmitted THEN 1 ELSE NULL END) AS AdmissionFound,
COUNT(CASE WHEN CDischargeDate=@PDischarge_date THEN 1 ELSE NULL END) AS DischargeFound
FROM ConsentForms
where CCHINumber=@PCHINumber and CAgreeYesNo=1
)t
CROSS JOIN
(
SELECT COUNT(*) AS ViralLabFound,
COUNT(CASE WHEN (LOWER(VMRADAX) LIKE 'm%' or LOWER(VMRADNS) LIKE 'm%' or LOWER(VMRADPE) LIKE 'm%' or LOWER(VMRADTS) LIKE 'm%' or LOWER(VMRADWS) LIKE 'm%' or LOWER(VMRBABROTH) LIKE 'm%' or LOWER(VMRBDBROTH) LIKE 'm%' or LOWER(VMRDIAX) LIKE 'm%' or LOWER(VMRDINS) LIKE 'm%' or LOWER(VMRDIPE) LIKE 'm%' or LOWER(VMRDITS) LIKE 'm%' or LOWER(VMRDIWS) LIKE 'm%') THEN 1 ELSE NULL END) AS MRSAFound
FROM ViralLabData where VCHINumber=@PCHINumber and VSwabDate=@PAdmission_SwabTaken
)u
CROSS JOIN
(
SELECT COUNT(*) AS RiskAssessFound FROM RiskAssessmentForms where RCHINumber=@PCHINumber and RAdmission_date=@PAdmissionAdmitted
)v

CROSS JOIN
(
SELECT COUNT(*) AS ReferenceLabFound FROM ReferenceLabData where RefCHINumber=@PCHINumber
and RefSwabDate=@PAdmission_SwabTaken
)w

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skinto28
Starting Member

6 Posts

Posted - 2010-02-11 : 09:27:17

Really appreciate your feedback on this Visakh. What i was looking to achieve after the count was the actual update using the data and not the counts as i was just using the counts to get me to this stage.

Below i've attached a few sample records from my virallabdata table

ViralID, Code, DateEntered, PCHINumber, DOB, Department

21 AA 04/02/2010 13:14:54 123456789 14/12/1990 00:00:00 XYXYXYXY1
22 AA 04/02/2010 13:14:54 123456789 14/12/1990 00:00:00 XYXYXYXY2
23 AA 04/02/2010 13:14:54 123456789 14/12/1990 00:00:00 XYXYXYXY3
24 AA 04/02/2010 13:14:54 123456789 14/12/1990 00:00:00 XYXYXYXY4
25 AA 04/02/2010 13:14:54 123456789 14/12/1990 00:00:00 XYXYXYXY5

So at this stage my flat table looks like:

PCHINumber, Department1, Department2, Department3, Department4, Department5

123456789, Null, Null, Null, Null, Null

How do i rotate through the records in the virallabdata table and then build them into the flat table using an update so it looks like

123456789, XYXYXYXY1, XYXYXYXY2, XYXYXYXY3, XYXYXYXY4, XYXYXYXY5

Hope this makes sense

Ta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 09:32:33

[code]UPDATE f
SET f.Department1= v.Department1,
f.Department2= v.Department2,
f.Department3= v.Department3,
f.Department4= v.Department4,
f.Department5= v.Department5
FROM FlatTable f
INNER JOIN
(
SELECT PCHINumber,
MAX(CASE WHEN Seq=1 THEN Department ELSE NULL END) AS Department1,
MAX(CASE WHEN Seq=2 THEN Department ELSE NULL END) AS Department2,
MAX(CASE WHEN Seq=3 THEN Department ELSE NULL END) AS Department3,
MAX(CASE WHEN Seq=4 THEN Department ELSE NULL END) AS Department4,
MAX(CASE WHEN Seq=5 THEN Department ELSE NULL END) AS Department5
(SELECT ROW_NUMBER() OVER (PARTITION BY PCHINumber ORDER BY ViralID) AS Seq,* FROM virallabdata)t
GROUP BY PCHINumber
)v
ON v.PCHINumber=f.PCHINumber
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skinto28
Starting Member

6 Posts

Posted - 2010-02-11 : 09:36:30

Brilliant,

Really appreciate your help with this as it's had me stumped for a while the best way to do it.

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 09:40:44

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skinto28
Starting Member

6 Posts

Posted - 2010-02-11 : 10:11:04

Just been in contact with someone who maintains the live server and now been told they are using sql server 2000!!! Is there a row_number alternative to your statement that's not too much hassle to yourself?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 10:15:41

Try


UPDATE f
SET f.Department1= v.Department1,
f.Department2= v.Department2,
f.Department3= v.Department3,
f.Department4= v.Department4,
f.Department5= v.Department5
FROM FlatTable f
INNER JOIN 
(
SELECT PCHINumber,
MAX(CASE WHEN Seq=1 THEN Department ELSE NULL END) AS Department1,
MAX(CASE WHEN Seq=2 THEN Department ELSE NULL END) AS Department2,
MAX(CASE WHEN Seq=3 THEN Department ELSE NULL END) AS Department3,
MAX(CASE WHEN Seq=4 THEN Department ELSE NULL END) AS Department4,
MAX(CASE WHEN Seq=5 THEN Department ELSE NULL END) AS Department5
(SELECT (SELECT COUNT(*) FROM virallabdata WHERE PCHINumber=r. PCHINumber AND Department<>r.Department AND ViralID<r.ViralID) +1 AS Seq,* FROM virallabdata r )t
GROUP BY PCHINumber
)v
ON v.PCHINumber=f.PCHINumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources