SQL Server – Prakash Rathod

Cube and Rollup Operator in SQL Server 2008

CUBE operator is used with Group by clause. It use to get the subtotal and grand total of all permutation of columns provided by the CUBE operator. ROLLUP operator is used with Group by clause. It…

Insert multiple rows into a table from another table and get identity values

Let me explain the case first, so you will get the idea when this is required to use. In the database, there is more than one tables which store the future values and will move on…

SQL Server Transaction

BEGIN TRANSACTION BEGIN TRY — put SQL commands here INSERT/UPDATE/Delete — if successful – COMMIT the work COMMIT TRANSACTION END TRY BEGIN CATCH — handle the error case (here by displaying the error) SELECT ERROR_NUMBER() AS…

MSSQL Selecting top 10 but include columns with duplicate values

SAMPLE RECORDS Query for getting top 10 records based on the company values with duplicate values select T.Country ‘Text’, T.Company’Value’ from ( select Country , Company, dense_rank() over(order by Companydesc) as rn from TableName ) T…

Attendance UNPIVOT first then Pivot for count present, absent etc in SQL

Sample data download: here Declare @tempAttendance table(EmpID int, EmpName varchar(50), edate int, ecode varchar(5)) insert into @tempAttendance SELECT empid, empname, code as ‘edate’, value as ‘ecode’ FROM [dbo].[hattendance] UNPIVOT ( value FOR code IN ( [1],…

Dynamically create columns in a temp table and generate pivot report

Sample Data download: http://goo.gl/lcOf8z Generate excel report as below. Username, Address, City, State, Zip, Question 1, Question 2, Question 3, ….. Prakash, test, anand, gujarat, 388001, Answer 1, Answer 2, Answer 3,.. Chirag, test, anand, gujarat,…

function for spliting a string and return a table in SQL Server

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[unqSplit](@String nvarchar(4000), @Delimiter char(1)) RETURNS @Results TABLE (value nvarchar(4000)) AS –this function takes two parameters; the first is the delimited string, the second is the delimiter…

How to schedule and automate backups of SQL Server databases in SQL Server Express

Create store procedure. // Copyright © Microsoft Corporation. All Rights Reserved. // This code released under the terms of the // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.) USE [master] GO /****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/ SET ANSI_NULLS…

How to compare two tables of different database – SQL Server

To find records which exist in source table but not in target table: SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.Id = t1.Id) or SELECT * FROM t1 LEFT OUTER JOIN…

User Dynamic query for search records and use uniqueidentifier

Declare @SearchText nvarchar(max) set @SearchText = ‘sah.CheckInCheckOutDateTime>=”2011-09-28 10:00:00.000”’ Declare @SchoolYearId uniqueidentifier set @SchoolYearId=’25511F7F-8F87-4085-A242-2004F4419D36′ Declare @Str as nvarchar(max) set @Str=’ select s.LastName +”, ” + s.FirstName as ”Name”,CheckInCheckOut,sah.CheckInCheckOutDateTime as ”ClockOut” from StaffAttendenceHistory sah join Staffschoolyear ssy on…