SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

ImJasonH: Apr 2, 2004; RAMALAMADINGDONG!

Factor Mystic posted:

Alright, this is making a lot more sense now with the example tables there. I got that I would need a Keyword and a Bookmark table, but I didn't know how to properly link to the two together. So each row of the Bookmark_Keyword table contains exactly one Keyword <-> Bookmark relation. Now I get it. Thank you

Just to generalize, this is how you do any N-to-N relation of anything. Tags<->Bookmarks, Students<->Classes, Books<->Customers, anything in which a bunch of Somethings can have a bunch of SomethingElses.

# ? Jun 24, 2008 08:20

Adbot: ADBOT LOVES YOU

# ? Apr 29, 2024 09:33

Bruce Hussein Daddy: Dec 26, 2005; I testify that there is none worthy of worship except God and I testify that Muhammad is the Messenger of God

I want to return Name, H1, H2 and JobID. I've tried Union and Select In and I don't know what I'm doing I'm stupid.

# ? Jun 26, 2008 13:26

Aredna: Mar 17, 2007; Nap Ghost

Bruce Hussein Daddy posted:

I want to return Name, H1, H2 and JobID. I've tried Union and Select In and I don't know what I'm doing I'm stupid.

If you don't have spaces in the field names just remove them below. If you don't have spaces in the names you don't need the square brackets around the field names either, but it won't hurt anything by leaving them.

code:

SELECT H.NAME,
       H.HOURS1,
       H.HOURS2,
       JOBS.[JOB ID]

FROM   H
       INNER JOIN R
         ON H.[ROW NUM] = R.[ROW NUM]
       INNER JOIN JOBS
         ON R.[JOB NUM] = JOBS.[JOB NUM]

# ? Jun 26, 2008 14:18

HIERARCHY OF WEEDZ: Aug 1, 2005

Jesus, I typed all this up, and then on a hunch I googled UNION, and there I was. gently caress. Well, here's the post for posterity.

Here's a MySQL question.

Say I have two tables, namely:

code:


+-------------+----------+
| column_a    | column_b |
+-------------+----------+
| 'some text' |      123 |
+-------------+----------+

+------------------+-------------+
| column_alpha     | column_beta |
+------------------+-------------+
| 'some more text' |         456 |
+------------------+-------------+

These tables have no relation to each other whatsoever, they just happen to have some columns that, while named differently, store the same types of data (e.g., column_a and column_alpha both are VARCHARS). Is there any way that I can run a SELECT that retrieves from both tables, returning a result set that looks something like this?

code:


+-------------------------+------------------------+
| new_varchar_column_name | new_number_column_name | <-- perhaps sorted by this column?
+-------------------------+------------------------+
|             'some text' |                    123 |
+-------------------------+------------------------+
|        'some more text' |                    456 |
+-------------------------+------------------------+

# ? Jun 26, 2008 15:00

Aredna: Mar 17, 2007; Nap Ghost

shopvac4christ posted:

Jesus, I typed all this up, and then on a hunch I googled UNION, and there I was. gently caress. Well, here's the post for posterity.
...

You should also look at the difference between UNION ALL and UNION as they will give you different result sets.

UNION ALL will return every row from both queries.

UNION will merge the results of the queries and if there are any duplicated rows between the unioned queries they will be printed only once.

# ? Jun 26, 2008 15:45

HIERARCHY OF WEEDZ: Aug 1, 2005

Aredna posted:

You should also look at the difference between UNION ALL and UNION as they will give you different result sets.

UNION ALL will return every row from both queries.

UNION will merge the results of the queries and if there are any duplicated rows between the unioned queries they will be printed only once.

Awesome, very good to know. Thanks!

# ? Jun 26, 2008 15:51

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Aredna posted:

You should also look at the difference between UNION ALL and UNION as they will give you different result sets.

UNION ALL will return every row from both queries.

UNION will merge the results of the queries and if there are any duplicated rows between the unioned queries they will be printed only once.

