Single-table import/Export text file

/*--Implementation of data import/export stored procedures





can implement import/export specify table to text file


supports custom row/column delimiters





--Jiangjian 2003.07--*/





/*--Call Example


Export Call Example


--Export the specified table, where you specify the export table: regional data


exec file2table ' zj ', ', ', ' c:\zj.txt ', ' xzkh_new. Area Information ', @rowsplit = ', '





Import Call Example


--Import the specified table, here Specify the import table: regional data


exec file2table ' zj ', ', ', ' c:\zj.txt ', ' Xzkh_sa. Regional information ', 0


--*/





if exists (select 1 from sysobjects where name= ' file2table ' and objectproperty (ID, ' isprocedure ') =1)


drop procedure File2table


Go





CREATE PROCEDURE file2table


@servername varchar (200)--Server name


, @username varchar (200)--user name, NULL if NT authentication method


, @password varchar (200)--Password


, @filename varchar (1000)--directory name + filename


, @tbname varchar (500) = '-database. Table name


, @isout bit=1--1 for export (default), 0 for import


, @fdsplit varchar = ' \ t '--field separator, Default to tab


, @rowsplit varchar = ' \ n '--record delimiter, default to carriage return


as


DECLARE @sql varchar (8000)





Set @sql = ' bcp ' + @tbname


+case when @isout the =1 then ' out ' else ', ' "in '"


+ ' "' + @filename + '"/C ' + '/S "' + @servername


+case when IsNull (@username, ') = ' Then '


Else '/u ' + @username end


+ ' "/P" ' +isnull (@password, ') + ' "'


+ '/t ' + @fdsplit + ' "'


+ '/R ' + @rowsplit + ' "'





exec master. xp_cmdshell @sql


Go