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
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
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.
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
