String_Agg Feature in Sql 2017

The easiest way to List the Tables with Columns as Comma Separated 

SELECT t.name[Table Name],STRING_AGG (c.name, ‘,’) AS ColumnNameCommaSeperated
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
GROUP BY t.name
ORDER BY 1

Advertisements

Output Clause Archive

Output clause is always my all-time favourite feature in SQL. A simple method of archiving the table is shown below.

CREATE TABLE #Employees
(
ID INT IDENTITY(1,1),
EmpNo VARCHAR(100),
EmployeeName VARCHAR(200)
)

CREATE TABLE #EmployeesArchive
(
ID INT IDENTITY(1,1),
EmpNo VARCHAR(100),
EmployeeName VARCHAR(200)
)

INSERT INTO #Employees (EmpNo,EmployeeName)
SELECT '1001','Madav' UNION ALL
SELECT '1002','Rajiv' UNION ALL
SELECT '1003','Dev' UNION ALL
SELECT '1004','Steve'

SET IDENTITY_INSERT #EmployeesArchive ON
DELETE FROM #Employees
OUTPUT
DELETED.ID,DELETED.EmpNo,DELETED.EmployeeName
INTO #EmployeesArchive (id,EmpNo,EmployeeName)
WHERE ID>2
SET IDENTITY_INSERT #EmployeesArchive OFF

SELECT * FROM #Employees
SELECT * FROM #EmployeesArchive

DROP TABLE #Employees
DROP TABLE #EmployeesArchive


Continue reading “Output Clause Archive”