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'

Ardhanari: Mar 23, 2006

Dakha posted:

Is there some simple way we can rework things so that the extraction can be done in a reasonable amount of time? Some of the more complicated queries are taking FAR too long to run

Stuff the IDs into a temp table and select a join off it?

# ¿ Nov 5, 2007 16:36

Adbot: ADBOT LOVES YOU

# ¿ Apr 25, 2024 10:18

Ardhanari: Mar 23, 2006

jwnin posted:

Can anyone provide me with some more ammo as to why we would not want to go down this path?

Did the devteam also volunteer to manage, troubleshoot, and update those tens of thousands of databases?

It's a horrible idea and whoever suggested it should be torn into little bitsy pieces and buried alive.

# ¿ Nov 8, 2007 20:06

Ardhanari: Mar 23, 2006

You're pretty much in the same situation as Dakha was on page 1. If you're going to need to scale it up, a temp table is the way to go.

I'm pretty sure 8000 characters isn't the limit for a query. That's the max size of the usual text variable types.

# ¿ Nov 8, 2007 21:53

Ardhanari: Mar 23, 2006

Replace the last two WHERE conditions with a BETWEEN?

# ¿ Jan 12, 2008 17:22

Ardhanari: Mar 23, 2006

Grigori Rasputin posted:

Does anyone know a good replacement for query analyzer? I'm working in a .NET/SQLServer shop now and find Query Analyzer abysmal to use. I used PL/SQL Developer at my last job as an Oracle dev and found it to be robust, easy to use and cheap. It made it easy to view tables/records/store procedures and had a proper IDE for creating/debugging PL/SQL stored procedures. Basically, it was a stripped down version of TOAD.

