Generate Script for Table Data 찾았습니다.

본 sp는 테이블의 데이터를 insert할 수 있는 insert문을 자동으로 만들어 주는 매우 유용한 sp입니다.

 

쩝... 생각보다 쉽게 찾았네요 --;;

 

첨부 파일 중 sp_generate_inserts.sql을 실행하시면 sp가 하나 만들어집니다.

sp_generate_inserts.mht는 해당 홈페이지를 긁어 놓은 파일입니다. 혹시 홈페이지가 없어지면 대략 낭패이니까요.

 

sp 이름이 'sp_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

 

출처 : http://vyaskn.tripod.com/code.htm#inserts