I tried to make the title as clear as possible... here is my scenario: I have 2 tables (let's call them table A and table B) that have a similar schema. I would like write a stored procedure that would select specific columns of data out of table A, and insert this data as a new record in table B. Can someone point me in the write dire...
How do you make a field in a sql select statement all upper or lower case? Example: select firstname from Person How do I make firstname always return upper case and likewise always return lower case? ...
I had a parametrized insert statement that was working well and I needed to add a select statement and wrap it all in a transaction in order to get data for one of the fields for the insert. I'm not sure if I've hit a limitation with ASP/ADO or if I've just got something syntactically wrong. Here's my code: set oSQLCommand = Server.Cr...
I'm reseting a sort column that has duplicate or missing values like so: set @last=''; set @sort=NULL; update conf_profile set sort= if( @last=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)), (@sort:=@sort+1), (@sort:=0) ) order by org_id,profile_type_id,page,col,sort,id; (Go through all th...
I'm selecting 1 field from 1 table and storing it into a temp table. Sometimes that table ends up with 0 rows. I want to add that field onto another table that has 20+ fields Regular union won't work for me because of the field # mismatch. Outer wont work for me because there is nothing to compare. NVL doesn't work on the first temp t...
I am trying to select a record out of the table1 by joining table2 and using table2 columns in the WHERE statement. Col1 and Col2 in table1 can be stored in col1 of table2. I need to join col1 of table2 with either the value of col1 or col2 in table1 here is the sql statement I created so (pseudo): SELECT t1.Col1, t1.Col2, t...
I'm looking to inspect SQL statements in Java/jdbc code to ensure that the SQL to be executed is of acceptable quality. Neither PMD not Findbugs appears to have JDBC or sql rules. I could use p6spy to log the SQL and look at that way, but this is manual. I'm wondering if the strategy of of using PMD/Findbugs/etc to create a rule that ...
I was reading Jeff Atwood's blog on Behold WordPress, Destroyer of CPUs and saw that many people there considered 20 SQL Queries per page load to be a lot. What is the average amount of queries per page nowadays for a highly dynamic page with auto suggest, auto refreshing of data, customized pages, and the kitchen sink? For a simple ...
Does anyone know of a link to a reference on the web that contains a sample English dictionary word script, that can be used to populate a dictionary table in SQL Server? I can handle a .txt or .csv file, or something similar. Alternatively, I'm adding custom spellchecking functionality to my web apps...but I don't want to integrate th...
Hi there, I have a strange encounter when creating a GridView using "SELECT..WHERE.. IN (value1, val2...)". in the "Configure datasource" tab, if i hard code the values "SELECT ....WHERE field1 in ('AAA', 'BBB', 'CCC'), the system works well. However, if I define a new parameter and pass in a concatenated string of values using a varia...
how to remove html elements in a text field from sql server ...
I found http://www.databaseanswers.org/data_models/ very useful. Any other suggestions? ...
I need some SQL to update a record in a database if it exists and insert it when it does not, looking around there looks to be several solutions for this, but I don't know what are the correct/ accepted ways to do this. I would ideally like it to work on both Firebird 2 and MySQL 5 as the update will need to be ran against both database...
What is a good book to become an SQL master? That is, learn how to tweak queries, table design, optimizing the server, etc. I use mostly MySQL and Postgres, but maybe a DBMS agnostic book would come handy. The "optimizing the server" part is probably specific to each DBMS. I would like to know of a good book to learn advanced SQL and d...
I have setup both mirroring and and log shipping on 8 databases of about 10 GB each size on same instance, but now when I want to mirror another database it very long to display the Database Properties page. Any suggestion for this problem. ...
I have a table like so: keyA keyB data keyA and keyB together are unique, are the primary key of my table and make up a clustered index. There are 5 possible values of keyB but an unlimited number of possible values of keyA,. keyB generally increments. For example, the following data can be ordered in 2 ways depending on which key c...
Consider the following table: mysql> select * from phone_numbers; +-------------+------+-----------+ | number | type | person_id | +-------------+------+-----------+ | 17182225465 | home | 1 | | 19172225465 | cell | 1 | | 12129876543 | home | 2 | | 13049876543 | cell | 2 | | 15064223454 | home | ...
I've had this problem before and found that basically I've got a connection that I'm not closing quickly enough (leaving connections open and waiting for garbage collection isn't really a best practice). Now I'm getting it again but I can't seem to find where I'm leaving my connections open. By the time is see the error the database has...
I have a table like this: Column | Type | Modifiers ---------+------+----------- country | text | food_id | int | eaten | date | And for each country, I want to get the food that is eaten most often. The best I can think of (I'm using postgres) is: CREATE TEMP TABLE counts AS SELECT country, food_id, count(*) as count...
We've all hit those errors that take hours/days to debug, only to find it was because of a misplaced assumption, wrong input data, or some other unseemly error. As an example, I just spent 6 hours trying to find an error in a stored procedure. It fired fine in Query Analyzer, but failed in code. The end result was a space that didn't ...