Tuesday, March 08, 2011

Generate SQL Server Insert Script by Stored Procedure

Generate SQL Server Insert Script by Stored Procedure

--EXEC sp_generate_inserts TABELNAME
--=============================================================

SET NOCOUNT ON

PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL
BEGIN
PRINT 'Procedure already exists. So, dropping it'
DROP PROC sp_generate_inserts
END
GO

CREATE PROC sp_generate_inserts
(
      @table_name varchar(776),
      @target_table varchar(776) = NULL,
      @include_column_list bit = 1,
      @from varchar(800) = NULL,
      @include_timestamp bit = 0,
      @debug_mode bit = 0,
      @owner varchar(64) = NULL,
      @ommit_images bit = 0,
      @ommit_identity bit = 0,
      @top int = NULL,
      @cols_to_include varchar(8000) = NULL,
      @cols_to_exclude varchar(8000) = NULL
)
AS
BEGIN

SET NOCOUNT ON

IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
BEGIN
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not specify
 both',16,1)
RETURN -1
END

IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include)
 =0))
BEGIN
RAISERROR('Invalid use of @cols_to_include property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by
 commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',
''title''"'
RETURN -1
END
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude)
 =0))
BEGIN
RAISERROR('Invalid use of @cols_to_exclude property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by
 commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id''
,''title''"'
RETURN -1
END

IF (parsename(@table_name,3)) IS NOT NULL
BEGIN
RAISERROR('Do not specify the database name. Be in the required database
 and just specify the table name.',16,1)
RETURN -1
END

IF @owner IS NULL
BEGIN
IF (OBJECT_ID(@table_name,'U') IS NULL)
BEGIN
RAISERROR('User table not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table. In that
 case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table.'
RETURN -1
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = @table_name AND TABLE_TYPE = 'BASE TABLE' AND
TABLE_SCHEMA = @owner)
BEGIN
RAISERROR('User table not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table.
 In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table.'
RETURN -1 --Failure. Reason: There is no user table with this name
END
END
--Variable declarations
DECLARE @Column_ID int,
@Column_List varchar(8000),
@Column_Name varchar(128),
@Start_Insert varchar(786),
@Data_Type varchar(128),
@Actual_Values varchar(8000),
@IDN varchar(128)
--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = 0
SET @Column_List = ''
SET @Actual_Values = ''
IF @owner IS NULL
BEGIN
SET @Start_Insert = 'INSERT INTO ' + '[' +RTRIM(COALESCE(@target_table,
@table_name)) + ']'
END
ELSE
BEGIN
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' 
+ '[' +RTRIM(COALESCE(@target_table,@table_name)) + ']'
END

--To get the first column's ID
IF @owner IS NULL
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name
END
ELSE
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
TABLE_SCHEMA = @owner
END

--Loop through all the columns of the table, to get the column names 
--and their data types
WHILE @Column_ID IS NOT NULL
BEGIN
IF @owner IS NULL
BEGIN
SELECT @Column_Name = '[' + COLUMN_NAME + ']',
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name
END
ELSE
BEGIN
SELECT @Column_Name = '[' + COLUMN_NAME + ']',
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name AND
TABLE_SCHEMA = @owner
END
IF @cols_to_include IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) 
+'''',@cols_to_include) = 0
BEGIN
GOTO SKIP_LOOP
END
END
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) 
+'''',@cols_to_exclude) <> 0
BEGIN
GOTO SKIP_LOOP
END
END
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the 
---table has an IDENTITY column
IF (SELECT COLUMNPROPERTY(OBJECT_ID(@table_name),
SUBSTRING(@Column_Name,2,LEN(@Column_Name)- 2),'IsIdentity')) = 1
BEGIN
IF @ommit_identity = 0 
--Determing whether to include or exclude the IDENTITY column
SET @IDN = @Column_Name
ELSE
GOTO SKIP_LOOP
END

--Tables with columns of IMAGE data type are not supported 
--for obvious reasons
IF(@Data_Type in ('image'))
BEGIN
IF (@ommit_images = 0)
BEGIN
RAISERROR('Tables with image columns are not supported.',16,1)
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the
 rest of the columns.'
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit
 column list using @include_column_list=0, the generated INSERTs will fail.'
RETURN -1 --Failure. Reason: There is a column with image data type
END
ELSE
BEGIN
GOTO SKIP_LOOP
END
END

--making sure, not to lose any data from flot, real, money, smallmomey, 
--datetime columns
SET @Actual_Values = @Actual_Values +
CASE
WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
THEN
''''''''' + '+'COALESCE(REPLACE(RTRIM(' + @Column_Name +'),
'''''''',''''''''''''),''nvkon©'')' + ' + '''''''''
WHEN @Data_Type IN ('datetime','smalldatetime')
THEN
''''''''' + '+'COALESCE(RTRIM(CONVERT(char,' + @Column_Name +',109)),
''nvkon©'')' + ' + '''''''''
WHEN @Data_Type IN ('uniqueidentifier')
THEN
''''''''' + '+'COALESCE(REPLACE(CONVERT(char(255),RTRIM(' + 
@Column_Name+ ')),'''''''',''''''''''''),''NULL'')' + ' + '''''''''
WHEN @Data_Type IN ('text','ntext')
THEN
''''''''' + '+'COALESCE(REPLACE(CONVERT(char,' + @Column_Name +'),
'''''''',''''''''''''),''NULL'')' + ' + '''''''''
WHEN @Data_Type IN ('binary','varbinary')
THEN
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + 
@Column_Name +'))),''NULL'')'
WHEN @Data_Type IN ('timestamp','rowversion')
THEN
CASE
WHEN @include_timestamp = 0
THEN
'''DEFAULT'''
ELSE
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + 
@Column_Name +'))),''NULL'')'
END
WHEN @Data_Type IN ('float','real','money','smallmoney')
THEN
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + 
@Column_Name + ',2)' +')),''NULL'')'
ELSE
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +
 @Column_Name + ')' +')),''NULL'')'
END + '+' + ''',''' + ' + '

--Generating the column list for the INSERT statement
SET @Column_List = @Column_List + @Column_Name + ','
SKIP_LOOP: --The label used in GOTO
IF @owner IS NULL
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID
END
ELSE
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID AND
TABLE_SCHEMA = @owner
END
--Loop ends here!
END
--To get rid of the extra characters that got concatened during the last run
-- through the loop
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
--Forming the final string that will be executed, to output the 
--INSERT statements
IF (@include_column_list <> 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' +
LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +
' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values + 
',''''''nvkon©'''''',''NULL'')' + '+'')'''+ ' ' +COALESCE(@from,' FROM ' 
+ CASE WHEN @owner IS NULL THEN '' ELSE '['+ 
LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' 
+'(NOLOCK)')
END
ELSE IF (@include_column_list = 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' 
+LTRIM(STR(@top)) +
 ' ' END +
'''' + RTRIM(@Start_Insert) +
' '' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values + ',''''''nvkon©'''''',
''NULL'')' +'+'')''' + ' ' +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL 
THEN '' ELSE '['+ LTRIM(RTRIM(@owner)) + '].' END 
+ '[' + rtrim(@table_name) + ']' +'(NOLOCK)')
END
--Determining whether to ouput any debug information
IF @debug_mode =1
BEGIN
PRINT '/*****START OF DEBUG INFORMATION*****'
PRINT 'Beginning of the INSERT statement:'
PRINT @Start_Insert
PRINT ''
PRINT 'The column list:'
PRINT @Column_List
PRINT ''
PRINT 'The SELECT statement executed to generate the INSERTs'
PRINT @Actual_Values
PRINT ''
PRINT '*****END OF DEBUG INFORMATION*****/'
PRINT ''
END

PRINT ''
PRINT 'SET NOCOUNT ON'
--Determining whether to print IDENTITY_INSERT or not
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + '[' +RTRIM(COALESCE
(@target_table,@table_name)) + ']' + ' ON'
PRINT 'GO'
END
PRINT 'PRINT ''Inserting values into ' + '[' +RTRIM(COALESCE
(@target_table,@table_name)) + ']' + ''''
--All the hard work pays off here!!! You'll get your INSERT statements,
-- when the next line executes!
EXEC (@Actual_Values)
PRINT 'PRINT ''Done'''
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + '[' +RTRIM(COALESCE
(@target_table,@table_name)) + ']' + ' OFF'
PRINT 'GO'
END
PRINT 'SET NOCOUNT OFF'
SET NOCOUNT OFF
RETURN 0 --Success. We are done!
END
GO
PRINT 'Created the procedure'
GO
SET NOCOUNT OFF
GO
PRINT 'Done'
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)