SQL Server: Extract the all Columns from a table in a Line

Hello Friends,

Many times we required all columns from a table in Inserted format and select farmat

here i am using a Stored Procedure for this process You can use and take a enjoy………………!!!!

—————Start Procedure ——————-

CREATE PROCEDURE sp_insertstr

(

@cTableName varchar(50),

@cSourceDb varchar(50) = ”

)

AS BEGIN

SET NOCOUNT ON

DECLARE  @cInsertStr varchar(8000)

DECLARE @cSysCols TABLE ( column_name varchar(50) )
IF @cSourceDb = ” SET @cSourceDb = ‘<sourcedb>’
INSERT @cSysCols SELECT name FROM syscolumns WHERE id = OBJECT_ID(@cTableName) AND name <> ‘TS’
SET @cInsertStr = ” UPDATE @cSysCols

SET @cInsertStr = @cInsertStr + ( CASE WHEN @cInsertStr <> ” THEN ‘, ‘ ELSE ” END )

+ [column_name] SET @cInsertStr = ‘ INSERT ‘ + @cTableName +    ‘ ( ‘ + @cInsertStr + ‘ ) ‘ +    ‘ SELECT ‘ +    ‘  ‘

+ @cInsertStr +    ‘ FROM ‘ + @cSourceDb + ‘.[dbo].’ + @cTableName

PRINT @cInsertStr

END

——End Procedure—————

Execution Result:-

——————-

use nAdventureWorks

GO

—-(Sp_Insertstr <Tablename>)—-

Sp_Insertstr [HumanResources.Employee]

OUTPUT Result

————————-

INSERT HumanResources.Employee ( EmployeeID, NationalIDNumber, ContactID, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate )

SELECT   EmployeeID, NationalIDNumber, ContactID, LoginID, ManagerID, Title, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate FROM <sourcedb>.[dbo].HumanResources.Employee

 

Regards

Manoj Kumar

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *