Chapter 12: MS SQL SERVER 2005 SQL Procedure to script you data
MS SQL SERVER 2005
How to generate INSERT statements from the existing data
Procedure to script your data EXAMPLE
This is an advanced level guide. Utilising this guide for your ICA is considered a merit advance feature.
The SQL Code has been provided by: http://vyaskn.tripod.com/code.htm
This guide shows how to create a stored procedure which generates INSERT query script files for existing data in a table. Once the procedure has been created you are then able to execute whenever you need to generate INSERT script files for your data set. It's very useful when you have to:
· Generate different data simulations for testing and documentation of a SQL Server database application. Ideal for troubleshooting purposes.
· Helps in the development process of the SQL Server database i.e. build, test, delete, modify and rebuild
· Most importantly for ICA purposes you provide evidence of an advance feature. This can get a merit/dist component towards the overall criteria of SQL CODE.
Advantages:
· Data from both tables and views can be scripted
· No CURSORs are used
· Table names and column names with spaces are handled
· All datatypes are handled except images, large text and binary columns with more than 4 bytes
· NULLs are gracefully handled
· Timestamp columns are handled
· Identity columns are handled
· Very flexible and configurable
· Non-dbo owned tables are handled
· Computed columns are handled
· You can filter the rows for which you want to generate INSERTs
Loading the SQL Procedure
Exercise 1 – Load and Review the SQL code to create the procedure
Click on the link below to open the SQL script file 15.Procedure_sp_generate_inserts in Management Studio.
Review the SQL Code and note the comments made throughout to aid your understanding of the SQL code and what it does. You are not expected to understand the SQL code in detail – it is at an advance level. However once you complete the guide you will appreciate what the SQL code does. Review this code again once you have completed the guide.
Click here to download the sp_generate_inserts procedure! (SQL Server 2005 / Yukon version) *** if the link does not work the sql code is provided on page 7
Note the query window is set to the default MASTER area (expand System Database tag as in screen above) making this procedure accessible to all your databases. Yes you can build up a library of procedures to execute.
Executing the SQL Procedure
Exercise 2 –Executing the procedure in Management Studio.
Click on the button to run the sql script file in the New Query window .
Note the messages dialogue box shows the PRINT statements executed within the SQL code. Good programming practice to indicate flow through the code. Below the message dialogue box you should also note the following:
Congratulations!! You have successfully implemented your first stored procedure. You will not need to run the above SQL code again. We can now execute the stored procedure as and when required to generate INSERT script files.
Using the SQL Procedure
Exercise 3 – Using the SQL Procedure to generate insert script files.
Expand the Object Explorer and click on the table you wish to use to generate the insert script commands. This table should already have data entered via management studio (similar to MS Access data entry).
In the following example the insert script commands are generated for the database table Students from the case study STUDENT1. This case study is provided online. Ensure the table you choose has data in it. Right click the table and from the menu choose open with to view the data.
Highlighted the table you need to generate the INSERT statements for.
Click on New Query window . Make sure the New Query Window tag is for the correct database eg STUDENT1
In the New Query window type in the following SQL command which executes the stored procedure sp_generate_inserts for the table called student
EXEC sp_generate_inserts 'Student'
or click on the SQL Script File 15.sql.student1.exec.sp_generate_insert.sql
Click on the button to run the query and note the INSERT scripts have been generated for the data already in the table.
If you right click the results pane you are able to either Select All and copy them into a notepad file or Save Results as and filter the insert scripts to a file. The insert script files can then be used at a later date to recreate the database form scratch.
The following file contains the generated code 15.sql.student1.student.insert.sql
Parameter options for the SQL Procedure
The following table indicates the acceptable parameters variations the procedure sp_generate_inserts can handle. Note you should replace ‘title’ with the relevant table name eg ‘Student’ etc.
Usage:
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)
NOTE: 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:
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:
NOTE: 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:
NOTE: 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:
NOTE: 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 for the rest of the columns excluding IDENTITY column:
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 only with the columns you want:
EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12: / To generate INSERT statements by ommitting some columns:
EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13: / To avoid checking the foreign key constraints while loading data with INSERT statements:
NOTE: The @disable_constraints option will disable foreign key constraints, by assuming that the source data is valid and referentially sound
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14: / To avoid scripting data from computed columns:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
NOTE: Please see the code and read the comments to understand more about how this procedure works!
To remove the stored procedure sp_generate_inserts click and execute the following SQL Script code 15.Remove.Procedure_sp_generate_inserts.sql
If the download does not work please copy and paste the following code sp_generate_inserts procedure! (SQL Server 2005 / Yukon version)
/* Procedure to automatically generate SQL CODE - INSERT SCRIPTS for database tables for manually entered data using the
management studio data entry interface similar to that of MS Access
Part 1 of 2 sets of code which need to be executed
Part 1 - This sql script file generates the sql code needed to run in seperate New Query window. It builds the command in the format
EXEC sp_generate_inserts [tablename] eg. EXEC sp_generate_inserts 'customers'
Part 2 - Seperate sql script file
INSERT database.table1.[fields1, fields2, ...] ('value1', 'value2', ....)
" " "
" " "
INSERT database.table2.[fields1, fields2, ...] ('value1', 'value2', ....)
" " "
" " "
INSERT database.tableN.[fields1, fields2, ...] ('value1', 'value2', ....)
*/
SET NOCOUNT ON
GO
PRINT 'Using Master database'
USE master /* Using master allows this procedure to be called from within any of your databases
In UoT labs this command needs to be replaced with USE database where database
is your student number ie USE U0000706 */
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/view 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
@disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement
)
AS
BEGIN
/***********************************************************************************************************
Procedure: sp_generate_inserts (Build 22)
(Copyright © 2002 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
Acknowledgements:
Divya Kalra -- For beta testing
Mark Charsley -- For reporting a problem with scripting uniqueidentifier columns with NULL values
Artur Zeygman -- For helping me simplify a bit of code for handling non-dbo owned tables
Joris Laperre -- For reporting a regression bug in handling text/ntext columns
Tested on: SQL Server 7.0 and SQL Server 2000 and SQL Server 2005
Date created: January 17th 2001 21:52 GMT
Date modified: May 1st 2002 19:50 GMT
Email:
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
Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
like nchar and nvarchar
ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON
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'"
Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14: To exclude computed columns from the INSERT statement:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/