want to create store procedure that. want to send the table name as argument and retrive the data of that argument. and want to store data in temperary table using the insert query.
HERE I AM ATTACHING A SAMPLE PROCEDURE IT WAS NOT WORKING PERFECTLY. HELP ME
--exec SP_Generate_Ins ert_Statements tbl_Attachment, 1
--select * from tbl_Attachmentt esting
--exec SP_Generate_Ins ert_Statements tbl_Attachmentt esting,1
ALTER PROCEDURE [dbo].[SP_Generate_Ins ert_Statements_ Testing]
@strTableName varchar(128), -- used to specify the table to generate data for
@RequiredGo bit = 0 -- used to allow GO statements to separate the insert statements
AS
--Variable declarations
DECLARE @InsertStmt varchar(max)
DECLARE @Fields varchar(8000)
DECLARE @SelList varchar(max)
DECLARE @ColName varchar(128)
DECLARE @IsChar tinyint
DECLARE @tableData varchar(8000)
DECLARE @strImageSQL varchar(200)
DECLARE @strTextSQL varchar(200)
DECLARE @ImageData varbinary(8000)
DECLARE @TextData varchar(max)
DECLARE @bitIdentity BIT
SET NOCOUNT OFF
SELECT @bitIdentity = OBJECTPROPERTY( OBJECT_ID(TABLE _NAME), 'TableHasIdenti ty')
FROM INFORMATION_SCH EMA.TABLES
WHERE TABLE_Name =@strTableName
PRINT '---- ** Start of Inserts ** ----'
PRINT ''
IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName +'] ON '
END
--initialize variables
SELECT @InsertStmt = 'INSERT INTO [' + @strTableName + '] (',
@Fields = '',
@SelList = 'SELECT '
--create a cursor that loops through the fields in the table
--and retrieves the column names and determines the delimiter type that the field needs
DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME,
'IsChar' = CASE
WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timest amp') THEN 0
WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uni queidentifier', 'nchar') THEN 1
WHEN DATA_TYPE in ('datetime', 'smalldatetime' ) THEN 2
WHEN DATA_TYPE in ('text', 'ntext') THEN 3
WHEN DATA_TYPE in ('sql_variant') THEN 4
WHEN DATA_TYPE in ('image') THEN 5
ELSE 6
END
FROM INFORMATION_SCH EMA.COLUMNS c WITH (NOLOCK)
INNER JOIN syscolumns sc WITH (NOLOCK)
ON c.COLUMN_NAME = sc.name
INNER JOIN sysobjects so WITH (NOLOCK)
ON sc.id = so.id
AND so.name = c.TABLE_NAME
WHERE table_name = @strTableName
AND DATA_TYPE <> 'timestamp'
AND sc.IsComputed = 0
ORDER BY ORDINAL_POSITIO N
FOR READ ONLY
OPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1)
BEGIN
IF @@fetch_status <> -1
BEGIN
IF @SelList = 'SELECT'
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', '
SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 2 THEN @SelList + 'ISNULL(''''''' ' + CONVERT(VARCHAR (20),['+ @ColName + '])+ '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR (MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
WHEN 5 THEN @SelList + '''MyImageData' ''''
ELSE @SelList + ' ISNULL(CONVERT( VARCHAR(2000),['+ @ColName + '],0),''NULL'')' + ' COLLATE database_defaul t + ' END
END
ELSE
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', '
SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 2 THEN @SelList + ''',''' + ' + ' + 'ISNULL(''''''' ' + CONVERT(VARCHAR (20),['+ @ColName + '])+ '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR (MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
WHEN 5 THEN @SelList + ''',''' + '''MyImageData' ''''
ELSE @SelList + ''',''' + ' + ' + ' ISNULL(CONVERT( VARCHAR(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_defaul t + ' END
END
IF @IsChar = 5
SET @strImageSQL = 'SELECT ' + @ColName + ' FROM ' + @strTableName
END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
CLOSE CR_Table
DEALLOCATE CR_Table
SELECT @Fields = SUBSTRING(@Fiel ds, 1,(len(@Fields)-1))
SELECT @SelList = SUBSTRING(@SelL ist, 1,(len(@SelList )-1))
SELECT @SelList = @SelList + ' FROM ' + @strTableName
SELECT @InsertStmt = @InsertStmt + @Fields + ')'
SET NOCOUNT ON
--now we need to create and load the temp table that will hold the data
--that we are going to generate into an insert statement
CREATE TABLE #TheData (TableData varchar(MAX))
INSERT INTO #TheData (TableData) EXEC (@SelList)
IF @strImageSQL <> ''
BEGIN
CREATE TABLE #ImageData (TableData image)
INSERT INTO #ImageData (TableData) EXEC (@strImageSQL)
END
--Cursor through the data to generate the INSERT statement / VALUES
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @tableData
IF @strImageSQL <> ''
BEGIN
DECLARE CR_ImageData CURSOR FAST_FORWARD FOR SELECT TableData FROM #ImageData FOR
READ ONLY
OPEN CR_ImageData
FETCH NEXT FROM CR_ImageData INTO @ImageData
END
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @strImageSQL <> ''
BEGIN
PRINT @InsertStmt
PRINT 'VALUES '
PRINT '('
PRINT SUBSTRING(@tabl eData,1,CHARIND EX('MyImageData ',@tableData)-2)
PRINT @ImageData
PRINT SUBSTRING(@tabl eData,CHARINDEX ('MyImageData', @tableData) + 12,LEN(@tableDa ta))
PRINT ')' + CHAR(13)
END
ELSE
PRINT @InsertStmt + ' VALUES (' + @tableData + ')' + CHAR(13)
IF @RequiredGo = 1
PRINT 'GO'
END
FETCH NEXT FROM CR_Data INTO @tableData
IF @ImageData <> ''
FETCH NEXT FROM CR_ImageData INTO @ImageData
-- IF @TextData <> ''
-- FETCH NEXT FROM CR_TextData INTO @TextData
END
CLOSE CR_Data
DEALLOCATE CR_Data
IF @ImageData <> ''
BEGIN
CLOSE CR_ImageData
DEALLOCATE CR_ImageData
END
IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName + '] OFF '
END
PRINT '---- ** End of Inserts ** ----'
RETURN (0)
HERE I AM ATTACHING A SAMPLE PROCEDURE IT WAS NOT WORKING PERFECTLY. HELP ME
--exec SP_Generate_Ins ert_Statements tbl_Attachment, 1
--select * from tbl_Attachmentt esting
--exec SP_Generate_Ins ert_Statements tbl_Attachmentt esting,1
ALTER PROCEDURE [dbo].[SP_Generate_Ins ert_Statements_ Testing]
@strTableName varchar(128), -- used to specify the table to generate data for
@RequiredGo bit = 0 -- used to allow GO statements to separate the insert statements
AS
--Variable declarations
DECLARE @InsertStmt varchar(max)
DECLARE @Fields varchar(8000)
DECLARE @SelList varchar(max)
DECLARE @ColName varchar(128)
DECLARE @IsChar tinyint
DECLARE @tableData varchar(8000)
DECLARE @strImageSQL varchar(200)
DECLARE @strTextSQL varchar(200)
DECLARE @ImageData varbinary(8000)
DECLARE @TextData varchar(max)
DECLARE @bitIdentity BIT
SET NOCOUNT OFF
SELECT @bitIdentity = OBJECTPROPERTY( OBJECT_ID(TABLE _NAME), 'TableHasIdenti ty')
FROM INFORMATION_SCH EMA.TABLES
WHERE TABLE_Name =@strTableName
PRINT '---- ** Start of Inserts ** ----'
PRINT ''
IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName +'] ON '
END
--initialize variables
SELECT @InsertStmt = 'INSERT INTO [' + @strTableName + '] (',
@Fields = '',
@SelList = 'SELECT '
--create a cursor that loops through the fields in the table
--and retrieves the column names and determines the delimiter type that the field needs
DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME,
'IsChar' = CASE
WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint' ,'numeric', 'bit', 'bigint', 'smallmoney', 'float','timest amp') THEN 0
WHEN DATA_TYPE in ('char', 'varchar', 'nvarchar','uni queidentifier', 'nchar') THEN 1
WHEN DATA_TYPE in ('datetime', 'smalldatetime' ) THEN 2
WHEN DATA_TYPE in ('text', 'ntext') THEN 3
WHEN DATA_TYPE in ('sql_variant') THEN 4
WHEN DATA_TYPE in ('image') THEN 5
ELSE 6
END
FROM INFORMATION_SCH EMA.COLUMNS c WITH (NOLOCK)
INNER JOIN syscolumns sc WITH (NOLOCK)
ON c.COLUMN_NAME = sc.name
INNER JOIN sysobjects so WITH (NOLOCK)
ON sc.id = so.id
AND so.name = c.TABLE_NAME
WHERE table_name = @strTableName
AND DATA_TYPE <> 'timestamp'
AND sc.IsComputed = 0
ORDER BY ORDINAL_POSITIO N
FOR READ ONLY
OPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1)
BEGIN
IF @@fetch_status <> -1
BEGIN
IF @SelList = 'SELECT'
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', '
SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 2 THEN @SelList + 'ISNULL(''''''' ' + CONVERT(VARCHAR (20),['+ @ColName + '])+ '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR (MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
WHEN 5 THEN @SelList + '''MyImageData' ''''
ELSE @SelList + ' ISNULL(CONVERT( VARCHAR(2000),['+ @ColName + '],0),''NULL'')' + ' COLLATE database_defaul t + ' END
END
ELSE
BEGIN
SELECT @Fields = @Fields + '[' + @ColName + ']' + ', '
SELECT @SelList = CASE @IsChar
WHEN 1 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(['+ @ColName + '],'''''''', '''''''''''' ) + '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 2 THEN @SelList + ''',''' + ' + ' + 'ISNULL(''''''' ' + CONVERT(VARCHAR (20),['+ @ColName + '])+ '''''''',''NULL '') ' + ' COLLATE database_defaul t + '
WHEN 3 THEN @SelList + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(MAX),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
--WHEN 3 THEN @SelList + ''' CONVERT(VARCHAR (MAX),['+ @ColName + ']) '''
WHEN 4 THEN @SelList + ''',''' + ' + ' + ' ISNULL('''''''' + REPLACE(CONVERT (VARCHAR(8000),['+ @ColName + ']),'''''''', '''''''''''')+ '''''''' ,''NULL'') '+ ' COLLATE database_defaul t + '
WHEN 5 THEN @SelList + ''',''' + '''MyImageData' ''''
ELSE @SelList + ''',''' + ' + ' + ' ISNULL(CONVERT( VARCHAR(2000),['+@ColName + '],0),''NULL'')' + ' COLLATE database_defaul t + ' END
END
IF @IsChar = 5
SET @strImageSQL = 'SELECT ' + @ColName + ' FROM ' + @strTableName
END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
CLOSE CR_Table
DEALLOCATE CR_Table
SELECT @Fields = SUBSTRING(@Fiel ds, 1,(len(@Fields)-1))
SELECT @SelList = SUBSTRING(@SelL ist, 1,(len(@SelList )-1))
SELECT @SelList = @SelList + ' FROM ' + @strTableName
SELECT @InsertStmt = @InsertStmt + @Fields + ')'
SET NOCOUNT ON
--now we need to create and load the temp table that will hold the data
--that we are going to generate into an insert statement
CREATE TABLE #TheData (TableData varchar(MAX))
INSERT INTO #TheData (TableData) EXEC (@SelList)
IF @strImageSQL <> ''
BEGIN
CREATE TABLE #ImageData (TableData image)
INSERT INTO #ImageData (TableData) EXEC (@strImageSQL)
END
--Cursor through the data to generate the INSERT statement / VALUES
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @tableData
IF @strImageSQL <> ''
BEGIN
DECLARE CR_ImageData CURSOR FAST_FORWARD FOR SELECT TableData FROM #ImageData FOR
READ ONLY
OPEN CR_ImageData
FETCH NEXT FROM CR_ImageData INTO @ImageData
END
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @strImageSQL <> ''
BEGIN
PRINT @InsertStmt
PRINT 'VALUES '
PRINT '('
PRINT SUBSTRING(@tabl eData,1,CHARIND EX('MyImageData ',@tableData)-2)
PRINT @ImageData
PRINT SUBSTRING(@tabl eData,CHARINDEX ('MyImageData', @tableData) + 12,LEN(@tableDa ta))
PRINT ')' + CHAR(13)
END
ELSE
PRINT @InsertStmt + ' VALUES (' + @tableData + ')' + CHAR(13)
IF @RequiredGo = 1
PRINT 'GO'
END
FETCH NEXT FROM CR_Data INTO @tableData
IF @ImageData <> ''
FETCH NEXT FROM CR_ImageData INTO @ImageData
-- IF @TextData <> ''
-- FETCH NEXT FROM CR_TextData INTO @TextData
END
CLOSE CR_Data
DEALLOCATE CR_Data
IF @ImageData <> ''
BEGIN
CLOSE CR_ImageData
DEALLOCATE CR_ImageData
END
IF @bitIdentity = 1
BEGIN
PRINT 'SET IDENTITY_INSERT [' + @strTableName + '] OFF '
END
PRINT '---- ** End of Inserts ** ----'
RETURN (0)