Procedure to script
your data (to generate INSERT statements from the existing data)
This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.
Click here to download the procedure! (SQL Server 2000 version)
Click here to download the procedure! (SQL Server 2005 / Yukon version)
Advantages:
This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.
Click here to download the procedure! (SQL Server 2000 version)
Click here to download the procedure! (SQL Server 2005 / Yukon version)
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
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 |
To generate INSERT statements for all the tables in your database, execute the following query in that database, which will output the commands, that you need to execute for the same:
SELECT 'EXEC sp_generate_inserts ' +
'[' + name + ']' +
',@owner = ' +
'[' + RTRIM(USER_NAME(uid)) + '],' +
'@ommit_images = 1, @disable_constraints = 1'
FROM sysobjects
WHERE type = 'U' AND
OBJECTPROPERTY(id,'ismsshipped') = 0
Reference:
http://medusa-afilar.googlecode.com
No comments:
Post a Comment