/ ******* Export to Excelexec master..xp_cmdshell 'bcp settledb.dbo.shanghu out c: /temp1.xls -c -q -s "gnetdata / gnetdata" -u "sa" -p ""
/ *********** Import Excelselect * from OpenDataSource ('microsoft.jet.oledb.4.0', 'data source = "c: /test.xls"; user ID = admin; password =; extended Properties = Excel 5.0 ') ... xactions
SELECT CAST (CAST (Subject No. As Numeric (10, 2)) AS NVARCHAR (255)) '' converted alias from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'Data Source = "C: / Test .xls "; user ID = admin; password =; extended Properties = Excel 5.0 ') ... xactions
/ ** Import text files EXEC MASTER..XP_CMDSHELL 'BCP "DBNAME..TABLENAME" IN C: /DT.TXT -C -SSERVERNAME -USA-PPASSWORD'
/ ** Export text file EXEC MASTER..XP_CMDSHELL 'BCP "DBNAME..TABLENAME" OUT C: /DT.TXT -C -SSERVERNAME -USA-PPASSWORD' or EXEC MASTER HAINP_CMDSHELL 'BCP "SELECT * from DBNAME .. Tablename "Queryout C: /Dt.txt -c -sservername -usa -ppassword"
Export to TXT text, separate the exec master..xp_cmdshell 'bcp "with comma" library name .. Table name "D: /tt.txt" -c -t, -u sa -p password'
Bulk INSERT library name .. Name from 'c: /test.txt'with (Fieldterminator ='; ', rowterminator =' / n ')
- / * DBASE IV file Select * from openrowset ('microsoft.jet.oledb.4.0', 'dbase iv; hdr = no; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Data 4. DBF] ') - * /
- / * DBASE III file Select * from OpenRowSet ('microsoft.jet.oledb.4.0', 'DBASE III; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Information 3. DBF] ') - * /
- / * FoxPro Database Select * from OpenRowSet ('msdasql', 'Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDb = C: /', 'SELECT * from [aa.dbf]') - * // ************* Import DBF file **************** / SELECT * from OpenRowSet ('msdasql', 'Driver = Microsoft Visual FoxPro Driver ; SourceDB = E: / VFP98 / DATA; SourceType = DBF ',' Select * from customer where country! = "USA" Order by country ') go / *************** * Export to DBF ************** / If you want to export data to a structure (ie, existing) FoxPro table, you can directly use the following SQL statement
INSERT INTO OpenRowSet ('msdasql', 'driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDb = C: /', 'Select * from [aa.dbf]') SELECT * FROM table
Description: Sourcedb = C: / Specifies the folder AA.DBF of the FoxPro table to specify the file name of the FoxPro table.
/ ************* Export to Access ******************* / INSERT INTO OPENROWSET ('Microsoft.jet.OleDb.4.0 ',' X: /a.mdb ';' admin ';' ', a table) SELECT * FROM database name ..b table
/ ************* Import Access ******************************* / INSERT INTO B SELET * from OpenRowSet ('Microsoft.jet .Oledb.4.0 ',' x: /a.mdb ';' admin ';' ', a table) ******************** Import XML file
Declare @IDOC INTDECLARE @doc varchar (1000) - sample XML DocumentSet @doc = '
Zou Jian's / *********************************************** ********************* /
Stored procedure implemented with BCP
/ * Implementing data import / export stored procedures can implement import / export of the entire database / single table call example: - Export Call Example ---- Export Call Example ---- Export Call Example - Export Single Table EXEC File2Table 'ZJ', '', ' ',' xzkh_sa .. area, 'c: / zj.txt' ,1 ---- Export the entire database EXEC FILE2TABLE' ZJ ',' ',' ',' xzkh_sa ',' C: / DOCMAN ', 1
- Import call example --- Import a single table exec file2table 'zj', '', '', 'xzkh_sa .. Area,' c: / zj.txt' ,0 --- Import the entire database EXEC File2Table 'ZJ', '', '', 'Xzkh_sa', 'C: / DOCMAN', 0 * / IF EXISTS (SELECT 1 from sysobjects where name = 'file2table' And ObjectProperty (ID, 'isprocedure') = 1) Drop Procedure File2Table @ ServerName VARCHAR (200) - Server Name, @ Username Varchar (200) - User Name, if you use NT authentication mode, it is empty ', @password varchar (200) - password, @ TBNAME VARCHAR (500) - Database. DBO. Table name, if not specified: .dbo. Download all user tables, @ filename varchar (1000) - Import / Export Path / File name, if @ The TBName parameter indicates that the entire database is exported.
IF @tbname like '%.%.%' - If the table name is specified, directly export a single table begin set @ SQL = 'bcp' @ TBNAME CASE will 'ISOUT = 1 TEN' OUT 'ELSE' IN ' End '"' @ filename '" / w' '/ s' @ Servername Case When ISNULL (@username, '') = '' '' 'ELSE' / U ' @ username end ' / P ' isnull (@password,' ') exec master..xp_cmdshell @sqlendelsebegin - Export the entire database, define a cursor, remove all user table declare @m_tbname varchar (250) if Right (@ filename, 1) <>' / 'set @ filename = @ filename ' / '
set @ m_tbname = 'declare #tb cursor for select name from' @ tbname '.. sysobjects where xtype =' 'U' '' exec (@m_tbname) open #tb fetch next from #tb into @m_tbname while @@ fetch_status = 0 Begin set @ SQL = 'bcp' @ TBNAME '..' @ m_tbname case when @ iSout = 1 TEN 'OUT' ELSE 'IN' END '"' @ filename @ m_tbname '. Txt" / W ' ' / s ' @ ServerName Case When ISNULL (@username,') = '' TEN '' ELSE '/ U' @ UserName End '/ P' Isnull (@password, '') Exec master..xp_cmdshell @sql fetch next from #TB INTO @m_tbname End Close #tb deallocate #tb endgo still has an example, Zou Jian: / ***************** ***** Excel is guided to TXT **************************************** / Want to use Select * Into OpenDataSource (...) from openDataSource (...) to implement an Excel file content into a text file
Suppose there is two columns in Excel, the first list is named, the second list is a queue (16-bit) and the bank account is exported to the text file, and the first 8 bits and the post-8 bits are separated.
If you want to insert the statement above, the text file must exist, and there is a row: Name, Bank Account 1, Bank Account 2 You can use the following statement to make an important note file name and directory to modify according to your actual situation.
INSERT INTOOPENDATASOURCE ('microsoft.jet.oledb.4.0', 'text; hdr = yes; database = c: /') ... [aa # txt] -, aa # txt) - * / SELECT name, bank Account 1 = Left (bank account, 8), bank account 2 = Right (bank account, 8) from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'Excel 5.0; HDR = YES; IMEX = 2; Database = C : /a.xls' -, Sheet1 $) ... [Sheet1 $]
If you want to insert and generate a text file directly, use BCP
Declare @SQL VARCHAR (8000), @ TbName Varchar (50)
- First import the Excel table content into a global temporary table select @tbname = '[## Temp' Cast (newid () as varchar (40)) ']', @ SQL = 'SELECT name, bank account 1 = Left (bank account, 8), bank account 2 = Right (bank account, 8) INTO ' @ TBNAME ' from OpenDataSource ('' microsoft.jet.OleDb.4.0 ',' 'Excel 5.0; HDR = YES; IMEX = 2; Database = C: /A.XLS ') ... [Sheet1 $]' EXEC (@SQL) - then use BCP to export from global temporary table to text file set @ SQL = 'bcp " @ TBNAME '"OUT" C: /AA.TXT "/ S" (local) "/ p" "/ c'exec master..xp_cmdshell @SQL
- Delete Temporary Table EXEC ('Drop Table' @ TBNAME)
Add a file with BCP to import files to export to the database:
/ * - Import export of BCP-binary files
Support Image, Text, NTEXT field import / export image is suitable for binary files; text, ntext is suitable for text data files
Note: When importing all rows that cover the condition, all the rows that meet the conditions are also available to the specified file.
This stored procedure is implemented in Zou Jian 2003.08 with BCP ----------------- * /
/ * - Call example - Data Export EXEC P_BINARYIO 'ZJ', '', '', 'ACC_ Data ..TB', 'IMG', 'C: /ZJ1.DAT'
- Data Export EXEC P_BINARYIO 'ZJ', '', '', 'ACC_ Data .. Tb', 'IMG', 'C: /ZJ1.DAT', '', 0 - * / IF EXISTS Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_binaryio]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_binaryio] Go
Create Proc P_BINARYIO @ Server VARCHAR (30), - Server Name @uSername VARCHAR (30), - User Name @Password Varchar (30), - Password @tbname varchar (500), - Database .. Name @ FDNAME VARCHAR (30), - Field Name @FName Varchar (1000), - Directory File Name, During processing: @ filename .bak @ tj varchar (1000) = ', - processing conditions For data imports, if you contain @FDName, specify the table name prefix @isout bit = 1 --1 Export ((default), 0 Import As Declare @fname_in varchar (1000) --bcp processing answer file name, @ FSIZE VARCHAR (20) - The size of the file to be processed, @ m_tbname varchar (50) - temporary table name, @ SQL varchar (8000) - get the size of the import file if @ isout = 1 set @ fsize = ' 0'lsebegin create table #TB (optional VARCHAR (20), size INT, creation date varchar (10), creation time varchar (20), last written operation date varchar (10), last write operation time varchar ( 20) Is Null Begin Print 'File No' Return End
end
- Generate data processing answer file set @m_tbname = '[## Temp' Cast (newid () as varchar (40)) ']' set @ SQL = 'SELECT * INTO' @ m_tbname 'from (SELECT NULL AS Type UNION All SELECT 0 AS Prefix Union All SELECT ' @ fsize ' AS Length Union All Select Null AS End Union All Select Null AS Formats) A'EXEC (@SQL) SELECT @ fname_in = @ fname '_ Temp', @ SQL = 'bcp "' @ m_tbname '" out "' @ fname_in '" / s "' @ servename case when isnull (@username, '') = '' TEN '' ELSE '" / u "' @ username end '"/ p"' isnull (@password, '') '"/ c'exec master..xp_cmdshell @ SQL - Delete Temporary table set @ SQL =' Drop Table ' @ m_tbnameexec @SQL) IF @ isout = 1begin set @ SQL = 'bcp "SELECT TOP 1' @ fdname 'from' @ TBNAME CASE ISNULL (@tj, '') when '' Ten 'Else' Where ' @ Tj end '"queryout"' @ fname '"/ s" @ servename case when isnull (@username,' ') =' 'TEN' ELSE '"/ u"' @ username end '"/ P"' isnull (@password, '') '"/ i" "'" "'" "" ""' "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" " 0 ' @ fdname ' INTO ' @ m_tbname ' from ' @ TBNAME EXEC (@SQL)
- Import data to temporary table set @ SQL = 'bcp "' @ m_tbname '" in "' @fname '" / s "@ Servename Case When Inull (@username,' ') =' 'TEN' 'ELSE' "/ u" ' @ username end ' "/ p" ' isnull (@password,' ') ' "/ i" ' @ fname_in ' "'exec master..xp_cmdshell @ SQL - Import Data into the official table set @ SQL = 'update' @ TBNAME 'set' @ fdname '= B.' @fdname 'from' @ TBNAME 'A,' @ m_tbname ' B ' Case Isnull (@tj,' ') when' '' '' else 'Where' @ Tj end exec (@SQL) - Delete Data Processing Temporary Table Set @ SQL = 'Drop Table' @ m_tbnameeend
- Delete data processing answer file set @ SQL = 'del' @ fname_INEXEC MASTER..XP_CMDSHELL @SQL
GO Supplementary Description / ** Imported text files EXEC MASTER..XP_CMDSHELL 'BCP "DBNAME..TABLENAME" IN C: /DT.TXT -C -SSERVERNAME -USA -PASSWORD'
Changed to the following, no quotient exec master..xp_cmdshell 'bcp dbname..tablename in c: /dt.txt -c -ssrvername -usa -ppassword'
/ ** Export text file EXEC MASTER..XP_CMDSHELL 'BCP "DBNAME..TABLENAME" OUT C: /DT.TXT -C -SSSERVERNAME-ISA -PASSWORD' This sentence needs to be quoted
