Restore Database using T-SQL

Generate New Database from blank database bak file

           

DECLARE @Query VARCHAR(MAX)

DECLARE @mdfPath VARCHAR(500)

DECLARE @ldfPath VARCHAR(500)

--RESTORE FILELISTONLY FROM DISK='C:\Program Files\Microsoft SQL  Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\oldDB.bak'

-- Get Logical names to restore database.

SET @mdfPath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\newDb.mdf'

SET @ldfPath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ newDb.ldf'

 RESTORE DATABASE newDB FROM DISK='C:\Program Files\Microsoft SQL Server\

                                                            MSSQL11.MSSQLSERVER\MSSQL\Backup\oldDB.bak'

 WITH

 MOVE 'oldDB' TO @mdfPath,

 MOVE 'oldDB_log' TO @ldfPath

Get days list by Month in SQL

DECLARE @year INT = 2014

DECLARE @Month INT = 1

;WITH mths AS(

SELECT 1 AS mth, DATENAME(WEEKDAY, cast(@year*100+@Month AS VARCHAR) + '01')  as monthname,CONVERT(DATETIME, cast(@year*100+@Month AS VARCHAR) + '01') AS mnthDT

UNION ALL

SELECT mth+1 AS mth, DATENAME(WEEKDAY, cast(@year*100+@Month AS VARCHAR) + (CASE LEN(CAST((1+mth) as VARCHAR))  WHEN 1 THEN '0'+ CAST((1+mth) as VARCHAR) ELSE CAST((1+mth) as VARCHAR) END) ) AS monthname,

CONVERT(DATETIME,cast(@year*100+@Month  AS VARCHAR) + (CASE LEN(CAST((1+mth) as VARCHAR))  WHEN 1 THEN '0'+ CAST((1+mth) as VARCHAR) ELSE CAST((1+mth) as VARCHAR) END)) AS mnthDT FROM mths WHERE mnthDT < (SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, mnthDT) + 1, 0)))

)

SELECT * FROM mths

Delete duplicate records using Cursor and ROW Count

Delete duplicate records except one record.

Declare @ID int

Declare  @Name varchar(100)

set @ID=0

Set @name=''

DECLARE Employee_Cursor CURSOR FOR

select Count(*) as ID,name from SRC group by Name

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor INTO

 @ID,@Name;

WHILE @@FETCH_STATUS = 0

   BEGIN

                        Declare @Count int

                        set @Count=       @ID-1

                        set ROWCOUNT @Count

                        if(@Count>1)

                        begin

                                    delete from SRC where name like @Name

                        end

                        --print @name

                        set ROWCOUNT 0

                                     

      FETCH NEXT FROM Employee_Cursor INTO

 @ID,@Name;

   END;

CLOSE Employee_Cursor;

DEALLOCATE Employee_Cursor ;

GO

OFFSET FETCH in SQL Server

If you are given task to do custom paging then what you will do ?  you will write where condition and use row_number() function. But here SQL provide new way to get records as per our requirement for paging. Just write below query and pass start Record number and total record to get and you will gget your result :

SELECT * FROM  Table1

ORDER BY 1

OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Arguments Explanation :

OFFSET : Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.

FETCH (FIRST|NEXT): Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably

Why Should be a fan as you are star of tomorrow....

Pivot Table with Grand Total in SQL

At first crerate a table By following query

CREATE TABLE [dbo].[tblProduct](

          [Vendor] [varchar](50) NULL,

          [Product] [varchar](50) NULL,

          [TotalSell] [int] NULL

)

Insert records in Table by this queries:

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Dell', N'LED', 50)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Dell', N'Mouse', 10)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'Processor', 101)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'MotherBoard', 50)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'cabinet', 12)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'Keyboard', 60)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Microsoft', N'OS', 1005)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'OS', 20)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Microsoft', N'Mouse', 100)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'iBall', N'LED', 10)

GO

