Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing only rows with column data in specific columns.


Importing only rows with column data in specific columns.

Author
Message
robert.baird 2778
robert.baird 2778
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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;




Cool
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)
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
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.
robert.baird 2778
robert.baird 2778
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8367 Visits: 19499
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8552 Visits: 18142
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
robert.baird 2778
robert.baird 2778
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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
robert.baird 2778
robert.baird 2778
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8552 Visits: 18142
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search