SQL Server

                                    SQL Server

Autogenerate the varchar:

Example:(USR_1,USR_2,USR_3)

select isnull(max(convert(int,substring(Col_Name,5,len(Col_Name)))),0)   from  Table_Name

IS Null : Itaccepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.

Example: 

SELECT ISNULL ('32', 'W3Schools.com');

SELECT ISNULL (NULL, 'W3Schools.com');

COALESCE :COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter.

EX: SELECT COALESCE ('32', 'W3Schools.com');

Exception Handing:

BEGIN TRY

     Begin Tran

       -- Statements

     Commit

END TRY
BEGIN CATCH

   if(@@Rowcount > 0)   Rollaback;

END CATCH

Case Statement :

CASE
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   WHEN condition_n THEN result_n
   ELSE result
END

Deleting the duplicate records using row number():

step :1 Create the table

create table EmpDup(empid int not null ,name varchar(50) );

step:2 Inserting the Data

insert into EmpDup values(1,'Andy')

insert into EmpDup values (1,'Andy')

insert into EmpDup values (2,'Chris')

insert into EmpDup values(3,'Bill')

insert into EmpDup values(3,'Bill')

insert into EmpDup values (3,'Bill') 

step 4: check the duplicate rows 

select empid,name , row_number() over(partition by empid,name order by empid,name ) rownumber from EmpDup 

step 3: Deleting Duplicate rows

With Temp_Duplicates as

(select empid,name , row_number() over(partition by empid,name order by empid,name ) rownumber from EmpDup )

delete from Temp_Duplicates where rownumber >1

step 4: display the records

select * from EmpDup

Xml File reading in sql server?

step 1: declare the xml file data.

declare @xmldata xml='<ArrayOfCustomers xmlns:xsd="http://www.w3.org/2001/XMLSchema"        

                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <Customer>

       <ItemId>1</ItemId>

       <Value>Mr Smith</Value>

      <price value="12345"/> 

   </Customer>

   <Customer>

      <ItemId>2</ItemId>

      <Value>Mr Bloggs</Value>

      <price value="123456"/>  

   </Customer>

</ArrayOfCustomers>'

step 2: create the temparary and Main table.

 create table Main_customer(itemid int,customerName varchar(50),price decimal(18,2))

 create table temp_customer(itemid int,customerName varchar(50),price decimal(18,2))

step 3: view the data.

SELECT

   temp.value('(ItemId)[1]', 'int') AS 'ItemID',

   temp.value('(Value)[1]', 'Varchar(50)') AS 'Customer Name',

   temp.value('(price[1])/@value', 'decimal(18,2)') AS 'Hari' 

FROM

    @xmldata.nodes('/ArrayOfCustomers/Customer') AS AOC(temp) 

step 4: 

-> truncate temp table each iteration.

-> insert the data into temp table.

-> copy the temp table data into Main table 

truncate table temp_customer

insert into temp_customer 

SELECT

   temp.value('(ItemId)[1]', 'int') AS 'ItemID',

   temp.value('(Value)[1]', 'Varchar(50)') AS 'Customer Name',

   temp.value('(price[1])/@value', 'decimal(18,2)') AS 'Hari' 

FROM

    @xmldata.nodes('/ArrayOfCustomers/Customer') AS AOC(temp)   

  

  INSERT INTO Main_customer  SELECT * FROM temp_customer

  select * from Main_customer 

Converting rows into columns :

Step :1 checking the temp table existing or not.

IF OBJECT_ID('COZMOVMSB2BLiveTest-1..#ActiveAgentSources') IS NOT NULLbegin DROP TABLE #ActiveAgentSources endGo 

Step :2 Creating the Temp table.
CREATE table #ActiveAgentSources  (AgentName nvarchar(200),currency nvarchar(10) ,Code nvarchar(20),columnName nvarchar(20),status nvarchar(5)) Go

Step :3 Inserting the records from temp table.

INSERT INTO #ActiveAgentSources
select  AM.agent_name as AgentName ,AM.agent_currency as currency  ,
Code,  CASE  WHEN Code='6E' THEN 'INDIGO' ELSE  code end   as columnname,
CASE   WHEN BS.status='A' THEN 'YES'    ELSE 'NO' end   [status]   from BKE_ACTIVE_SOURCE BA
inner join bke_agent_sources BS on BA.id = BS.SourceId   
inner join CT_T_AGENT_MASTER  AM on AM.agent_id = BS.agentid
order by Code asc
Go

Step :4 Check the temp table records.

Step :5 Using pivot keyword to convert the rows into column based on columnname what we required.

select * from(  select AgentName,currency, columnname,status  from #ActiveAgentSources /* and where currency='INR'*/) d 
pivot(
 max(status)  
   for columnname in (Indigo,UA, G9, DOTW, RezLive, LOH, TUNES, HotelBeds, FZ, SG, Miki, GTA, HIS, TBOHotel, TA, WST, JAC, EET, Sayara, IX,FI, PK, CZA, G8, RELIGARE, J9, G8CORP, OV, CB, BABYLON)
) piv;
Go

Step :6 Check the result.

Step:7 Drop the temp table.

DROP TABLE #ActiveAgentSources

 Go