Just for further clarification, this means that, unless you specifically know that you want to discard duplicates you should use UNION ALL, since UNION forces the RDBMS to check for duplicates, even if there aren't any.

# ? Jun 26, 2008 16:41

Factor Mystic: Mar 20, 2006; Baby's First Post-Apocalyptic Fiction

Aredna posted:

If you don't have spaces in the field names just remove them below. If you don't have spaces in the names you don't need the square brackets around the field names either, but it won't hurt anything by leaving them.
code:
SELECT H.NAME,
       H.HOURS1,
       H.HOURS2,
       JOBS.[JOB ID]

FROM   H
       INNER JOIN R
         ON H.[ROW NUM] = R.[ROW NUM]
       INNER JOIN JOBS
         ON R.[JOB NUM] = JOBS.[JOB NUM]

In this case, is 'SELECT DISTINCT ...' the proper way to prevent duplicate rows in the result?

# ? Jun 26, 2008 17:07

chocojosh: Jun 9, 2007; D00D.

Jethro posted:

Just for further clarification, this means that, unless you specifically know that you want to discard duplicates you should use UNION ALL, since UNION forces the RDBMS to check for duplicates, even if there aren't any.

And for even more clarification:

UNION ALL is O(n) and UNION is O(n ^ 2) (n = number of records in both sets).

Why? Because UNION ALL simply takes both sets and pushes into a new table, an O(n) operation. UNION is O(n ^ 2) because for each record it goes to add, it needs to check all the previous records for a match (which takes 1 + 2 + 3 + ... + n - 1 = (n - 1)(n) / 2 operations).

# ? Jun 26, 2008 17:17

chocojosh: Jun 9, 2007; D00D.

Factor Mystic posted:

In this case, is 'SELECT DISTINCT ...' the proper way to prevent duplicate rows in the result?

Yes. http://www.w3schools.com/sql/sql_select.asp

I just did a simple test to reassure myself that DISTINCT applies to all columns in the select clause, which is does.

code:


create table #Temp (
  col1 varchar(10),
  col2 varchar(10))

insert into #Temp values ('0', '2')
insert into #Temp values ('1', '2')
insert into #Temp values ('1', '2')
insert into #Temp values ('1', '2')
insert into #Temp values ('1', '3')
insert into #Temp values ('1', '3')
insert into #Temp values ('2', '2')

select * from #Temp

select distinct col1, col2 from #Temp

Drop table #Temp

The select * returns:

code:

The select distinct returns:

code:

# ? Jun 26, 2008 17:23

Aredna: Mar 17, 2007; Nap Ghost

Factor Mystic posted:

In this case, is 'SELECT DISTINCT ...' the proper way to prevent duplicate rows in the result?

Chocojosh's post is a good example, but with that said you should always make sure you understand your data and know why you are getting duplicate rows before you decide to eliminate them. It could be a symptom of a large problem.

For this problem, it seems to me logically that ROWNUM and JOBNUM should both be unique fields in thier respective tables in H and JOBS. In table R it seems like the combination of ROWNUM and JOBNUM should be unique as well. If both of these are true then there wouldn't be any duplicate rows.

DISTINCT is great to remove extra rows as long as it's not used as a bandaid.

# ? Jun 26, 2008 19:28

Bruce Hussein Daddy: Dec 26, 2005; I testify that there is none worthy of worship except God and I testify that Muhammad is the Messenger of God

Aredna posted:

If you don't have spaces in the field names just remove them below. If you don't have spaces in the names you don't need the square brackets around the field names either, but it won't hurt anything by leaving them.
code:
SELECT H.NAME,
       H.HOURS1,
       H.HOURS2,
       JOBS.[JOB ID]

FROM   H
       INNER JOIN R
         ON H.[ROW NUM] = R.[ROW NUM]
       INNER JOIN JOBS
         ON R.[JOB NUM] = JOBS.[JOB NUM]

