Ordering varchar Column Numerically
SELECT * FROM ProductSales WHERE CustormerID = '12345'
ORDER BY Description, CASE WHEN QuantityOfItems LIKE '%[^0-9]%' THEN 9E99 ELSE CAST(QuantityOfItems AS INTEGER) END
Only Compare Date Without Time
DECLARE @selectedDate datetime
SET @selectedDate = '04/20/2006'
SELECT * FROM ProductSales WHERE datediff(day, @selectedDate, PurchaseDate) = 0
--Get sales on current day:
SELECT * FROM ProductSales WHERE PurchaseDate >= dateadd(day, datediff(day, 0, getdate()), 0)
--Get sales on last 24 hours:
SELECT * FROM ProductSales WHERE PurchaseDate > DateAdd(d,-1,GetDate())
Insert Data Returned From Stored Procedure Into a Table
INSERT INTO ProductAnalysis EXEC('spGetProductsByTime "2005/1/1", "2005/12/31"')
Select And Insert Into New Table
INSERT INTO OrdersBackup(Customer, OrderDate, ShippingCost)
SELECT Customer, OrderDate, ShippingCost FROM Orders;
Copy Table Definition
SELECT * INTO OrdersBackup FROM Orders WHERE 1 IS NULL
--Following will also copy data:
SELECT * INTO OrdersBackup FROM Orders
Identity Handling
SET IDENTITY_INSERT Industry ON
INSERT Department(DepartmentID, Name, Description) Values(1, 'ABC', 'BCD')
SET IDENTITY_INSERT Industry OFF
GO
Delete FROM Department
DBCC CHECKIDENT('Department', RESEED, 0)
Set IDENTITY_INSERT Department OFF
INSERT Department (Name) Values ('IT') -- DepartmentID = 1
Handling Null Field/Parameter
SELECT * FROM Users WHERE LastNam LIKE IsNull(@LastName,'%')
SELECT * FROM Users WHERE LastNam LIKE COALESCE(@LastName,'%')
SELECT COALESCE(BusinessPhone, CellPhone, HomePhone) AS Phone From Users
Change Column Data Type
IF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers' AND COLUMN_NAME = 'Notes' AND DATA_TYPE = 'varchar' )
ALTER TABLE Customers ALTER COLUMN Notes TEXT
--or:
IF (SELECT type_name(xtype) FROM syscolumns
WHERE id = object_id('tblname') AND name = 'colname'
ALTER TABLE Customers ALTER COLUMN Notes TEXT
Case When
SELECT Country = CASE
WHEN CountryCode = 1 THEN 'USA
WHEN CountryCode = 2 THEN 'CANADA'
ELSE 'Other' END
FROM Users
SELECT CASE CountryCode
WHEN 1 THEN 'USA'
WHEN 2 THEN 'CANADA'
ELSE 'Other' END AS Country
FROM Users
SELECT OrderID, SUM(Quantity), SUM
(CASE DiscountID
WHEN DiscountID IS NOT NULL THEN Quantity
ELSE 0 END
) AS DiscountQuantity
FROM Sales GROUP BY OrderID
SELECT FirstName, LastName, RegisterDate FROM Users ORDER BY CASE
WHEN CountryCode = 1 THEN 2
WHEN CountryCode = 2 THEN 1
ELSE 3 END
Multi-Value in One Parameter
CREATE PROCEDURE TestParameters
@idList nvarchar(500)
AS
DECLARE @sql nvarchar(520)
SET @sql = 'SELECT * FROM Products WHERE id IN (' + @idList + ')'
EXEC (@sql)
GO
--Note: potential SQL injection issue with above command.
Table Insertion Trigger
CREATE TRIGGER [dbo].[OrderInsertTrigger]
On [dbo].[OrderDetails]
FOR INSERT
AS
BEGIN
DECLARE @OrderID int, @TotalItem int
SELECT @OrderID = OrderID FROM INSERTED
SELECT @TotalItem = TotalItem FROM Orders WHERE OrderID = @OrderID
IF @TotalItem IS NULL
SET @TotalItem = 1
ELSE
SET @TotalItem = @TotalItem + 1
UPDATE Orders SET TotalItem = @TotalItem WHERE OrderID = @OrderID
END
Select 10 Random Rows From A Table
SELECT TOP 10 * FROM Orders ORDER BY newid()
Interact With Shell Commands
--First you need to turn on xp_cmdshell option:
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
--Inserting c:\data.txt data into a temp table:
CREATE TABLE #tmp(line varchar(2000))
INSERT INTO #tmpEXEC xp_cmdshell 'more <>
