본 sp는 테이블의 데이터를 insert할 수 있는 insert문을 자동으로 만들어 주는 매우 유용한 sp
사용법은 아래와 같습니다.
| 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
'SQL' 카테고리의 다른 글
| [펜티엄 프로젝트] ① 32비트 닷넷과 최적화 알고리즘 (0) | 2007/06/07 |
|---|---|
| 0001 자리에 맞게 0 출력하기 (0) | 2007/06/07 |
| Generate Script for Table Data (데이터 스크립트로 .. (0) | 2007/06/07 |
| 디비 커넥션 Kill (0) | 2007/06/07 |
| DB사이즈 증가량 모니터링하기.. (0) | 2007/06/07 |
| 유콘(Yukon)미리보기 : Service Broker 1부 (0) | 2007/06/07 |
SQL
0 Trackback,
0 Comment,
:
최근에 달린 댓글
링크
최근에 받은 트랙백
태그목록