Thank you, but I am using this human being Pervasive db and that syntax is no good. Here's the documentation for Joins in Pervasive http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-53.html

and it lists Inner Joins but my query

code:

SELECT H.description, H.prhours1, H.prhours2, J.jobid FROM JrnlHdr H
INNER JOIN jrnlrow R ON r.postorder = h.postorder
INNER JOIN JOBS j ON r.JobRecordNumber = j.JobRecordNumber
where PRHours1 > 0.000000000 and transactionDate >={d '2008-06-14'}

gives me an error

quote:

Error: Extra characters at end of query: INNER. (State:37000, Native Code: F3E)

If just for fun I put "LEFT OUTER JOIN" instead, it "works" but doesn't give me the correct result set. halp

# ? Jun 27, 2008 18:13

Aredna: Mar 17, 2007; Nap Ghost

Bruce Hussein Daddy posted:

Thank you, but I am using this human being Pervasive db and that syntax is no good. Here's the documentation for Joins in Pervasive http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-04-53.html

and it lists Inner Joins but my query
code:
SELECT H.description, H.prhours1, H.prhours2, J.jobid FROM JrnlHdr H
INNER JOIN jrnlrow R ON r.postorder = h.postorder
INNER JOIN JOBS j ON r.JobRecordNumber = j.JobRecordNumber
where PRHours1 > 0.000000000 and transactionDate >={d '2008-06-14'}
gives me an error
If just for fun I put "LEFT OUTER JOIN" instead, it "works" but doesn't give me the correct result set. halp

It shouldn't make a difference, but it looks like the join type is optional in your documentation. You could try it with just JOIN instead of INNER JOIN as that is the default join type.

If that doesn't work, hopefully someone with experience using Pervasive has some insight as to why the standard syntax isn't working.

# ? Jun 27, 2008 18:50

SaTaMaS: Apr 18, 2003

I'm runnings MS SQL Server 2005 and have close to 100 .sql files that I need it to run (generated by another program). Is there some way to automatically batch process all these files at one time? Some kind of utility or batch file that I could create to get SQL server to open each file and run it?

# ? Jun 28, 2008 17:26

mezz: Aug 12, 2004; oh polly

if all those files arrive in the same dir you could simply put cmd /c type c:\sql\*.sql > c:\sql\out\job.sql in a daily job.
Then you end up with only one file with a fixed filename which can be easily scheduled to run from sql server.

# ? Jun 28, 2008 19:12

SaTaMaS: Apr 18, 2003

mezz posted:

if all those files arrive in the same dir you could simply put cmd /c type c:\sql\*.sql > c:\sql\out\job.sql in a daily job.
Then you end up with only one file with a fixed filename which can be easily scheduled to run from sql server.

Works great! Thanks for the tip.

# ? Jun 28, 2008 20:59

Forzan: Mar 15, 2002; by Ozmaugh

What's the best way to store an MD5 hash in Postgres? char(32) is a terrible way to store a base 16 integer, but it's the only suggestion I've seen (other than the laughably worse varchar and text). I can't find a data type that accepts it on first glance.

edit: Decided to use a screwdriver instead of a hammer. PHP's MD5 function has a raw_output option since PHP5, meaning I can have the hash as a binary from the beginning. Not sure if it requires more or less effort on the function to get the hash that way, but it's probably negligible, and at least I know how to store a 16 byte binary.

Forzan fucked around with this message at 02:24 on Jun 29, 2008

# ? Jun 29, 2008 00:11

Green_Machine: Jun 28, 2008

I'm a .NET dev, and only occasionally have to write SQL. I can do the basics, mostly, but nothing comlex. Can you help me write a query?

What I'm trying to do is delete rows from one table based on the contents of rows in other tables. This is the "spirit" or what I want to do, but of course this isn't valid SQL:

code:

DELETE FROM [ClientPermissions] 
              INNER JOIN [Clients] 
                ON [ClientPermissions].[ClientId] = [Clients].[ClientId]
