Feeds:
Posts
Comments

Archive for November, 2011

CREATE
TABLE Sales

(

[ProductCategoryName]
NVARCHAR(50),

[ProductName]
NVARCHAR(50),

[SalesAmount]
MONEY

)

GO

 

INSERT
Sales

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Components’,‘Road-650 Black’,699.0982
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Black’,3374.99
UNION ALL

SELECT
‘Bikes’,‘Mountain-100 Silver’,3399.99
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-150 Red’,3578.27
UNION ALL

SELECT
‘Components’,‘Road-650 Red’,699.0982

GO

 

SELECT
* FROM
Sales

GO

 

 

GROUP BY

SELECT
ProductCategoryName,ProductName,SUM(SalesAmount)AS TotalSalesAmount FROM
Sales

GROUP
BY ProductCategoryName,ProductName

 

 

WITH ROLLUP 

SELECT
ProductCategoryName,ProductName,SUM(SalesAmount)AS TotalSalesAmount FROM
Sales

GROUP
BY ProductCategoryName,ProductName

WITH
ROLLUP

 

 

Read Full Post »

 

IF
EXISTS (SELECT 1 FROM
sysobjects WHERE type = ‘U’ and name = ‘tblname’)

BEGIN

 
DROP TABLE
tblname

END

GO

 

Read Full Post »

For one table

sp_spaceused ‘TABLE_NAME’

 

For all tables

EXEC
sp_msForEachTable ‘EXEC
sp_spaceused ”?”’

 

–OR

 

CREATE
TABLE #TableSize

(

   
SEQ INT IDENTITY(1,1) ,

   
name NVARCHAR(200),

   
rows BIGINT,

   
reserved VARCHAR(100),

   
data VARCHAR(100),

   
index_size VARCHAR(100),

   
unused VARCHAR(100)

)

 

INSERT
#TableSize

EXEC
sp_msForEachTable ‘EXEC
sp_spaceused ”?”’

 

SELECT * FROM
#TableSize

Read Full Post »

 

EXEC
SP_HELPDB

 

–OR

 

CREATE
TABLE #DatabaseSize

(

            NAME VARCHAR(100),

            DB_SIZE
VARCHAR(100),

            OWNER VARCHAR(100),

            DBID INT,

            CREATED
DATETIME,

            STATUS VARCHAR(500),

            COMPATIBILITY_LEVEL
INT

)

 

INSERT
INTO #DatabaseSize

EXEC
SP_HELPDB

 

SELECT
NAME,DB_SIZE
FROM #DatabaseSize

 

Read Full Post »

SELECT

     Restore_Date, 

     destination_database_name,

     User_Name

FROM
MSDB.dbo.RestoreHistory

ORDER BY
Restore_Date DESC

Read Full Post »

 

SELECT ss.last_run_outcome /*1-Success,0-Fail*/

 

FROM msdb.dbo.sysjobs s INNER JOIN msdb.dbo.sysjobservers ss

 

ON s.job_id = ss.job_id

 

WHERE s.name= ‘Job_Name’

 

Read Full Post »

CREATE
TABLE  tbl

(

ID 
INT IDENTITY(1,1),

NAME
VARCHAR(100)

)

GO

 

INSERT
tbl

SELECT
‘NAME1’

UNION ALL

SELECT
‘NAME1’

UNION ALL

SELECT
‘NAME2’

GO

 

 

WITH
CTE_Duplicate

AS

(

SELECT
Rank=ROW_NUMBER() OVER ( PARTITION BY
NAME ORDER
BY NAME),*

FROM
tbl

)

 

DELETE
FROM CTE_Duplicate

WHERE
Rank>1

GO

 

 

 

 

Read Full Post »

 

–Create table

CREATE
TABLE tbl

(

 Product_Name
VARCHAR(100),

 QTY
INT

)

GO

 

–Insert rows

INSERT
tbl

SELECT
‘prd1’,10

UNION ALL

SELECT
‘prd2’,20

UNION ALL

SELECT
‘prd3’,30

UNION ALL

SELECT
‘prd1’,40

UNION ALL

SELECT
‘prd2’,50

GO

 

SELECT
* FROM 
tbl

GO

 

 

–Dynamic Pivot

DECLARE
@PivotColumn VARCHAR(MAX)

SELECT
@PivotColumn =

                                                COALESCE(

                                                @PivotColumn
+ ‘,[‘ + cast(Product_Name as
varchar)
+ ‘]’,

                                                ‘[‘ +

                                                cast(Product_Name as varchar)+ ‘]’

                                                )

                                                FROM (

                                                                        SELECT DISTINCT
Product_Name FROM tbl)b

 

DECLARE
@PivotSQL NVARCHAR(MAX)

SET
@PivotSQL = N

                                                SELECT *

                                                FROM (

                                                SELECT

                                                Product_Name,QTY
from tbl

                                                ) AS

                                                PivotData

                                                PIVOT (

                                                SUM(QTY)

                                                FOR Product_Name

                                                IN (

                                                ‘ +

                                                @PivotColumn
+

                                                )

                                                ) AS

                                                PivotTable’

 

EXECUTE(@PivotSQL)

GO

 

 

Read Full Post »

Sometimes we don’t need static file name reason can be many, you already have a file with the same name in your target
folder and you don’t want to override or you can’t override it or maybe you want to keep track of all historical files or may be because of some xyz reasons.

Right click on Control Flow pane, choose variables, and then add three variables FilePath, FileName and FileExtension of data type String

1. FilePath-: Assign your directory path say C:\Output

2. FileName:-Select property pane of this variable and modify below two properties

EvaluateAsExpression=TRUE

Expression = "OutputFileName"+(DT_STR,4, 1252)  YEAR( GETDATE()  )+RIGHT("0" + (DT_STR, 2, 1252)  MONTH(GETDATE()  ), 2) + RIGHT("0" + (DT_STR, 2, 1252)  DAY( GETDATE() ), 2)

 

Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

 

3. FileExtension: Assign extension value “.txt”



4. Next, Right-click the Flat File connection and choose Properties and set below two properties

DelayValidation=TRUE 

ConnectionString =Choose “Connection String” property and assign value of Expression like below

@[User::FilePath]+@[User::FileName]+ @[User::FileExtension]

Click on button “Evaluate Expression” at the bottom left of above window to check for any errors

 

Read Full Post »

%d bloggers like this: