Select Into Multiple Variables

DECLARE @x nvarchar(max)
DECLARE @y nvarchar(max)
select @x=name,@y=city FROM (SELECT TOP 1 name, city FROM Table1) a
SELECT @x, @y

@_variable_in

When declaring input variables to a stored procedure in MS SQL Server, it is sometimes beneficial to use

@_variable_in instead of @variable_in

sql: easy datetime formatting to varchar

convert(varchar, FIELD_NAME, FORMAT_NUMBER_FROM_CHART)

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

sql: get just the hour from a datetime

SELECT DATEADD(hh, 13, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))

probably a fancier way to keep the hour information, but you can just in an hour variable there to add it back in.

replace getdate() with your date field

SQL: Insert from another table

INSERT INTO Table1 (a, b)     

SELECT c, d     

FROM Table2     

WHERE c = 'something'

SQL 2005: Delete Records Using Another Table

DELETE tobeupdated_table FROM tobeupdated_table  a

WHERE a.tableID = @ID_in

       AND a.otherTableID IN 

           ( SELECT otherTableID  FROM otherTable b

                    WHERE b.someField = @someValue_in

           );

SQL: Update a table using another table

UPDATE Table1
SET Table1.fieldToBeUpdated=
(SELECT Table2.fieldToUpdateFrom
FROM Table2
WHERE Table2.commonID= Table1.commonID)
)

WHERE EXISTS (
SELECT Table2.fieldToUpdateFrom
FROM Table2
WHERE Table2.commonID= Table1.commonID)
)

SQL 2005: Unpivot

SELECT recordID, oldColumnNames , newValue
FROM (SELECT recordID, [column1], [column2], [column3] FROM Table) AS p
UNPIVOT (newValue FOR oldColumnNames IN ([column1], [column2], [column3])) AS s

ok this is a little weird bear with me.

its going to take column1, 2, 3 and combine their data into one column. It will create a new column which I've called oldColumnNames and the old column name will be value of the oldColumnNames column.

so

id, column1, column2, column3
1, a, b, c
2, d, e, f

becomes
id, oldColumnNames, newValue
1, column1, a
1, column2, b
1, column3, c
2, column1, d
2, column2, e
2, column3, f

SQL: Insert CSV into Table

BULK INSERT OrdersBulk
FROM 'c:\file.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

stole it from here

SQL: Sequence Your Result Set

Say you want to order your result set in a specific way besides just ascending or descending. Here is a simple way to do that.

SELECT your_field,
CASE WHEN your_field = 'c' THEN 0
WHEN your_field = 'a' THEN 1
WHEN your_field = 'b' THEN 2
ELSE 3
END AS order_sequence
FROM your_table
ORDER BY order_sequence

Downside is the query will return an extra field of data (order_sequence)