WHERE NOT [Clients].[ClientGroupName] = 'X'

In English, "Delete the rows from ClientPermissions that have ClientIds that correspond to Clients rows that don't have ClientGroupName = 'X'." The reason that my SQL above is invalid is because it's saying to delete rows from a temporary join table, which makes no sense. I want to delete the rows from the ClientPermissionsTable.

I have no idea how to write a query such as this so that it will work.

Green_Machine fucked around with this message at 15:49 on Jun 29, 2008

# ? Jun 29, 2008 15:45

SaTaMaS: Apr 18, 2003

mezz posted:

if all those files arrive in the same dir you could simply put cmd /c type c:\sql\*.sql > c:\sql\out\job.sql in a daily job.
Then you end up with only one file with a fixed filename which can be easily scheduled to run from sql server.

Is there some way to get this to combine *.sql files that are in subdirectories as well? I've been working at it and came up with doing a copy first
for /R %G IN (*.sql) DO copy /Y %G c:\files
but even that doesn't work for some reason (I believe it's because for doesn't like long filenames)

# ? Jun 29, 2008 15:45

mezz: Aug 12, 2004; oh polly

You just need quotes around %g I think.
for /R %G IN (*.sql) DO copy /Y "%G" c:\files

e: there's certainly a more elegant way but i can only concentrate on my hangover right now it seems

mezz fucked around with this message at 16:04 on Jun 29, 2008

# ? Jun 29, 2008 16:00

JingleBells: Jan 7, 2007; Oh what fun it is to see the Harriers win away!

Green_Machine posted:

I'm a .NET dev, and only occasionally have to write SQL. I can do the basics, mostly, but nothing comlex. Can you help me write a query?

What I'm trying to do is delete rows from one table based on the contents of rows in other tables. This is the "spirit" or what I want to do, but of course this isn't valid SQL:
code:
DELETE FROM [ClientPermissions] 
              INNER JOIN [Clients] 
                ON [ClientPermissions].[ClientId] = [Clients].[ClientId]
WHERE NOT [Clients].[ClientGroupName] = 'X'
In English, "Delete the rows from ClientPermissions that have ClientIds that correspond to Clients rows that don't have ClientGroupName = 'X'." The reason that my SQL above is invalid is because it's saying to delete rows from a temporary join table, which makes no sense. I want to delete the rows from the ClientPermissionsTable.

I have no idea how to write a query such as this so that it will work.

You can't do joins in a delete statement, I'm unsure on what DBMS you're using, in oracle you'd do something akin to:

code:

DELETE FROM ClientPermissions
WHERE ClientPermissions.ClientId IN
       (SELECT Clients.ClientId
        FROM Clients
        WHERE Clients.ClientGroupName != 'X')

Essentially you have a subquery return the id's of all the clients which do not have a ClientGroupName of 'X', these are the id's you want to delete from ClientPermissions.

# ? Jun 29, 2008 16:20

Zombywuf: Mar 29, 2008

JingleBells posted:

You can't do joins in a delete statement, I'm unsure on what DBMS you're using, in oracle you'd do something akin to:

In TSQL you can, I'd assume that's what Green_Machine is using if he's doing .net. The syntax is:

code:

DELETE FROM
  [ClientPermissions]
FROM
  [ClientPermissions]
  INNER JOIN [Clients]
    ON [ClientPermissions].[ClientId] = [Clients].[ClientId]
WHERE
  NOT [Clients].[ClientGroupName] = 'X'

# ? Jun 29, 2008 16:34

Green_Machine: Jun 28, 2008

Thanks JingleBells and Zombywuf -- both of your solutions will do the trick.

# ? Jun 29, 2008 17:08

JingleBells: Jan 7, 2007; Oh what fun it is to see the Harriers win away!

Zombywuf posted:

In TSQL you can, I'd assume that's what Green_Machine is using if he's doing .net. The syntax is:
code:
DELETE FROM
  [ClientPermissions]
