SQL Use alias in Where statement
I wounder how I could use an alias in a where statement. Example : SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1 FROM MyTable WHERE Col1 = 'MySearch' I use MSSQL 2005 ...
sql
Paste from SQL to Excel... Excel wants to think for me
Group I am pasting data from SQL to Excel and it appears that Excel is trying to read my mind by converting some of my numbers to dates. I.e. one number is 2-2131 and when I paste it to excel it shows Feb-31 even 2-3291 wnats to be Feb-61 STOP IT EXCEL! This is driving me crazy and I can't seem to find an easy way around this. I trie...
execute sp in a select statement
For an instance I a select statement and it is returning 1000 rows. I need to execute a particular stored procedure for every row the the select statement is returning. have you got any idea how can I do that? ...
Will using and updating the same field in one UPDATE statment cause undefined behaviour?
Here is an example UPDATE duration = datediff(ss, statustime, getdate()), statustime = getdate() where id = 2009 Is the field duration going to be assigned an undefined assigned value since statustime is getting used and assigned in the same statement? (i.e. positive value if datediff processed first or negative if statustime is proc...
Single-table-inheritance or two tables?
Hi, Suppose I have a table with the following columns (a list of words): word: varchar contributor: integer (FK) Now, suppose I wanted to have translations for each "word". What would be best? Having a second table? word: integer (FK) translation: varchar contributor: integer (FK) lang: integer (FK) Or all in the same table? word...
Where is the best place to handle transactions in stored procedure or in application?
Hello gurus, When my C#.net application updates records in more than one table, I use transactions so if anything should fail during the transaction I can rollback. Which one is a better practice? -Use stored procedure with BEGIN TRANSACTION/ROLLBACK/COMMIT TRANSACTION; -Use TransactionScope in the application as below: using (...
Do I need to "lock" a Sql Table during update job?
I have a web App running and I want to access the sql db with a another app to update one of the tables with new calculations. Will my users notice anything during the updates? WIll I get an error if they access at the same time I do updates, or is there auto locking ? tia ...
Query to return 1 instance of a record with duplicates
INFO: I am working with Microsoft SQL. Ok the title is confusing but here is an example of the table I'm working with: ID Value Signal Read Firmware Date Time 5 123 656 444 217 3/30/2009 11:00:00 AM 5 123 421 333 217 3/30/2009 04:00:00 PM 5 123 ...
TSQL: Update values by using With statement?
I have a table with a column I would like to update its values. Here is an example of TSQL codes: WITH Pieces(id, newdesc) AS ( SELECT itemid, REPLACE(REPLACE(description, 'DESC_A', 'DESC_B'), 'NEW_1', 'NEW_2') FROM myTable WHERE description like '%DESC_A%DESC_B%' ) -- SELECT * FROM Pieces UPDATE myTable SET description = newdesc //...
SQL query to pull unique phone numbers from 2 related tables
Hello - I am trying to create an ANI lookup table from 2 separate tables, one a table of stores and the other a list of contacts for those stores. I am using MS SQL Server 2005, which, unfortunately, does not support the MERGE INTO syntax... The good stuff: The ANI lookup table has 2 significant columns, StoreID and PhoneNumber. The ...
How do I do an SQL join with a CSV file in php (drupal specifically)?
Hey guys, here is my problem. I am writing a Drupal module. A user uploads a CSV list of e-mail addresses to a server in a form. I need to do a JOIN on my database to get a list of unique addresses from the CSV file and the DB (No, it is not for spam :). How would I turn the uploaded CSV file into a format that I can do an SQL join on i...
Using PHP variable variables in SQL quiery
Hi, sorry for this question to be a "can you fix it" one, but this little bit of code has been confusing me for a while now. I'm basically making a table with a bunch of rows and columns and in each one I have a slightly changing SQl query. To make it a bit easier instead of typing all that out I made this bit of script but it is starti...
Asp.Net Sql Auto-Increment for Wall Post
I have a table that contains three columns. "UserId" type-nvarchar "PostAuthorId" type-nvarchar "Post" type-text This table will contain "wall" posts like in facebook for each user's page. I am going to use a gridview on each user's page to display the posts. The issue is I want to display them with the latest(most current) post bein...
Asp.Net Button Event on refresh fires again??? GUID?
The obvious issue here is that on refresh a button event is recalled and duplicate posts are created in the database. I have read other questions on this site about the very same issue. Apparently the answer is for every post create a GUID and check to make sure the GUID is unique? I am not really sure how to implement this. Does that...
Sqlite for disk backed associative array?
I want to use SQLite as an associative array that is saved to disk. Is this a good idea? I am worried about having to parse SQL every time I do something like: database["someindex"] which will have to be translated to something like select value from db where index = 'someindex' which in turn will have to be translated to the SQL int...
Comparing Tuples in SQL
Is there any more convenient way to compare a tuple of data in T-SQL than doing something like this: SELECT TOP 100 A, B FROM MyTable WHERE (A > @A OR (A = @A AND B > @B)) ORDER BY A, B Basically I'm looking for rows with (A, B) > (@A, @B) (the same ordering as I have in the order by clause). I have cases where I have 3 fields, but it...
What are the principles behind, and benefits of, the "party model"?
The "party model" is a "pattern" for relational database design. At least part of it involves finding commonality between many entities, such as Customer, Employee, Partner, etc., and factoring that into some more "abstract" database tables. I'd like to find out your thoughts on the following: What are the core principles and motiva...
How can I automatically determining table(s) schema from a set of queries?
Is there any tool, which will take a set of CRUD queries, and generate a 'good enough' table schema for that set: e.g. I can provide input like this: insert username, password insert username, realname select password where username=? update password where username=? update realname where username=? With this input,...
Dynamically taking a table name in aspx form using sql server
Hi! I'm trying to dynamically accept a table name depending on the conditions satisfied, also the column name is selected dynamically, and so is the comparison value, but I'm getting an error while running it. I'm writing this code in C# and my backend is SQL server 2005. Please help me. Here is the code: if( table=="studenttab"...
Graphical GUI for sql server.
Which version of sql server allows building queries in a graphical GUI ? ...