HOW TO INSERT OLD DATA INTO LOG TABLE BEFORE UPDATE/DELETE USING OUTPUT CLAUSE

Implementing the OUTPUT Clause in SQL Server 2008

 CREATE TEMPERAROY TABLE

create table #tempaduit

(

  id int ,

  Name varchar(50),

  Mobile varchar(50),

  Email varchar(50)

)

create table #temp

(

  id int identity (1,1),

  Name varchar(50),

  Mobile varchar(50),

  Email varchar(50)

)

INSERT INTO #temp (Name,Mobile,Email)

values

('Sunil','9899767132','sk@gmail.com'),

('Anil','78787998989','anil@gmail.com')

-- STORE OLD DATA INTO LOG TABLE BEFORE UPDATE

update #temp

   SET Name='Shyam',Email='s@gmail.com',Mobile='4565445564'

   OUTPUT

   deleted.id,deleted.Name,deleted.Mobile,deleted.Email

   INTO  #tempaduit(id,Name,Mobile,Email)

   WHERE id=1

  

-- STORE OLD DATA INTO LOG TABLE BEFORE DELETE

   DELETE FROM #temp (Name,Mobile,Email)

   OUTPUT

   deleted.id,deleted.Name,deleted.Mobile,deleted.Email

   INTO  #tempaduit(id,Name,Mobile,Email)

   WHERE id=2

  

  

-- GET NEWLY INSERTED DATA AS OUTPUT

   INSERT INTO   #temp

   OUTPUT

   inserted.id,inserted.Name,inserted.Mobile,inserted.Email

   INTO  #tempaduit(id,Name,Mobile,Email)

   values('Anil','9899767132','anil@gmail.com')

GET Nth Maximum Salary in SQL Server

In Below Post I have explained 2 way to get nth maximum salary in SQL Server.

Create Temperary Table  

create table #Employee

(

  Id int   Identity(1,1), 

  Name varchar(10) not null,

  salary numeric(18,2) not null,

  Email varchar(50)

)

insert into #Employee (Name,salary,Email)

values('Manish',1000,'ak@gmail.com'),

('Anill',2000,'Anill@gmail.com'),

('Sunil',1200,'Sunil@gmail.com'),

('Ram',1700,'ram@gmail.com'),

('Arun',1800,'arun@gmail.com'),

('Neha',2000,'Neha@gmail.com'),

('Vivek',2100,'Vivek@gmail.com')

---Nth highest salary using correlated subquery

DECLARE @N int=2

SELECT name, salary ,Email

FROM #Employee e1

WHERE @N-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2

WHERE e2.salary > e1.salary)

--Nth highest salary in SQL SERVER using TOP keyword

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP 2 salary

FROM #Employee

ORDER BY salary DESC

) AS temp

ORDER BY salary

stored procedure for insert into Table from XML Parameter in SQL Server.

--–@XmlData='<Content>

--–<Name>Suneel</Name>

--–<Email>sk@gmail.com</Email>

--–<Mobile>99889898989</Mobile>

--–<Item Course="HSC" Year="2004″ Mark="68 %"></Item>

--–<Item Course="SSC" Year="2006″ Mark="70 %"></Item>

--–<Item Course="B.Sc" Year="2009″ Mark="72 %"></Item>

--–<Item Course="MCA" Year="2012″ Mark="75 %"></Item>

--–<RegNo>70123</RegNo>

--–</Content>'

CREATE PROCEDURE TestProcToInsertUsingXml

(

@XmlData ntext=null

)

AS

BEGIN

SET NOCOUNT ON;

declare @TempXML xml

set @TempXML=CONVERT(xml,@XmlData)

declare @hDoc int

EXEC sp_xml_preparedocument  @hDoc output,@TempXML

Declare @Email varchar(50),@RegNo varchar(10)

select @RegNo=Q.RegNo

from OPENXML (@hDoc,N'/Content')

WITH(

RegNo varchar(10) 'RegNo'

)Q

IF NOT EXISTS(SELECT  RegNo FROM tblStudent where RegNo = @RegNo)

BEGIN

--— insert Into Registration Table

insert into tblStudent(StudentName,EmailId,MobileNo,RegId)

select Name,Email,Mobile,RegNo

from OPENXML (@hDoc,N'/Content')

WITH(

Name   varchar(10) 'Name',

Email  varchar(10) 'Email',

Mobile varchar(10) 'Mobile',

RegNo  varchar(10) 'RegNo'

)Std

set @RegNo=SCOPE_IDENTITY();

--— Insert Into Qualification Table

insert into tblStudentQfy(RegNo,Course,PassingYear,MarkObtained)

select @RegNo,Course,PYear,Mark

from OPENXML (@hDoc,N'/Content/Item')