INSERT [dbo].[tblProduct] ([Vendor], [Product], [TotalSell]) VALUES (N'Intel', N'cabinet', 20)

GO

Now You can get result with sell count of each item by its vendor with Total and Grand Total

SELECT Vendor, ISNULL(LED,0) AS LED,ISNULL(Mouse,0) AS Mouse,

                    ISNULL(Processor,0) AS Processor,ISNULL(MotherBoard,0) AS MotherBoard,

                    ISNULL(cabinet,0) AS cabinet,ISNULL(Keyboard,0) AS Keyboard,

                    ISNULL(OS,0) AS OS, Total

FROM

(

          SELECT *, SUM(TotalSell) OVER (PARTITION BY Vendor) AS Total FROM tblProduct

)a

PIVOT

  (SUM(TotalSell) FOR Product IN (LED,Mouse,Processor,MotherBoard,cabinet,Keyboard,OS)

)b

UNION

SELECT 'Grand Total', SUM(ISNULL(LED,0)) AS LED, SUM(ISNULL(Mouse,0)) AS Mouse,

                            SUM(ISNULL(Processor,0)) AS Processor,

                            SUM(ISNULL(MotherBoard,0)) AS MotherBoard,

                            SUM(ISNULL(cabinet,0)) AS cabinet,

                            SUM(ISNULL(Keyboard,0)) AS Keyboard,

                            SUM(ISNULL(OS,0)) AS OS,SUM(Total) as Total

FROM

(

          SELECT *, SUM(TotalSell) OVER (PARTITION BY Vendor) AS Total FROM tblProduct

)a

PIVOT

          (SUM(TotalSell) FOR Product IN (LED,Mouse,Processor,MotherBoard,cabinet,Keyboard,OS)

)b

ORDER BY Total

Record will like below:

Table valued parameter in SQL Server

Table Valued ParameterWhen you want bulk insert in SQL server database then this is best way to do this stuff. Here is example of SQL table valued parameter.Create parameter:

CREATE TYPE [dbo].[PersonType] AS TABLE(

          [Name] [varchar](50) NULL,

          [EmailID] [varchar](50) NULL

)

GO

Store Procedure for insert value to table :

CREATE PROC [dbo].[sp_Insert]

(

   @PersonType PersonType READONLY

)

AS

BEGIN

          INSERT INTO tblInfo (Name,EmailID) -- Table tblInfo has two columns

          SELECT Name,EmailID FROM @PersonType

END

Store Procedure for Update table :

CREATE PROC [dbo].[SP_UPDATE]

(

   @PersonType PersonType READONLY

)

AS

BEGIN

          UPDATE tbl SET Name = tp.Name

                              ,EMailID = tp.EmailID

          FROM tblInfo tbl INNER JOIN @PersonType as tp ON tbl.EmailID = tp.EmailID

END

Now from Coding side, How to pass parameter from C#.

              public void INSERT()

        {

            SqlConnection con = new SqlConnection(@"Connection String");

            SqlCommand cmd;

            if (test.CommandArgument == "1")

            {

                cmd = new SqlCommand("SP_Insert", con);

            }

            else

            {

                cmd = new SqlCommand("SP_UPDATE", con);

            }

            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para = new SqlParameter("@PersonType", 

                                             System.Data.SqlDbType.Structured);

           // para.TypeName = "dbo.PersonType";    /// For inline Query

            para.Value = GetName(txtName.Text, txtEmail.Text);

            cmd.Parameters.Add(para);

            con.Open();

            int i = cmd.ExecuteNonQuery();

            con.Close();

            cmd.Dispose();

        }

        public DataTable GetName(string Name, string Email)

        {

            DataTable dt = new DataTable();

            dt.Columns.Add("Name");

            dt.Columns.Add("Email");

            DataRow dr = dt.NewRow();

            dr["Name"] = Name;

            dr["Email"] = Email;

            dt.Rows.Add(dr);

            return dt;

        }