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 Friday, September 7, 2012 9:54 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 20,862, Visits: 32,893
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.




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 #1356081
Posted Friday, September 7, 2012 10:14 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 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
Post #1356088
Posted Friday, September 7, 2012 10:31 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 20,862, Visits: 32,893
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;





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 #1356102
Posted Friday, September 7, 2012 10:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 4,046, Visits: 9,204
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1356105
Posted Monday, September 10, 2012 9:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1356835
Posted Monday, September 10, 2012 9:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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!



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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1356839
Posted Monday, September 10, 2012 9:55 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 20,862, Visits: 32,893
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.



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 #1356852
Posted Monday, September 10, 2012 9:57 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 20,862, Visits: 32,893
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!


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.




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 #1356855
Posted Monday, September 10, 2012 12:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:37 AM
Points: 44, Visits: 1,089
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)

Post #1356954
Posted Monday, September 10, 2012 4:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 11:41 AM
Points: 1,564, Visits: 2,397
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.
Post #1357046
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse