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
