-- UTC datetime:
SELECT GETUTCDATE()
-- UTC datetime (long datetime, like SYSDATETIME):
SELECT SYSUTCDATETIME()
Note: in order to get the time zone offset:
Showing posts with label mssql2005. Show all posts
Showing posts with label mssql2005. Show all posts
Delete multiple objects in one statement
DROP TABLE StamTableItai1, StamTableItai2
GO
DROP PROCEDURE SP1,SP2
GO
DROP FUNCTION F1,F2
GO
Delete Duplicate Rows (using CTE)
We can Create a CTE on a table, and add to the table more 'columns', and when CTE is created - DELETE statement can be run on it.
When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
GO
INSERT INTO DuplicateRcordTable
VALUES
(1,1),
(1,1), --duplicate
(1,1), --duplicate
(1,2),
(1,2), --duplicate
(1,3),
(1,4)
GO
SELECT * FROM DuplicateRcordTable
GO
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO
SELECT * FROM DuplicateRcordTable
GO
DROP TABLE DuplicateRcordTable
GO
-------------------------------------------
-- Another example:
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT, Col3 INT)
GO
INSERT INTO DuplicateRcordTable
VALUES
(1,1,5),
(1,1,4), --duplicate
(1,1,1), --duplicate
(1,2,1),
(1,2,5), --duplicate
(1,3,4),
(1,4,1)
GO
SELECT * FROM DuplicateRcordTable
GO
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO
SELECT * FROM DuplicateRcordTable
GO
DROP TABLE DuplicateRcordTable
GO
PIVOT
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) PivotTable
Notes:
- Available from SQL Server 2005.
- When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.
- Pivot columns are specific values from the column of the source table, and can't be declared dynamic.
Subscribe to:
Posts (Atom)