USUÁRIO:      SENHA:        SALVAR LOGIN ?    Adicione o VBWEB na sua lista de favoritos   Fale conosco 

 

  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'
 


CyberWEB Network Ltda.    © Copyright 2000-2021   -   Todos os direitos reservados.
Powered by HostingZone - A melhor hospedagem para seu site
Topo da página