Tuesday, July 22, 2008

SQL Tips: Using MERGE SQL statement in SQL 2008


... And still one more from the same generous source ...

SQL Server 2008's new MERGE statement allows you to insert, update, or delete data based on certain join conditions in the same statement.

MERGE SalesArchive AS SA
USING (
    SELECT
        CustomerID,
        LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
        TotalSalesAmount = SUM(SaleAmount),
        TotalSalesCount = COUNT(*)
    FROM SalesFeed
    GROUP BY CustomerID
) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount)
ON (SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate )

WHEN NOT MATCHED THEN
INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate)
    VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE())
WHEN MATCHED THEN
UPDATE

    SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount,
            SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount,
            SA.UpdatedDate = GETDATE();


Also, there is a new ability to pass a table variable to the stored procedures:

DECLARE @MyTable TABLE (Col1 INT, Col2 Varchar(100))
EXEC MySP @Par1 = @MyTable OUTPUT

Monday, July 14, 2008

SQL Tips: Using Regular Expressions in SQL Query


One more; courtesy of our greate DBA - K. Fridman:

DECLARE @RegExp VARCHAR(100)
SET @RegExp = '%[^a-zA-Z0-9._-]%'
SELECT *
FROM Clients
WHERE PATINDEX(@RegExp, UserName) > 0


SQL Tips: Search String In All SP's


This is nice one; courtesy of our great DBA - Kostya Fridman:

SELECT name, OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%Search String%'