엑셀 화일을 SQL서버로 익스포트하는 스크립트

출처 : http://www.sqlservercentral.com/

내용 : 엑셀 화일을 SQL서버로 익스포트하는 스크립트

제약조건들이 꽤 되지만... 그럭저럭 쓸만한 스크립트이며 정확히 동작합니다.

OpenRowSet형태를 이용하는 방식이 내부적인 방식이니 참고 하시길 바랍니다.

그럼 이만.


--------------
Documentation
-------------
Assumptions

1.Excel sheet should be saved in one of the local drives of SQL Server Box.
   a. Excel sheet must have name Sheet1 as tab defined, it cannot be renamed.
2.SQL table to which data needs to be exported must already exist in the database
3.SQL Table should have Primary Key defined.
4.The first row of excel sheet should be same as column names of SQL table.

The order of the columns in Excel needs to be same as in SQL table, and the names should match exactly.  
Column names do not support spaces, for example 밬ser name?is invalid.

1.If a column in excel has both integer and character type data, please ensure integer type data is saved as
character type data in that column.

1.Uploading data from Excel sheet to SQL will be considered as Incremental instead of
밆estructiverebuild.
- If the data already exists in SQL, the data gets over-written from Excel table.
- New data in Excel will be inserted into SQL table.
- Data from SQL table will not be deleted; it needs to be done manually.
- Comparison between SQL table and Excel table is based of Primary Key.

2.SQL Server table with columns of following data type is not supported.
- ntext
- sql_variant
- text
- binary
- image
- varbinary

Steps to export data

1. Create couple of tables
2. Create a PK on these tables
3. Save Excel sheet in local drives of SQL Server Box
4. Please ensure Excel file is closed.
5. Using Query Analyzer execute the stored procedure.

[출처] 엑셀 화일을 SQL서버로 익스포트하는 스크립트|작성자 진카자마