I just found this yesterday. It's got its quirks, but still seems to be pretty great. Support looks good too -- I emailed the dev over a couple issues I have with 2005 support and was told to expect a fixed build in a couple days. Pretty sure I'll be buying it (if I can't get my company to) once the evaluation is up.

# ¿ Feb 14, 2008 17:19

Ardhanari: Mar 23, 2006

Tots posted:

If I have a CSV file that matches up perfectly with a table that has FK values, how do I input that data?

Do I have to hand edit it to take out the foreign key values, or is there a simple work around in SQL?

(I am brand new to SQL, just learned how to create tables. Go easy on me.)

Are you using BULK INSERT (if this is SQL Server)? You could disable the FK constraint temporarily, load the data, then null any invalid FK values out (UPDATE newTable SET foreignKey = NULL WHERE foreignKey NOT IN (SELECT primaryKey FROM oldTable) or something like that) and re-enable the constraint.

# ¿ Mar 27, 2008 02:21

Ardhanari: Mar 23, 2006

Tots posted:

I'm using the school's database server, which currently doesn't give me permission to do a BULK INSERT, but I am working on getting that permission. For now, I am using sed (a search and replace program) to add in "INSERT INTO MyTable VALUES (" before each line, and a ")" after each line. I know I could further use RegExp to manipulate the data to suit my needs, but I know even less about RegExp than I know about SQL.

Now, to see if I understand what you are saying.

I am basically going to set the Foreign Key columns as regular columns, then add in the data, then UPDATE the Foriegn Key columns, inserting the data from their parent table, then reset them as foreign keys?

Yeah. See here under 'Disabling Foreign Key Constraints'. You'll basically just be turning off the check for data integrity while you insert the new rows.

# ¿ Mar 27, 2008 06:08

Ardhanari: Mar 23, 2006

dest posted:

I copied that, added the correct FROM, and I got the error: "Didn't expect 'when' after the select column list"

Remove the A.UnitPrice from just before the WHEN. CASE [Column] WHEN takes a value, CASE WHEN... takes a conditional expression. You need the conditional since it's not an exact match.

Also you should be able to set the ELSE value to NULL if you don't want it populated with a zero.

# ¿ Apr 2, 2008 23:39

Ardhanari: Mar 23, 2006

minato posted:

Something more descriptive. Because you can be sure that eventually you'll have fields like "Created by", "Last updated by" and "Maintained by" that will all refer to user IDs, so you can't just use userId for those.

I'd name it UserID for consistency in the foreign key relationship (User.UserID->Article.UserID reads better than User.UserID->Article.AuthorID), and those others would be CreatedBy, ModifiedBy, etcetera -- and those could be either IDs or user names, I've seen both used.

Of course, it's all pretty much up to how quickly you want other people to pick up on what you did :v:

# ¿ Apr 8, 2008 07:29

Ardhanari: Mar 23, 2006

Dromio posted:

This seems pretty small. I imported some legacy data into a SQL Server database. I want to add a uniqueidentifier column to that table, and populate it with random GUIDS. I've added the column, but how to I populate it with random GUIDs for the existing data?

You can set the default value of the column to NEWID() in the table designer or the ALTER TABLE script, and it should add everything the way you want it.

# ¿ May 16, 2008 00:04

Ardhanari: Mar 23, 2006

epswing posted:

I'm curious about the background of your project, and what exactly would cause you to ask if juggling ~1000 temp tables is acceptable overhead.

Not saying it's not, maybe you have some crazy requirements.

Yeah... but I don't see why fez couldn't just have one table, temp or not, keyed on session ID. What the hell is going on there?

# ¿ May 31, 2008 02:21

Ardhanari: Mar 23, 2006

Casimirus posted:

What I can't figure out is how to take a database name passed to the stored procedure and then access things like
code:
SELECT * from [@otherdatabase].atable
I know that it's not best practice or whatever, but this is for something to be run manually. I guess I could build my statement in a string and call that system stored procedure that executes SQL, but that would be a nightmare. If I do have to go that route though, will that gently caress up a transaction?

Yeah, it won't let you do that directly. For what you're doing, you might be able to get away with

code:

EXEC 'USE ' + @otherdatabase

to switch over and then execute the rest of your stuff normally, but if there's anything more than that you'll have to go with dynamic SQL through the whole thing. Erland Sommarskog has what I guess is kind of the bible for that.

# ¿ Jun 2, 2008 02:19

Ardhanari: Mar 23, 2006

Ardhanari posted:

code:

EXEC 'USE ' + @otherdatabase

Or, poo poo, I thought of this this morning but didn't have time to post: is there anything keeping you from using sp_addlinkedserver?

# ¿ Jun 3, 2008 00:42

Ardhanari: Mar 23, 2006

chocojosh posted:

How would you go about importing a CSV file into the database with some custom logic and be able to report errors (ideally by sending an e-mail)? We're using SQL Server 2005.

Look into BULK INSERT.

# ¿ Jun 18, 2008 00:02

Ardhanari: Mar 23, 2006

chocojosh posted:

Bulk insert doesn't seem to support custom rules.

I'm guessing the best thing to do would be to first check the database and ensure that our input is valid. If there are any errors, report them first, and keep the valid data and use bulk insert on the valid data?

I've never used the FIRE_TRIGGERS part before, but I think you could do it through that -- just set up a trigger on the destination table with whatever custom logic you need (checking that every row in Inserted has a corresponding ID, etc). You should even be able to send emails from the trigger, but make sure you only do it under strictly specified conditions unless you want whoever's running your email server to cut your throat.

# ¿ Jun 18, 2008 21:22

Ardhanari: Mar 23, 2006

xenilk posted:

Thanks for explaining, it completly makes sense now

Although, I'm still running into "errors"

Takes more than 1 minute to execute, is there anyway to make it faster?

Use EXCEPT to get rid of that innermost block comparison.

code:

SELECT Gallery.gallery_Id
FROM Gallery 
WHERE Gallery_Id in 
( 
	SELECT Gallery_Id from GalleryCategory
	WHERE Category_Id in(5,39)
	EXCEPT
	SELECT Gallery_Id FROM GalleryCategory
	WHERE Category_Id IN (10, 37, 38, 52, 62, 65, 74, 96)
);

I'm stuck with 2000 here so I can't test it (EXCEPT is a completely different animal) but something along those lines should work. I'm not sure where the HAVING and GROUP BY clauses would go.

# ¿ Jun 20, 2008 17:52

Ardhanari: Mar 23, 2006

Factor Mystic posted:

I've got some questions for a new hobby project. It's a web bookmark database type thing, and there will be user-added keywords associated with each url. I want to be able to search by keyword, and I want to do this in an intelligent way. I know not to just dump all the keywords into a big Text field, but I don't know the proper way to handle this. Can anyone post some tips or basic-intermediate articles on this type of thing?

Each URL can have many keywords, and keywords can have multiple bookmarks 'tagged', right? Set up a Keywords(KeywordID, KeywordData, ...) table and a Bookmark_Keyword (BookmarkID, KeywordID) to create your many-to-many relation. Then you can get the keywords for a URL by querying through the bridge table, or get all bookmarks with a particular tag the same way.

# ¿ Jun 22, 2008 16:58

Ardhanari: Mar 23, 2006

Factor Mystic posted:

Alright, in that setup I don't understand how a bookmark is related to more than one keyword. If each KeywordID links to only one keyword (KeywordData), how can more than one keyword be saved for a bookmark? I guess I don't understand the proper way to store a variable length list of data.

Keyword.KeywordID links to Bookmark_Keyword.KeywordID. Bookmark_Keyword.BookmarkID then links to Bookmark.BookmarkID. You'll have unique keywords (if you want) but they link to multiple Bookmark_Keyword entries and thus to multiple bookmarks (and vice versa).

code:

Keyword		Bookmark_Keyword	Bookmark
ID Name		K_ID	B_ID		ID Name
1  cool		1	3		1  google
2  shop		2	2		2  amazon
3  search	3	1		3  yahoo
		3	3

So Google is both 'cool' and 'search', and the 'search' keyword covers both Google and Yahoo. You're not storing lists, you're storing relations. If you want to store your tags as flat fields, you might as well replace SQL with Notepad. The relational database lets you say 'okay, item type one hooks up to item type two like this' -- with other side benefits like avoiding duplication (imagine how many things would get tagged with 'cool' or 'poo poo' in a flat list structure!) and faster querying.

ninja edit: holy poo poo, I didn't realize I had an encoded message about markup in there. Unintended, but I'm leaving that in!

# ¿ Jun 22, 2008 21:44

Adbot: ADBOT LOVES YOU

# ¿ Apr 25, 2024 10:18

Ardhanari: Mar 23, 2006

Super Mario Shoeshine posted:

I just found a way to separate that string but i need to know how to pass the result of a string function to a temp variable, i mean for example if i use this function SELECT RIGHT() i get as a result a number, is there a way to pass this number to a variable i just defined in the session?

SELECT @variable = RIGHT(whatever you're doing here)

# ¿ Jul 18, 2008 18:34

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