alter PROCEDURE [dbo].[procUtils_GenerateMetaInsert]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start
SET NOCOUNT ON;
BEGIN TRY --begin try
--CODE SNIPPET TO LIST TABLE COLUMNS
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'
--Declare the Table variable
DECLARE @ColNames TABLE
(
Number INT IDENTITY(1,1), --Auto incrementing Identity column
ColName VARCHAR(300) , --The string value
DataType varchar(50) --the datatype
)
--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT
--Populate the TABLE variable using some logic
INSERT INTO @ColNames SELECT column_name , Data_type from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=@TableName
--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1
--Determine the number of rows in the Table
SELECT @Count=max(Number) from @ColNames
--A variable to hold the currently selected value from the table
DECLARE @ColumnName varchar(300);
DECLARE @DataType varchar(50)
print '
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
SET IDENTITY_INSERT [dbo].[' + @TableName + '] ON;
SELECT TOP 5 * FROM ' + @TableName + ' order by 1 desc
GO
'
print 'INSERT INTO ' + @TableName + '( '
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print '[' + @ColumnName + '] --type of ' + @DataType -- this is the last row no comma!
else
print '[' + @ColumnName + '], --type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
print ') VALUES ( '
--AND START ALL OVER AGAIN
SET @CurrentDelimiterPositionVar = 1
--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count
BEGIN
--Load current value from the Table
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar
--Process the current value
if @CurrentDelimiterPositionVar = @Count
print ''''' --[' + @ColumnName + '] type of ' + @DataType -- this is the last row no comma!
else
print ''''' , --[' + @ColumnName + '] type of ' + @DataType -- print it without the ,
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName
-- print @ColumnName --SIMPLE PRINT
--Increment loop counter
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END
set nocount off
PRINT ')'
PRINT '-- NOW VIEW THE RESULTS '
PRINT 'Select top 5 * from '+ @TableName +' order by 1 desc '
END TRY --end try
BEGIN CATCH
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))
END CATCH
END --procedure end
/*
USE [GenApp]
GO
SELECT NAME FROM SYS.tables where name like '%Msg%'
EXEC [dbo].[procUtils_GenerateMetaInsert] @TableName = N'msg'
GO
*/
Posts
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!