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

   

Leave a Reply

Your email address will not be published.