Friday, January 5, 2018

How to CREATE TABLE in sql server


This is basic syntax of the CREATE TABLE in sql server

Syntax
CREATE TABLE table_name
(
column_name1 datatype,
column_name2 datatype,
column_name3 datatype,
column_name4 datatype,
)


For Example if you want to create the Student table, the statement would be like,

CREATE TABLE student
(
id int identity(1,1),
name varchar(100),
mobile varchar(10),
age int
)

You can verify if your table has been created successfully by looking at the message displayed like Commands completed successfully.

Thursday, December 28, 2017

How to convert json in sql server using For Json

How to convert json in sql server using For Json

Select * from Employee
for json path

Output

[{"EMP_ID":1,"EMP_NAME":"TEST1","EMP_SALARY":50000},{"EMP_ID":2,"EMP_NAME":"TEST2","EMP_SALARY":45000},{"EMP_ID":3,"EMP_NAME":"TEST3","EMP_SALARY":55000},{"EMP_ID":4,"EMP_NAME":"TEST4","EMP_SALARY":50000},{"EMP_ID":5,"EMP_NAME":"TEST5","EMP_SALARY":40000}]

Tuesday, December 12, 2017

How to use SQL Server REPLACE() Function


How to use SQL Server REPLACE() Function


Syntax

REPLACE(Your_String, string_to_replace, replacement_string)


SELECT REPLACE('1,2,3,4', ',' , '/ ')

Result => 1/ 2/ 3/ 4

Monday, December 11, 2017

How to convert 24 hour formatted time into 12 hour formatted time in SQL server ?

How to convert 24 hour formatted time into 12 hour formatted time in SQL server ?


DECLARE @TIME TIME
SET @TIME = '17:45:00'
SELECT CONVERT(VARCHAR(15),@TIME,100)

Result : 5:45PM

Saturday, December 9, 2017

How to remove duplicate record in sql table

Hello Friends here are example of how to remove duplicate record in table in SQL server

 This is a table with duplicate record

SELECT * FROM PRODUCTMASTER
PD_ID PD_NAME PD_AMOUNT
1 PRD1 500
2 PRD2 550
3 PRD3 500
5 PRD3 500



Fist of all we are find row number using ROW_NUMBER() Funcation

SELECT
ROW_NUMBER() OVER(ORDER BY PD_NAME) AS SRNO,
* FROM PRODUCTMASTER


SRNO PD_ID PD_NAME PD_AMOUNT
1 1 PRD1 500
2 2 PRD2 550
3 3 PRD3 500
4 5 PRD3 500


Now we can PARTITION duplicate record using PARTITION

SELECT
ROW_NUMBER() OVER(PARTITION BY PD_NAME  ORDER BY PD_NAME) AS SRNO,
* FROM PRODUCTMASTER


SRNOPD_IDPD_NAMEPD_AMOUNT
11PRD1500
12PRD2550
13PRD3500
25PRD3500

Then after run this queury for delete duplicate record

WITH TBL AS (
SELECT
ROW_NUMBER() OVER(PARTITION BY PD_NAME  ORDER BY PD_NAME) AS SRNO,
* FROM PRODUCTMASTER
)
DELETE FROM TBL WHERE SRNO !=1

Friday, December 8, 2017

How to backup table or copy table in sql server

How to backup table or copy table in sql server

Copy or Backup All Column
SELECT * INTO  EMPLOYEE_BACKUP FROM EMPLOYEE

Copy or Backup Selectd Column
 
SELECT EMP_NAME,EMP_SALARY INTO EMPLOYEE_SELECTDBACKUP  FROM EMPLOYEE