|
|
|
|
|
|
|
|
Dicas
|
|
|
Visual Basic (Banco de Dados)
|
|
 |
Título da Dica: Stored Procedure sp_generate_inserts
|
 |
|
|
|
Postada em 27/6/2007 por Vilmar Brazão
SET NOCOUNT ON
GO
PRINT 'Using Master database'
USE master
GO
PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists
BEGIN
PRINT 'Procedure already exists. So, dropping it'
DROP PROC sp_generate_inserts
END
GO
CREATE PROC sp_generate_inserts
(
@table_name varchar(776), -- The table for which the INSERT statements will be generated using the existing data
@target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted
@include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement
@from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
@debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
@ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns
@ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
@top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement
@cols_to_exclude varchar(8000) = NULL -- List of columns to be excluded from the INSERT statement
)
AS
BEGIN
/***********************************************************************************************************
Procedure: sp_generate_inserts
(Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.)
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some other location.
This procedure is also useful to create a database setup, where in you can
script your data along with your table definitions.
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0 and SQL Server 2000
Date modified: April 8th 2001 5:30 PM
Email: vyaskn@hotmail.com
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL Server 2000's
sql_variant data types
IMPORTANT: Whenever possible, Use @include_column_list parameter to ommit
column list in the INSERT statement, for better results
Example 1: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
Example 2: To ommit the column list in the INSERT statement: (Column list
is included by default)
IMPORTANT: If you have too many columns, you are advised to ommit column
list, as shown below, to avoid erroneous results
EXEC sp_generate_inserts 'titles', @include_column_list = 0
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4: To generate INSERT statements for 'titles' table for only those titles
which contain the word 'Computer' in them:
NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that
you are good with T-SQL if you are using this parameter
EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"
Example 5: To specify that you want to include TIMESTAMP column's data as
well in the INSERT statement:
(By default TIMESTAMP column's data is not scripted)
EXEC sp_generate_inserts 'titles', @include_timestamp = 1
Example 6: To print the debug information:
EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7: If you are not the owner of the table, use @owner parameter to
specify the owner name
To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8: To generate INSERT statements for the rest of the columns
excluding images
When using this otion, DO NOT set @include_column_list parameter to 0.
EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9: To generate INSERT statements excluding (ommiting) IDENTITY
columns:
(By default IDENTITY columns are included in the INSERT statement)
EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10: To generate INSERT statements for the TOP 10 rows in the table:
EXEC sp_generate_inserts mytable, @top = 10
Example 11: To generate INSERT statements with only those columns you want:
EXEC sp_generate_inserts titles, @cols_to_include =
"'title','title_id','au_id'"
Example 12: To generate INSERT statements by omitting certain columns:
EXEC sp_generate_inserts titles, @cols_to_exclude =
"'title','title_id','au_id'"
***********************************************************************************************************/
SET NOCOUNT ON
--Making sure user only uses either @cols_to_include or @cols_to_exclude
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 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
END
--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
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 --Failure. Reason: Invalid use of @cols_to_include property
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 --Failure. Reason: Invalid use of @cols_to_exclude property
END
--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is now allowed
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 --Failure. Reason: Database name is specified along with the table name, which is not allowed
END
--Checking for the existence of 'user table'
--This procedure is not written to work on system tables
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 --Failure. Reason: There is no user table with this name
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), --This is the string that will be finally
executed to generate INSERT statements
@IDN varchar(128) --Will contain the IDENTITY column's name in the table
--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 Min 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
--Determining the data type of the column and depending on the data type, the VALUES part of
--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
--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 Max 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 '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'
PRINT '--Problems/Suggestions? Contact Vyas @ answer_me@hotmail.com'
PRINT '--http://vyaskn.tripod.com'
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
--Run the following commands ONLY if you want this procedure to
--work from Master database, just like any other system procedure. But
--the following commands modify a system table. If you don't want that,
--then don't run these commands (in this case, you will have to Min
--create this procedure in the database, before using it on that database's
--tables. So you will end up creating this same procedure on all the databases
--for which you want to generate insert scripts
sp_configure 'allow', 1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysobjects SET status=-2147483647 WHERE name = 'sp_generate_inserts'
GO
sp_configure 'allow', 0
GO
RECONFIGURE WITH OVERRIDE
GO
SET NOCOUNT OFF
GO
PRINT 'Done'
|
|
|
|
|