FROM
  [ClientPermissions]
  INNER JOIN [Clients]
    ON [ClientPermissions].[ClientId] = [Clients].[ClientId]
WHERE
  NOT [Clients].[ClientGroupName] = 'X'

Interesting, I don't think I've seen that type of syntax before

# ? Jun 29, 2008 17:27

Tomathan: Nov 6, 2006; They won't let us wear our baggy pants

This isn't a sql code question, but a general question regarding sql server versions. If this is not the right place for this kind of question my apologies.

Our company has a point of sale system that uses a sql database and when we first got the system we installed sql server 2005 express because we didn't think our database would grow larger than 4gb, however one year later it has. We plan on getting sql server 2005 standard, but since our operations have nearly come to a stand still, I was wondering if installing the 180 day trial version of sql 2005 standard that microsoft provides would be a temporary fix, Or does the trial version have limitations that would prevent it from functioning in the same way as a full licensed install? I tried searching google for limitations or similar situations to the one we are in, but no luck.

# ? Jun 29, 2008 18:09

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Tomathan posted:

This isn't a sql code question, but a general question regarding sql server versions. If this is not the right place for this kind of question my apologies.

Our company has a point of sale system that uses a sql database and when we first got the system we installed sql server 2005 express because we didn't think our database would grow larger than 4gb, however one year later it has. We plan on getting sql server 2005 standard, but since our operations have nearly come to a stand still, I was wondering if installing the 180 day trial version of sql 2005 standard that microsoft provides would be a temporary fix, Or does the trial version have limitations that would prevent it from functioning in the same way as a full licensed install? I tried searching google for limitations or similar situations to the one we are in, but no luck.

I am pretty sure it is fully functional, but just grab the phone and call microsoft sales, they should be able to tell you pretty quick.

# ? Jun 29, 2008 20:12

Tomathan: Nov 6, 2006; They won't let us wear our baggy pants

Begby posted:

I am pretty sure it is fully functional, but just grab the phone and call microsoft sales, they should be able to tell you pretty quick.

Thanks, for the reply. It is in fact fully functional, but the license agreement prohibits its use in a "live operating environment" which i think we fall under. So, that's that. I'll just have to keep shrinking the database until our full copy comes in with all the CAL's

# ? Jun 29, 2008 21:09

chocojosh: Jun 9, 2007; D00D.

No Safe Word posted:

I've been working with SSIS for about a year now and the two Wrox books I used to get started: Professional SQL Server 2005 Integration Services and the Expert counterpart were both pretty good.

Also, reading through Jamie Thomson's blog entries are good too: http://blogs.conchango.com/jamiethomson/

Awesome. I've spent a few hours playing with SSIS and the Professional Wrox book came in from amazon on Friday. I'm starting to understand how it all fits together.

The main thing I have left to do now is to figure out how to get the errors from my Data Flow task (recordset destination, datareader destination, variable) to an Execute SQL task. I need to be able to call a stored procedure for each erroneous row (perhaps also call one stored proc for the entire batch of rows). I imagine in the Data Flow I just set output destinations and then I go to the control flow and link my Data Flow task to multiple Execute SQL tasks?

Zombywuf: As a coder I much prefer using plain old SQL/.NET to handle this kind of work, although I can see how there is a market for it in suitably large systems.

# ? Jul 1, 2008 20:37

No Safe Word: Feb 26, 2005

chocojosh posted:

The main thing I have left to do now is to figure out how to get the errors from my Data Flow task (recordset destination, datareader destination, variable) to an Execute SQL task. I need to be able to call a stored procedure for each erroneous row (perhaps also call one stored proc for the entire batch of rows). I imagine in the Data Flow I just set output destinations and then I go to the control flow and link my Data Flow task to multiple Execute SQL tasks?

Use the OLE DB Command. Getting stuff outside the Data Flow is (as far as I know) impossible*, the control flow has no "data" between its tasks.

* - unless you use package variables which are scoped to the entire package, but obviously you can only do so much with that

