Importing only rows with column data in specific columns.

Question

Importing only rows with column data in specific columns.

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246697 More actions · Answer 1

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:

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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 SSC Guru Points: 442467 More actions · Answer 3

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.

SQL Padawan Right there with Babe Points: 742 More actions · Answer 4

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 SSCoach Points: 16106 More actions · Answer 5

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.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 6

Greg Snidow (9/10/2012)
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.

Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
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.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?

I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 8

Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
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.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.

I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Greg Snidow SSCoach Points: 16106 More actions · Answer 9

ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
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.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?

Probably the main reason, albeit not a valid one, is, similar to what Lynn said, I've never used PIVOT/UNPIVOT, I just happened to help someone with PIVOT on another forum a couple of days ago, and this question seemed like a good opportunity to try UNPIVOT. Other than that I don't know anything about it from a performance perspective. Sounds like a good opportunity for a million row test. Are there any indexes on the table in question?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

ScottPletcher (9/10/2012)
Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
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.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.
I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.

And that is exactly why you should run a million row test. Prove that UNPIVOT is the better solution. A developer must know, not guess. Hmmm, where have I heard that before?

ScottPletcher SSC Guru Points: 100949 More actions · Answer 11

Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Lynn Pettis (9/10/2012)
ScottPletcher (9/10/2012)
Greg Snidow (9/10/2012)
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.
Why? Is there any advantage to that method, which requires multiple scans of the table rather than a single one for UNPIVOT?
I would say that there is an opportunity for another test, wouldn't you? Perhaps you will take this one as I still have one to perform.
I don't see any need for a test. One full table scan per column can't possibly compete with a single table scan for everything.
And that is exactly why you should run a million row test. Prove that UNPIVOT is the better solution. A developer must know, not guess. Hmmm, where have I heard that before?

So does one also need to run tests to prove that:

SELECT a, b, c, d, e FROM dbo.table1

is more efficient than:

SELECT a, null, null, null, null FROM dbo.table1 UNION ALL

SELECT null, b, null, null, null FROM dbo.table1 UNION ALL

SELECT null, null, c, null, null FROM dbo.table1 UNION ALL

SELECT null, null, null, d, null FROM dbo.table1 UNION ALL

SELECT null, null, null, null, e FROM dbo.table1

??

Because that's a roughly equivalent comparison.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Greg Snidow SSCoach Points: 16106 More actions · Answer 12

ScottPletcher (9/10/2012)
So does one also need to run tests to prove that:
SELECT a, b, c, d, e FROM dbo.table1
is more efficient than:
SELECT a, null, null, null, null FROM dbo.table1 UNION ALL
SELECT null, b, null, null, null FROM dbo.table1 UNION ALL
SELECT null, null, c, null, null FROM dbo.table1 UNION ALL
SELECT null, null, null, d, null FROM dbo.table1 UNION ALL
SELECT null, null, null, null, e FROM dbo.table1
??
Because that's a roughly equivalent comparison.

Maybe. All I'm saying is that I don't know anything about PIVOT/UNPIVOT. Although I have not tested it, it might only be doing one table scan, but what else is it doing? It could be you are absolutely correct, but were I the OP, I would certainly want to test it for myself.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 13

I'm not sure about performance from Lynn's solution against UNPIVOT.

But there's another solution in here that has been proved to be better than UNPIVOT by Dwain in this article.

http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Could that be enough? Or someone is willing to make a complete test?

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 SSC Guru Points: 442467 More actions · Answer 14

Set up a quick test here as well:

UNION method:

Elapsed Time (ms) 1339

UNPIVOT method:

Elapsed Time (ms) 1181

Ran it several times with similar results. Sorry, didn't set up a test for Dwain Camps method.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

Something changed on my VM, but not sure what because I didn't change anything. All of a sudden this is the normal results:

UNION method:

Elapsed Time (ms) 2665

UNPIVOT method:

Elapsed Time (ms) 1257