WITH(

Course varchar(10) '@Course',

PYear  varchar(10) '@Year',

Mark   varchar(10) '@Mark'

)Qfy

END

else

BEGIN

Update  tblStudent

SET Name =s.Name,

Email=s.Email,

Mobile=s.Mobile

from OPENXML (@hDoc,N'/Content')

WITH(

Name    varchar(50) 'Name',

Email   varchar(10) 'Email',

Mobile  varchar(10) 'Mobile',

RegNo   varchar(10) 'RegNo'

) s

where @RegNo=RegNo

END

END

Stored Procedure to return XML data from select query in sql server.

--— =============================================

--— Author:ABC

--— Create date: Date

--— Description: Decs

--— =============================================

CREATE procedure   sp_GetEmployeeList

(

@EmpId int=null

)

as

begin

select  EmpId,Name,City,Telephone

from Employee

where   EmpId=@EmpId

order by Name

for XML AUTO, ROOT('Employees')

end

Send alert email from database using CURSOR.

--— =============================================

--— Author: S K

--— Create date: Date

--— Description: Desc

--— =============================================

CREATE PROC Sp_AlertMail

AS

declare @EmpId varchar(50)

Declare @Name varchar(50)

Declare @Email varchar(50)

Declare @Moblie varchar(50)

Declare Mail_Alert Cursor For

SELECT EmpId,Name,Email,Moblie FROM Employee

WHERE  DATEDIFF(DAY,CONVERT(DATETIME,CONVERT(DATE,GETDATE())),CreatedDate) IN(1,2)

OPEN Mail_Alert

FETCH NEXT FROM  Mail_Alert Into @EmpId,@Name,@Email,@Service,@Moblie

WHILE(@@fetch_status=0)

BEGIN

begin

Declare @Body varchar(max)

SET @Body = 'your text….'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Alert_From_Database',

@recipients=@EMAIL,

@copy_recipients ='abc@gmail.com' ,

@subject = 'Alert Mail',

@body = @Body,

@body_format = 'HTML';

End

FETCH  Mail_Alert Into @EmpId,@Name,@Email,@Service,@Moblie

END

CLOSE Mail_Alert

DEALLOCATE Mail_Alert

Filter Record from table if data stored in comma (,) seprated.

CREATE TABLE TBL_USER

(

 id int,

 LanguageKnow varchar(300)

)

insERT INTO TBL_USER VALUES

(1,'HI,EN'),

(2,'HI,EN,FR')

I WANT TO FILTER THOSE RECORDS THAT  CONTAINS 'HI,FR'


Query to filter-

DECLARE @ListValue varchar(50)='FR'

SELECT * FROM  TBL_USER T WHERE

1 = case when isnull(@ListValue,0)='' then 1 when isnull(@ListValue,0)!='0'

and (exists(select A.Item from SplitString(@ListValue,',') A

inner join (select Item from SplitString(T.LanguageKnow,',')) B on A.Item=B.Item))then 1 else 0 end



You need to create below  spilt string function- 

---Function ———–

CREATE FUNCTION [dbo].[SplitString]

(

@SplitStr nvarchar(max),

@SplitChar nvarchar(5)

)

RETURNS @RtnValue table

(

Item nvarchar(max)

)

AS

BEGIN

Declare @Count int

Set @Count = 1

While (Charindex(@SplitChar,@SplitStr)>0)

Begin

Insert Into @RtnValue (Item)

Select

Item = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))

Set @Count = @Count + 1

End

Insert Into @RtnValue (Item)

Select Item = ltrim(rtrim(@SplitStr))

Return

END

Insert / Fech Data into database using conncection object.

strConnection = objBase.getConnectionString();
using (sqlCon = new SqlConnection(strConnection))
{
sqlCommand = new SqlCommand();
sqlAdapter = new SqlDataAdapter();
sqlCommand.CommandText = “SP_NAME”;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlCon;

sqlCommand.Parameters.Add(“@USerID”, SqlDbType.VarChar).Value = USERID
sqlCommand.Parameters.Add(“@QueryType”, SqlDbType.VarChar).Value = QueryType;

#region– For Select DATA
sqlAdapter.SelectCommand = sqlCommand;
if (!sqlCon.State.Equals(ConnectionState.Open))
sqlCon.Open();
sqlAdapter.Fill(myDS);
#endregion

#region —– For Insert
// Add output Parameter

SqlParameter param = new SqlParameter(“Output”, SqlDbType.VarChar, 20);
param.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(param);

if (!sqlCon.State.Equals(ConnectionState.Open))
sqlCon.Open();

iTripId = sqlCommand.ExecuteNonQuery();
sMsg = param.Value.ToString();
#endregion
}