# ? Jul 1, 2008 20:39

chocojosh: Jun 9, 2007; D00D.

No Safe Word posted:

Use the OLE DB Command. Getting stuff outside the Data Flow is (as far as I know) impossible*, the control flow has no "data" between its tasks.

* - unless you use package variables which are scoped to the entire package, but obviously you can only do so much with that

I may not be able to use an OLE DB command for what I need. This is what I have:

Right now in my dataflow I'm joining a CSV file and a database table (to import daily information that we get from an external source).

What I'm doing now is that if there are values that exist in one source and not the other (obtained from a FULL-OUTER JOIN and checking for NULL), I want to send an e-mail for each value (record/row) that is not in both lists (there's a few other rules, but that's the gist of it).

# ? Jul 1, 2008 20:52

Twlight: Feb 18, 2005; I brag about getting free drinks from my boss to make myself feel superior; Fun Shoe

Found it out

Twlight fucked around with this message at 22:52 on Jul 1, 2008

# ? Jul 1, 2008 21:29

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

I may not be able to use an OLE DB command for what I need. This is what I have:

Right now in my dataflow I'm joining a CSV file and a database table (to import daily information that we get from an external source).

What I'm doing now is that if there are values that exist in one source and not the other (obtained from a FULL-OUTER JOIN and checking for NULL), I want to send an e-mail for each value (record/row) that is not in both lists (there's a few other rules, but that's the gist of it).

You might be able to use an OLE DB Command for that, but I think your best bet would be to store that information in a different table and then run a SQL task (or whatever) afterwards. What you'd want to do either way is use a multi-cast to create a second copy of the data, then have one copy go to the destination tables as normal, while the other copy goes into a conditional split, which you use to discard the "good" rows. The "bad" rows then either go through your OLE DB Command, or they go to another table for use outside of the Data Flow. If you don't want your "bad" rows to go into the destination tables, then you don't need the multi-cast, just have the conditional split send the good rows on to the destination instead of discarding them.

# ? Jul 1, 2008 21:38

MrHyde: Dec 17, 2002; Hello, Ladies

I have a list in TSQL ('1','2','3') and I want to select them as a table so I can compare them, so I'd like to say something like

code:

SELECT * FROM ('1','2','3')

and get the result as a table in the form

code:

+-----------+
|  COLUMN:  |
+-----------+
| 1         |
| 2         |
| 3         |
+-----------+

Is there an easy way to do this? For some reason I'm totally unable to figure this out.

# ? Jul 1, 2008 21:40

chocojosh: Jun 9, 2007; D00D.

Jethro posted:

You might be able to use an OLE DB Command for that, but I think your best bet would be to store that information in a different table and then run a SQL task (or whatever) afterwards. What you'd want to do either way is use a multi-cast to create a second copy of the data, then have one copy go to the destination tables as normal, while the other copy goes into a conditional split, which you use to discard the "good" rows. The "bad" rows then either go through your OLE DB Command, or they go to another table for use outside of the Data Flow. If you don't want your "bad" rows to go into the destination tables, then you don't need the multi-cast, just have the conditional split send the good rows on to the destination instead of discarding them.

First, I'm a moron for not realizing that the OLE DB Command is in the Data Flow and not in the Control Flow. It *is* exactly what I needed.

In this instance I *don't* need the multicast -- currently some people are inputting the data manually every day and there is a CSV file already created.

Thanks for your help! Did you find it a bit intimidating at first to learn how to use SSIS? I'm having some trouble because I have to think: *ok, I know how to do a join in SQL, but how do I do it here? --scan the list of tools-- Hmm.. this merge join seems interesting.. let's google it.. oh, that actually is the right thing.. ok.. let's figure out this dialog box now...*.

# ? Jul 1, 2008 21:57

chocojosh: Jun 9, 2007; D00D.

MrHyde posted:

