Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Importing only rows with column data in specific columns. Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 1:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 4:04 PM
Points: 30, Visits: 183
Hi all -
I have an interesting scenario that i would like some input on. I have a requirement to import data from one system into another. The source table is not normalized to the destination.
The first (source) table will have a few pieces of common info, and the last several columns will have data in various columns. As an example, each row looks as follows:

name, addy, ph, grade1, grade2, grade3, grade4, grade5
Dan, 123 Main, 555-5555, A, NULL, C+, NULL, NULL
...

Now, in the destination table, the import needs to omit the records with any NULL grades. As an example (based on the above):

name, addy, ph, grade
Dan, 123 Main, 555-5555, A
Dan, 123 Main, 555-5555, C+
...

My question I guess is, how is the best way to do this? Ultimately, I will want this in an integration serv package. My first thought was to pre-process the records on import with a CASE / IMPORT type solution, but not sure if that will work using case...

Any suggestions would be appreciated.

Thanks,
Dan
Post #1354133
Posted Tuesday, September 4, 2012 1:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
Should do something like this:


with basedata (
name,
addy,
ph,
grade
) as (
select
name,
addy,
ph,
grade1
from
sourcetable
union all
select
name,
addy,
ph,
grade2
from
sourcetable
union all
select
name,
addy,
ph,
grade3
from
sourcetable
union all
select
name,
addy,
ph,
grade4
from
sourcetable
union all
select
name,
addy,
ph,
grade5
from
sourcetable
)
select
name,
addy,
ph,
grade
from
BaseData
where
grade is not null;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1354138
Posted Tuesday, September 4, 2012 3:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:36 AM
Points: 1,565, Visits: 2,378
Dan, I think Lynn's solution is probably better, but just for academic purposes, you could try something like this...

IF OBJECT_ID('tempdb..#test','u') IS NOT NULL
DROP TABLE #test;
SELECT
'Dan' AS [name],
'123 Main' AS addy,
'555-5555' AS ph,
CAST('A' AS VARCHAR(20)) AS grade1,
CAST(NULL AS VARCHAR(20)) AS grade2,
CAST('C+' AS VARCHAR(20)) AS grade3,
CAST(NULL AS VARCHAR(20)) AS grade4,
CAST(NULL AS VARCHAR(20)) AS grade5
INTO #test;

SELECT
[name],addy,ph,grade
FROM
(
SELECT
[name],
addy,
ph,
grade1,
grade2,
grade3,
grade4,
grade5
FROM #test) t
UNPIVOT
(
grade
FOR x IN ([grade1],[grade2],[grade3],[grade4],[grade5])
) AS unpvt
WHERE grade IS NOT NULL



Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Post #1354220
Posted Friday, September 7, 2012 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 4:04 PM
Points: 30, Visits: 183
Thanks! I understand the unions and can see how it puts it together in a serial fashion, not sure on the 'with basedata' portion though.... could you elaborate on that just a bit?
Thanks
Dan
Post #1355994
Posted Friday, September 7, 2012 8:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 4,977, Visits: 11,670
Lynn's solution is the way to go.

In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.

If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1356007
Posted Friday, September 7, 2012 8:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 3,359, Visits: 7,271
To understand the WITH basedata, you should read this:
http://msdn.microsoft.com/en-us/library/ms175972(v=sql.105).aspx
and this:
http://msdn.microsoft.com/en-us/library/ms190766(v=SQL.105).aspx



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356008
Posted Friday, September 7, 2012 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 4:04 PM
Points: 30, Visits: 183
Okay... i read through them, and have a point or two of confusion. In my example above, my source has 'grade1', 'grade2', etc.... however it seems that she is referencing a column that doesn't exist - 'grade'... and after i convert this to my scenerio, it states that the 'grade column doesn't exist and errors out.
Also, the very last portion (select outside the parents) seems to be missing the FROM object reference...
Do I need to add the 'grade' column somewhere, in the queried table perhaps?

Dan
Post #1356025
Posted Friday, September 7, 2012 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 19, 2012 4:04 PM
Points: 30, Visits: 183
ok.... i got it... the object referenced at the end is the basedata. so looking at the result set, i see all of the values.... but that leaves me with another issue... i see the 'grades' but not the subjects... this is frustrating...lol

Post #1356046
Posted Friday, September 7, 2012 9:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 3,359, Visits: 7,271
You never mentioned the subjects.
You have to add them to the query that constructs the basedata (the selects with union all).

with basedata (
name,
addy,
ph,
subject,
grade
) as (
select
name,
addy,
ph,
'Subject 1',
grade1
from
sourcetable
union all
select
name,
addy,
ph,
'Subject 2',
grade2
from
sourcetable
union all
...




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356073
Posted Friday, September 7, 2012 9:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
Phil Parkin (9/7/2012)
Lynn's solution is the way to go.

In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.

If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.


Should read:

In her his solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.


Not a biggie, happens rather frequently.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356079
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse