How to perform an IF…THEN in an SQL SELECT? How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product Answer: The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server SELECT CAST( CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END AS bit) as Saleable, * FROM Product You only need

How can I remove duplicate rows? What is the best way to remove duplicate rows from a fairly large table (i.e. 300,000+ rows)? The rows of course will not be perfect duplicates because of the existence of the RowID identity field. MyTable ----------- RowID int not null identity(1,1) primary key, Col1 varchar(20) not null, Col2 varchar(2048) not null, Col3 tinyint not null Answer: Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to

Inserting multiple rows in a single SQL query? I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Idand Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "London Office"); INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office"); Can I insert all 4 rows in a single SQL statement? Best Answer: In SQL Server 2008 you can insert multiple rows using a single SQL INSERT

How to return the date part only from a SQL Server datetime datatype SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 Answer: On SQL Server 2008 and higher, you should convert to date: SELECT CONVERT(date, getdate()) On older versions, you can do the following: SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date)) for example SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) gives me 2008-09-22 00:00:00.000 Pros: No varchar<->datetime conversions required No need to think about locale http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype

Concatenate many rows into a single text string? Consider a database table holding names, with three rows: Peter Paul Mary Is there an easy way to turn this into a single string of Peter, Paul, Mary? answer: I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily. If there is a table called STUDENTS SubjectID StudentName ---------- ------------- 1 Mary

Insert results of a stored procedure into a temporary table How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but using a stored procedure that returns data, is not quite the same. select * into tmpBusLine from exec getBusinessLineHistory '16 Mar 2009' Output message: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'. I