sql

insert data into several tables

Let us say I have a table (everything is very much simplified): create table OriginalData ( ItemName NVARCHAR(255) not null ) And I would like to insert its data (set based!) into two tables which model inheritance create table Statements ( Id int IDENTITY NOT NULL, ProposalDateTime DATETIME null ) create table Items...

Generated LinqtoSql Sql 5x slower than SAME EXACT hand-written sql

I have a sql statement which is hardcoded in an existing VB6 app. I'm upgrading a new version in C# and using Linq To Sql. I was able to get LinqToSql to generate the same sql (before I start refactoring), but for some reason the Sql generated by LinqToSql is 5x slower than the original sql. This is running the generated Sql Directly ...

SQL: joining multiples tables into one.

I have 4 tables. r1, r2, r3 and r4. The table columns are the following: rId | rName I want to have, in fine, an unique table - let's call it R. Obviously, R will have the following structure: rTableName | rId | rName I'm looking for a solution, and the more natural for me is to: add a single column to all rX insert this column...

Stored procedures vs. parameter binding

I am using SQL server and ODBC in visual c++ for writing to the database. Currently i am using parameter binding in SQL queries ( as i fill the database with only 5 - 6 queries and same is true for retrieving data). I dont know much about stored procedures and I am wondering how much if any performance increase stored procedures have ove...

Sanitizing DB inputs with XSLT

Hello I've been looking for a method to strip my XML content of apostrophes (') since my DBMS is complaining of receiving those. I need <name> Jim O'Connor</name> to become: <name> Jim O''Connor</name> By looking at the example described here, that is supposed to replace ' with '', I constructed the following script: <xsl:s...

SELECT DISTINCT on a field not appearing in recordset?

I want to do a SELECT DISTINCT guid, ..., but I don't want guid appearing in the recordset. How do I do this? ...

SQL Server 2008 - Update a temporary table

Hello, I have stored procedure in which I am trying to retrieve the last ticket completed by each user listed in a comma-delimited string of usernames. The user may not have a ticket associated with them, in this case I know that i just need to return null. The two tables that I am working with are defined as follows: User ---- UserNam...

SQL 2005 Merge / concatenate multiple rows to one column

Hi, We have a bit of a SQL quandry. Say I have a results that look like this... 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | B 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | C 61E77D90-D53D-4E2E-A09E-9D6F012EB59C | D 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E 7ce953ca-a55b-4c55-a52c-9d6f012ea903 | F is...

Highlight row in report?

I have a SSRS report which displays hundred of rows. I was wondering if there is anyway i can highlight the rows so that i can easily know on which row i am while accessing the report. Any thoughts? ...

SQL for sorting boolean column as true, null, false

My table has three boolean fields: f1, f2, f3. If I do SELECT * FROM table ORDER BY f1, f2, f3 the records will be sorted by these fields in the order false, true, null. I wish to order them with null in between true and false: the correct order should be true, null, false. I am using PostgreSQL. ...

What is the Null Character literal in TSQL?

I am wondering what the literal for a Null character (e.g. '\0') is in TSQL. Note: not a NULL field value, but the null character (see link). I have a column with a mix of typical and a null character. I'm trying to replace the null character with a different value. I would have thought that the following would work but it is unsucce...

MySQL - how to retrieve columns in same row as the values returned by min/mx

I couldn't frame the question's title properly. Suppose a table of weekly movie earnings as below: MovieName <Varchar(450)> MovieGross <Decimal(18)> WeekofYear <Integer> Year <Integer> So how do I get the names of top grossers for each week of this year, if I do: select MovieName , Max(MovieGross) , WeekofYear from earni...

SQL - sub count

Hi I've got some SQL ... SELECT AdviceNo, Registration FROM tblSalesDetail that produces something like ... ADV00001, ABC123 ADV00001, CDE564 ADV00002, FGE432 ADV00003, HUY789 ADV00003, MJS532 ADV00003, JFY428 Can anyone tell me how I'd adjust it to see the following please? ADV00001, ABC123, 1 ADV00001, CDE564, 2 ADV00...

SQL Server Composite Primary Keys

I am attempting to replace all records for a give day in a certain table. The table has a composite primary key comprised of 7 fields. One such field is date. I have deleted all records which have a date value of 2/8/2010. When I try to then insert records into the table for 2/8/2010, I get a primary key violation. The records I am att...

Inserting Strings Without Trailing Spaces SQL

I have a database with a field named Field1 that has 100 nchars per entry. Each time I add a value, it is stored as: "value (100-ValueLength Spaces) " So Basically each stored value has a string of spaces after it. This is getting to be an issue when I try doing: if (value == "Example") because of all of the empty spa...

adodb: what does createparameter do?

in plain english can you please explain to me what does this line mean: Cmd.Parameters.Append _ Cmd.CreateParameter("datapath", adChar, adParamInput, 100) i then have a line that writes data to the datapath column: Cmd.Parameters("datapath").Value = "abc" but i do not understand the purpose of the first line? what is adChar and...

Problem with MySQL query

This time my setup looks like this: one table with galleries names CREATE TABLE GalleriesName ( gallery_id , gallery_name ) and another table with galleries photos CREATE TABLE GalleriesPhotos ( photo_id , photo_gallery_id , photo_name ) What I need is to get all the galleries with one random picture for eac...

rails howto compare datetime ?

hello, i have games in my sqLite DB with the attribute starting_date( t.date :starting_date). i would like to know all the games that have alreday started so i am using this lines of code: Game.find :all,:conditions=>"starting_date <= #{Date.today}" Game.find_by_sql("SELECT * FROM "games" WHERE (created_at < 2010-05-13)") the resu...

Basic SQL query question

Hello, I have the following query: SELECT Base.ReportDate, Base.PropertyCode, Base.FirstName, Base.MiddleName, Base.LastName, Person.FirstName, Person.MiddleName, Person.LastName FROM LeaseT INNER JOIN Base ON LeaseT.LeaseID = Base.LeaseID INNER JOIN Person ON LeaseT.TenantID = Person.ID works fine, ...

Show SQL query results in Sharepoint

Hi All I support Rational clearquest for my company and have been asked to show some of the data in Sharepoint. I can query the data in Clearquest no problem (Using Toad) but have no idea about how to go displaying it in Sharepoint. The data I'm querying contains dates and ideally I'd like to be able display it in a sharepoint calenda...