sql

Dynamic SQL for updating a table from ASP .NET

I have an ASP.NET 3.5 application that I want to allow the user to select a table and allow CRUD operations on that table. The user will be restricted to a number of tables to edit however the tables and even database won't be known until after deployment; the web.config will setup the connection and tables. So I need to build a framewo...

SQL: No Identity feature workaround using triggers, Any Help?

I'm a little rusty with my triggers and what not and am trying to figure out this problem for a class: In a database TEST, tables do not have the option of the IDENTITY feature. In other words, when we insert a row into the table “Users”, we would like the primary key “UserID” to auto-increment. Please suggest a workaround to implement ...

sql select replace integer with string

I’m pulling data form a MySQL database where integer values correlate to more meaningful string outputs. For example, a relation’s attribute “Sport” is defined as integer that can have the value of 1-4. Where 1 => Basketball, 2= Football, 3 = Soccer, 4 = Kickball. Or even a more trivial example, “Employed” where 1=> True, 0=> False. Wh...

Is it possible to do a case-insensitive DISTINCT with SAS (PROC SQL)?

Is there a way to get the case-insensitive distinct rows from this SAS SQL query? ... SELECT DISTINCT country FROM companies; The ideal solution would consist of a single query. Results now look like: Australia australia AUSTRALIA Hong Kong HONG KONG ... where any of the 2 distinct rows is really required One could upper-case the...

ORA-4030 Oracle : How to resolve

Hi all, In our oracle server(10 g), we are getting ORA-4030 error on sometimes. ORA-04030: out of process memory when trying to allocate nn bytes We understood it is related with memory size adjustment. We are trying some memory settings. Other than this, wanted to know, (1) Any specific SQL query usages will be cause this kin...

Use bcp to import csv file to sql 2005 or 2008

I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a ';' . Example CSV FILEcontents: FirstName;LastName;Country;Age Roger;Mouthout;Belgium;55 SQL Person Table Columns: FName,LName,Country ...

SSIS Union All problem - is there a bug in SSIS for this item?

I am using SQL 2005 and SSIS.............. I have 2 data sources. One from table A and one from table B. I want to move data from table A to table B. But first i get the MAX date from both and compare them. If they are the same then i must either stop the SSIS package or use the Conditional Split. But when the MAX date from table B g...

Using views in a datawarehouse

I recently inherited a warehouse which uses views to summarise data, my question is this: Are views good practise, or the best approach? I was intending to use cubes to aggregate multi dimensional queries. Sorry if this is asking a basic question, I'm not experienced with warehouse and analyis services Thanks ...

How do I create a customary ranking of my result set in SQL?

Say I have a result set that can contain one or more values, but I always only want one value in the result, and specific values are worth more than others. How would I solve this in sql so I can use it in a sub-query? Example (T-SQL specific, that's the accent I work with): I have a table: tChore(ChoreId int primary key, ChoreDescri...

Select all records don't meet certain conditions in a joined table

Let's say we have two tables here , posts and comments, the relations is one to many, there has a field call comment_date in the comments table. Now I am struggle to get the expected results as follow: All the posts have no comments after a certain date, can I done this by a SQL statement? ...

Database Design and the use of non-numeric Primary Keys

Hello, I'm currently in the process of designing the database tables for a customer & website management application. My question is in regards to the use of primary keys as functional parts of a table (and not assigning "ID" numbers to every table just because). For example, here are four related tables from the database so far, one ...

SQL Query for retrieving search results

I need help designing a query that will be used to return a website's search results. Users search by selecting items from a list of attributes. Each search result item returned must have ALL the attributes selected by the user. The challenge (for me at least!) is figuring out how to only return results that have ALL the attributes ve...

SQL to SOQL Conversion (Salesforce.com SOQL)

Has any one done SQL to SOQL Conversion for Salesforce.com Objects? ...

SQL Dump from DB2

Hi, I'm trying to dump the contents of a particular schema in one IBM DB2 UDB server into an sql text file (much like the mysqldump functionality of mysql). I came across db2look, but it only dumps the structure of the schema (only ddl, no dml). So how can I get my thing done? jrh. ...

Getting SQL to use the correct index

I have a table with two indexes, one of which is the faster covering index for a certain query. However, mySQL (5.1) is not choosing the correct index. I have looked at the explain for this query and done some speed tests and it makes a big difference if you force the key. Is there any way of examining how it picks the index and what cr...

Optimizing SELECT Query on Oracle 10 Dense Unique Index

I have a table in Oracle 10 with following structure Create Table Bookmarks( BOOKMARKID NUMBER(10,0) NOT NULL PRIMARY KEY, URL VARCHAR2(4000 CHAR) NOT NULL UNIQUE ) URL has a unique constraint set thus a unique index. There are about 1 million records in this table. I have to frequently check if a bookmark already exists in ...

How do you generate classes in C# from a SQL Server Schema?

I have a sql database and I want to create a class for each table. I think a tool exists that allows me to extract information from a sql database and transform it into classes like "DataTable" or "DataRow". Afterwards, I could use those object in dataset. ...

Combine fields from joined tables

One category can have many products. I have to build StoredProcedure that returns all categories with some data from Products combined into one field: SP Result: idCategory Name ProductNames ProductQuantities 1 Cat1 Procut1,Product2 24,32 2 Cat2 ProductX,ProductY 0,61 ProductNames and Product...

Simple MySQL Table Running Slow Queries

I have a very simple table with two columns, but has 4.5M rows. CREATE TABLE `content_link` ( `category_id` mediumint(8) unsigned NOT NULL, `content_id` int(10) unsigned NOT NULL, PRIMARY KEY (`content_id`,`category_id`), KEY `content_id` (`content_id`,`category_id`) ) ENGINE=MyISAM; When I run a simple query like: SELECT ...

Retrieving auto-incremented column value from table where multiple insert/selects in single stored procedure

I have a stored procedure with multiple insert/select statements. Let's say I'm using the first insert to populate a "Manager" table. On insert, a ManagerId (incremented automatically) is added, but not referenced in the insert statement. I then wish to use the ManagerId from this table to insert a row into another table, where Manage...