I have a list in TSQL ('1','2','3') and I want to select them as a table so I can compare them, so I'd like to say something like
code:
SELECT * FROM ('1','2','3')
and get the result as a table in the form
code:
+-----------+
|  COLUMN:  |
+-----------+
| 1         |
| 2         |
| 3         |
+-----------+
Is there an easy way to do this? For some reason I'm totally unable to figure this out.

Dirty/quick way is to use a temp table. I've seen stuff using recursive CTEs but I've never had to use recursive CTEs before.

SET NOCOUNT ON
CREATE TABLE #Temp (col1 int)
DECLARE @Index int
DECLARE @MaxValue int
SET @Index = 1
SET @MaxValue = 10
WHILE @Index <= @MaxValue
BEGIN
INSERT INTO #Temp VALUES (@Index)
SET @Index = @Index + 1
END
select * from #Temp
DROP TABLE #Temp

# ? Jul 1, 2008 22:10

No Safe Word: Feb 26, 2005

chocojosh posted:

Thanks for your help! Did you find it a bit intimidating at first to learn how to use SSIS? I'm having some trouble because I have to think: *ok, I know how to do a join in SQL, but how do I do it here? --scan the list of tools-- Hmm.. this merge join seems interesting.. let's google it.. oh, that actually is the right thing.. ok.. let's figure out this dialog box now...*.

That's pretty much it. Play around with it, read as much as you can find. Eventually you figure out better ways of doing things. It helps if you have other folks doing similar things at the same time, feeling their own way out.

# ? Jul 1, 2008 22:11

MrHyde: Dec 17, 2002; Hello, Ladies

chocojosh posted:

Dirty/quick way is to use a temp table. I've seen stuff using recursive CTEs but I've never had to use recursive CTEs before.

SET NOCOUNT ON
CREATE TABLE #Temp (col1 int)
DECLARE @Index int
DECLARE @MaxValue int
SET @Index = 1
SET @MaxValue = 10
WHILE @Index <= @MaxValue
BEGIN
INSERT INTO #Temp VALUES (@Index)
SET @Index = @Index + 1
END
select * from #Temp
DROP TABLE #Temp

Thanks for the help. I wasn't really clear. I don't want a list of integers, I just want a list of comma separated values I have, a better example would be ('firstVal','2ndval','3rdval')

I guess the actual problem is I have a list of values from an outside source and I have a table. I want to figure out which values are in the list, but not in the table.

# ? Jul 1, 2008 22:15

Victor: Jun 18, 2004

MrHyde posted:

I have a list in TSQL ('1','2','3') and I want to select them as a table so I can compare them

code:

with T as (
    select n = 1 union
    select 2 union
    select 3
)
select  *
from    T

# ? Jul 1, 2008 22:15

chocojosh: Jun 9, 2007; D00D.

MrHyde posted:

Thanks for the help. I wasn't really clear. I don't want a list of integers, I just want a list of comma separated values I have, a better example would be ('firstVal','2ndval','3rdval')

I guess the actual problem is I have a list of values from an outside source and I have a table. I want to figure out which values are in the list, but not in the table.

If the "outside source" happens to be a file you may be able to use bulk insert to load the data into a (temporary) table.

Then you have two tables, your temp containing the values from the list and your actual DB table. At this point you can do an outer join or a subquery with NOT EXISTS to compare the tables.

# ? Jul 1, 2008 22:21

Adbot: ADBOT LOVES YOU

# ? Apr 29, 2024 09:33

MrHyde: Dec 17, 2002; Hello, Ladies

Got it, thanks guys.

Ended up using something along the lines of

code:

create table #temp (fileName nvarchar(200))
INSERT INTO #temp (fileName)
(SELECT 'filename.fil' UNION
SELECT 'filename2.fil' UNION
SELECT 'filename3.fil' UNION)
select * from #temp
WHERE #temp.fileName NOT IN (SELECT Table.FileName FROM Table)
DROP TABLE #temp

# ? Jul 1, 2008 22:27

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »