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
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: 24179 Visits: 37948
robert.baird 2778 (9/7/2012)
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



Tell you what, it wouldn't be frustrating if you provided all the information up front instead of providing things piecemeal.

The best place to start, please read the first article I reference below in my signature block about asking for help. It gives a step by step approach on what and how to post the information needed to provide you with the best possible answers. I haven't read it in a while myself, so I think it may leave out that we also need to know the expected results based on the sample data you need to post.

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)
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 guys.... i worked it out... now, if i were to turn the select into an insert statement?

with basedata (
name,
addy,
ph,
grade
) as (
select <--PUT INSERTS HERE?
name,
addy,
ph,
grade1
from
sourcetable
union all
select
name,
addy,
ph,
grade2
from
sourcetable
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: 24179 Visits: 37948
Nope, more 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
)
INSERT INTO dest_table(
name,
addy,
ph,
grade
)
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)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8506 Visits: 18115
No, you put it outside the WITH statement.
Here's the reference to the syntax:
http://msdn.microsoft.com/en-us/library/ms174335(v=sql.105).aspx
And here's is the example:
http://msdn.microsoft.com/en-us/library/dd776381(v=sql.105).aspx#OtherTables


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
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3943 Visits: 6674
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8324 Visits: 19470
Lynn Pettis (9/7/2012)
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.


Eek, I apologise for this!Blush


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.
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: 24179 Visits: 37948
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.


Probably, but I am still new at writing pivot/unpivot queries so I will leave that one to others.

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)
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: 24179 Visits: 37948
Phil Parkin (9/10/2012)
Lynn Pettis (9/7/2012)
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.


Eek, I apologise for this!Blush


Like I said, not a problem. It happens regularly with me, in fact it happened this weekend before a soccer game I was officiating. One of the other refs though we were waiting for a lady to join us while the third official was working the game before ours.

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)
SQL Padawan
SQL Padawan
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 1162
Why not start with something like this to build your query or inserts around...?
SELECT name,addy,ph,subject,grade
FROM #test CROSS APPLY (VALUES('subject 1',grade1),('subject 2',grade2),('subject 3',grade3),('subject 4',grade4),('subject 5',grade5)) AS x(subject,grade)


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
ScottPletcher (9/10/2012)
The UNPIVOT (one table scan) looks much better to me than fully scanning the table once for every grade column.


Yep, as I said, I put that out there for academic purposes only, as it can be useful. However, for this situation, I would probably go with Lynn's solution.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
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