Hello, I'm using SQL Server 2005. I am migrating data over from a current database (single table) to a new database (normalized - many tables). In the new database, I have a base table (let's call it "BaseTable"), and multiple other tables (let's call them "DependentA", and "DependentB"). Some of the data from the old database will go t...
I'm refactoring some older SQL, which is struggling after 4 years and 1.7m rows of data. Is there a way to improve the following MS SQL Query: SELECT ServiceGetDayRange_1.[Display Start Date], SUM (CASE WHEN Calls.line_date BETWEEN [Start Date] AND [End Date] THEN 1 ELSE 0 END) AS PerDayCount FROM dbo.ServiceGetDayRange(GET...
I know how to create NSPredicate to do sql like "SELECT * FROM DRINK". But how about this query: "SELECT I.name, D_I.amount FROM DRINK_INGREDIENT D_I, DRINK, INGREDIENT I WHERE D_I.drinkID=1 AND DRINK.drinkID=1 AND I.ingredientID = D_I.ingredientID;" How do I even set the NSEntityDescription, and NSPredicate for this kind of que...
It seems like to combine two or more tables, we can either use join or where. What are the advantages of one over the other? ...
I'd like to loop through a list of strings and execute a function/procedure with each string as the argument. What's the best alternative to the following generic code (since it's not legal): set serveroutput on; begin FOR r IN ('The', 'Quick', 'brown', 'fox') LOOP dbms_output.put_line( r ); END LOOP; end; I assu...
I've some huge text files to process and make sense out of the data. Part of the task is to save this data into a database. I want to use Ruby, with postgres or mysql, postgres being the first choice. What libraries should I include? There is no model, its going to be plain SQL statements. How to do this without rails? ...
Hey there, I have a CATEGORIES table that links to an ITEMS table (one to many). Each item table could have multiple barcodes in the BARCODES table (one to many). The idea is that a new barcode may be added against an item at some point, but the old data is stored in the BARCODES table so that if a search is done with an order with las...
I need to determine the available space on the drive where my database lives. I know about the xp_fixeddrives procedure but how do I get the info for the specific drive where my database resides? ...
Based on getting Query Execution Statistics using this extremely useful piece of SQL obtained from this post Most Executed Stored Procedure - Stack Overflow: SELECT TOP 100 qt.TEXT AS 'SP Name', SUBSTRING(qt.text, qs.statement_start_offset/2, CASE WHEN (qs.statement_end_offset = -1) THEN LEN(qt.text) ELSE (qs.statement_end_offset ...
How do you do pagination in sql server 2008 ? ...
Is there any way to update all the records in a table if there is atleast one filed which has empty value? Thanks. ...
Hi all. A while back I had to come up with a way to clean up all indexes and user-created statistics from some tables in a SQL Server 2005 database. After a few attempts it worked, but now I gotta have it working in SQL Server 2000 databases as well. For SQL Server 2005, I used SELECT Name FROM sys.stats WHERE object_id = object_id(@t...
I have a fulltext indexed table and try to query for results matching multiple words. E.g. I have a address table with the indexed columns address_text, zip_code and city. | ROW | address_text | zip_code | city | | 1 | Bourbon street | 1234 | Baltimore | | 2 | Bourbon street | 1234 | New Orleans| Now I want t...
I based my code on the answer to the question Access get all tables, but am getting the following error: DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'MSysObjects'. (SQL-42000) at direct.pl line 22. [Microsoft][ODBC Microsoft Access Driver] Reco...
Hi, How to execute plain sql in grails . I need to use sql query for inserting new record in database . How can we achieve this with out using HQL and gorm relations . thanks ...
I am storing network share paths in my table in MySQL. but some entries got corrupted, i.e they dont have any backslashes in between. Path is like a big word. I wrote a small script in PHP to update these paths to try and add slashes. It does not give me any error when I run it but the results are not getting updated in the table. Curr...
This is hopefully just a simple question involving performance optimizations when it comes to queries in Sql 2008. I've worked for companies that use Stored Procs a lot for their ETL processes as well as some of their websites. I've seen the scenario where they need to retrieve specific records based on a finite set of key values. I'v...
Hello there, i have a Problem with a query. I have a huge Table with Zipcodes from Germany called 'Postleitzahlen'. There is another table with Companies called 'Firmen' Structure is like this: Firmen ------ ID City State ZipCode Postleitzahlen -------------- ZipCode State Now I want, all empty ( '' or NULL ) State-fields of Firm...
My stored procedure: CREATE PROCEDURE spSomeStoredProcedure AS BEGIN SELECT CategoryName FROM Categories ORDER BY CategoryName SELECT Top 10 CompanyName FROM Customers ORDER BY CompanyName END GO I tried to use ISQLQuery, but List() method returns data only from the first SELECT ...
Hi, I have a database table containing scripts for plays/dramas. The scripts can be categorized under 'Theatre and Education', 'Plays for Young People' or both (by using bools to determine whether they belong to said categories); scriptID: int theatreAndEducation:bit playsForYoungPeople: bit scriptTitle: varchar scriptSample:text t...