/ * - Implement data import / export stored procedure
You can implement import / export specifying tables to text files Support custom row / column separator
- Zou Jian 2003.07 (please keep this information) - * /
/ * - Call the sample Export Call Example - Export Specify Table, specify the export table: Area, ',' ',' c: / zj.txt ',' xzkh_new .. regional information ' @ rowsplit = ','
Import Call Example - Import Specify Table, specify the import table: Area, ',' ',' c: / zj.txt ',' xzkh_sa .. area information ', 0- * /
If EXISTS (SELECT 1 from sysobjects where name = 'file2table' and objectproperty (id, 'isprocedure') = 1) Drop Procedure File2TableGO
Create Procedure File2Table @ ServerName VARCHAR (200) - Server Name, @ username varchar (200) - Username, if you use NT authentication mode, it is empty '', @ Password Varchar (200) - Password, @ filename varchar (1000) - Directory Name File Name, @ TBName Varchar (500) = '' - Database .. Name, @ isout bit = 1 --1 is export (default), 0 is import, @ fdsplit varchar ( 10) = '/ t' - field separator, default is a tab, @ rowsplit varchar (10) = '/ n' - Record the separator, default as the carriage return asdeclare @SQL varchar (8000)
Set @ SQL = 'BCP "' @ TBNAME CASE WHEN @ isout = 1 TEN '" OUT' ELSE '"IN' END '"' @ filename '"/ c' '/ s"' @ Servername Case When Isnull (@USERNAME, ') =' 'TEN' 'ELSE' "/ u" ' @ username end ' "/ p" ' isnull (@password,' ') ' "' ' / t "' @ fdsplit '" ' ' / r "' @ rowsplit '" "
Exec master..xp_cmdshell @SQLGO
