LinkedIn

Thursday, 5 March 2009

Detect columns and copy a row - t-sql


This is a way to copy a row in a table without caring whether someone has added new columns.



USE [TRAM]

GO
/****** Object: StoredProcedure [dbo].[COPY_Subleases_2] Script Date: 03/04/2009 15:16:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[COPY_Subleases_2]
(
@iSourceTable VARCHAR (150),
@lPrintIt INT,
@CurrentAssetID INT,
@NewAssetID INT,
@CurrentsubleaseID INT OUTPUT,
@executeIt int
)
AS
BEGIN

DECLARE @cName varchar(150)
DECLARE @lCnt int
DECLARE @lColumnList varchar(MAX)

SET NOCOUNT ON;

-- SET @iSourceTable = 'PropertyAnnualProperty'
-- SET @lPrintIt = 1
SET @lColumnList = ''

DECLARE _LIST
INSENSITIVE SCROLL CURSOR

FOR

SELECT column_name

FROM

information_schema.columns

WHERE

table_name = @iSourceTable AND
data_type <> 'timestamp' AND
column_name <> 'SubLeaseID' AND
column_name <> 'AssetID'

ORDER BY
ordinal_position

OPEN _LIST

IF @lPrintIt = 1 print 'Column names returned...' + convert(char(8),@@cursor_rows)

-- Check for an error
IF @@CURSOR_ROWS = 0

BEGIN

GOTO Done_Loop_LIST

END

-- Grab the first record
FETCH FIRST FROM _LIST

INTO @cName

SELECT @lCnt = 0

Loop_LIST:

SELECT @lCnt = @lCnt + 1
SELECT @lColumnList = @lColumnList + '[' + @cName + '],'

FETCH NEXT FROM _LIST

INTO @cName

-- Any more ?

IF @@FETCH_STATUS != -1

BEGIN

GOTO Loop_LIST

END

Done_Loop_LIST:

DEALLOCATE _LIST

-- Remove the trailing ','
print '@lColumnList: ' + convert(varchar(MAX), @lColumnList)
SELECT @lColumnList = substring(@lColumnList,1,datalength(@lColumnList)-1)

IF @lPrintIt = 1 PRINT @lColumnList

DECLARE @stm varchar(MAX)

SET @stm = 'INSERT INTO ' + @iSourceTable + ' (AssetId, ' + @lColumnList + ') SELECT ' + Convert(varchar(10), @NewAssetID) + ', ' + @lColumnList + ' from ' + @iSourceTable +
' where assetid = ' + Convert(varchar(10), @CurrentAssetID)

PRINT @stm

IF(@executeIt = 1)
BEGIN
EXEC(@STM)
END

SELECT @CurrentsubleaseID = @@identity

END