Using While Loop to Union Selects?
in [Programming SQL]
|
Prev: Fastest way to move lots of data between databases on same ser
Next: Finding all table and fields that are involved in a one to many relation (on many side) with a given field
From: SnapDive on 11 Jun 2010 10:58 With SQL Server 2008, I have a single-col table variable containing n varchar values, each with something that will go into a where clause. For example, if I had 5 rows in the table, I would need 5 select statements where MyCol=next value. How could I write a while loop to pull the next available row from the table var, issue a select, and union all of the results into a single set? Is while the most expedient tool? Should I go cursor in that case? CTE? Thanks.
From: Erland Sommarskog on 11 Jun 2010 18:17 SnapDive (SnapDive(a)community.nospam) writes: > With SQL Server 2008, I have a single-col table variable containing n > varchar values, each with something that will go into a where clause. > For example, if I had 5 rows in the table, I would need 5 select > statements where MyCol=next value. > > How could I write a while loop to pull the next available row from the > table var, issue a select, and union all of the results into a single > set? > > Is while the most expedient tool? Should I go cursor in that case? > CTE? Why not just do: SELECT ... FROM thattable a WHERE EXISTS (SELECT * FROM @tablevar b WHERE a.MyCol = b.nextvalue) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: --CELKO-- on 12 Jun 2010 15:28
>> With SQL Server 2008, I have a single-col table variable containing VARCHAR values, each with something that will go into a WHERE clause.<< Please follow basic netiquette and post DDL. From your narrative, is this what you meant to post? CREATE TABLE Foobar (whacko_txt VARCHAR (25) NOT NULL PRIMARY KEY); Notice that since you have only one column, it has to be the key. This is basic RDBMS. And what did you mean by something that will go into a WHERE clause!! Are you putting code into a text column in a database? Ghod! I hope not! >> For example, if I had 5 rows in the table, I would need five SELECT statements with WHERE my_col = next_value. << You really have absolutely no idea what you are doing. SQL and RDBMS are based on sets. Sets are not ordered, so there is no concept of a next; that is a sequential file concept. Magnetic tapes? Punch cards? 1950's computing systems? >> How could I write a while loop to pull the next available row from the table var, issue a select, and union all of the results into a single set? << LOOP!!?? In SQL that is like raping a nun in church (is that overly dramatic enough? I love excess when I am on a roll.) UNION results? We write ONE --repeat ONE-- query that gives us the data we want. This is NOT a tape file where we build results in steps. SQL is a set-oriented language, so we do things on the entire set. We do not loop one thing at a time. We seven-times never mix data, metadata and code together. >> Is while the most expedient tool? Should I go cursor in that case? CTE? << First, read a book on basics! Since you don't know anything about RDBMS, then get a copy of the simplest intro book I know -- http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905 If you want a simple introduction to thinking in sets instead of sequential file structures,look at: http://sqluniversity.net/media.php?mfile=ThinkingInSets.rm&pid=57 |