SQL statement import export @ ******* Export to Excel Exec master..xp_cmdshell 'bcp settledb.dbo.shanghu out c: /temp1.xls -c -q -s "gnetdata / gnetdata" -u "SA "-P" "" "" "imported" ", 'microsoft.jet.Oledb.4.0', 'data source =" c: /test.xls "; user ID = Admin; Password =; extended Properties = Excel 5.0 ') ... XActionS SELECT CAST (Cast) AS NVARCHAR (255)) ' 'Convert Alias from OpenDataSource (' Microsoft .Jet.oledb.4.0 ',' data source = "c: /test.xls"; user ID = admin; password =; extended proties = excel 5.0 ') ... xactions / ** Import text files Exec Master .. XP_cmdshell 'BCP "DBNAME..TABLENAME" IN C: /DT.TXT -C -SSERVERNAME -USA-PPASSWORD' / ** Export file exec master..xp_cmdshell 'bcp "dbname..tablename" OUT C: / DT. TXT-CSSERVERNAME-ISA-Password 'or Exec Master "," Select * from dbname..tablename "Queryout C: /dt.txt -c -ssrvername -usa -ppassword' exported to txt text, with comma Separate the exec master..xp_cmdshell 'bcp "library name .. Name" OUT "D: /TT.TXT" -c -t, -u sa -p password' bulk INSERT library name .. Table name from 'C: / Test.txt 'with (Fiel DTERMINATOR = ';', rowterminator = '/ n') - / * DBase IV file Select * from openrowset ('microsoft.jet.oledb.4.0', 'dbase iv; hdr = no; IMEX = 2; data = 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 Data 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: / Specify FoxPro Table The folder AA.DBF specifies the file name of the FoxPro table. / ************ Export to Access ****************** * / INSERT INTO OPENROWSET ('Microsoft.jet.OleDb.4.0', 'x: /a.mdb'; 'admin'; '', table) Select * from database name ..b table / ***** ******** Import Access ****************************** / INSERT INTO B SELET * from OpenRowSet ('Microsoft.jet.OleDb.4.0', 'x: /a.mdb'; 'admin'; '', table A ********************* Import XML file Declare @idoc int declare @doc VARCHAR (1000) --Sample XML Document Set @doc = 'Customer Was Very Satisfied
Important
Happy Customer.
'- Create an internal representation of the XML document EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -. Execute a SELECT statement using OPENXML rowset provider SELECT * FROM OPENXML (@idoc,.' / Root / Customer / Order ', 1) With (Oid Char (5), Amount Float, Comment Ntext 'Text () EXEC SP_XML_REMOVEDOCUMENT @IDOC / ****************************** *********************************** / with BCP implementation / * Implementing data import / export stored procedures can implement import / export of the entire database / single table calling 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 Go Create 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 , @ TB Name varchar (500) - database.dbo. Download, if not specified: .dbo. Download, export all user tables of the database, @ filename varchar (1000) - Import / Export Path / File Name, if @ The TBName parameter indicates that the entire database is exported, and this parameter is the file storage path. The file name is automatically used .txt, @ isout bit --1 is exported, 0 is imported as declare @SQL varchar (8000) if @tbname like ' %.%.% '- If the table name is specified, directly export a single table begin set @ SQL =' bcp ' @ TBNAME CASE WHEN @ ISout = 1 TEN' OUT 'ELSE' IN 'end ' "' @ filename '"/ w' '/ s' @ ServerName Case When ISNULL (@usename,') = '' TEN '' ELSE '/ U' @ UserName End '/ P' Isnull @password, '') Exec master..xp_cmdshell @SQL END ELSE BEGIN - 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 from #tb @m_tbname while @@ fetch_status = 0 begin set @ SQL = 'bcp' @ tbname '.' @ m_tbname case when @ iSout = 1 Then 'Out' else 'IN' end '"' @ filename @ m_tbname '. txt" / w' '/ s' @ Servername Case When Isnull (@usename, '') = '' TEN '' ELSE '/ U' @ Username end '/ p' isnull (@password, '') exec master..xp_cmdshell @sql fetch next from #tb @m_tbname end close #tb deallocate # End Go / ******* *************** EXCEL is guided to txt ****************************** ********* / Want to use Select * Into OpenDataSource (...) from openDataSource to import an Excel file content into a text file to assume two columns in Excel, the first list as Name, the second list is a quotation (16-bit) and the bank account is derived from two parts, and the first 8 bits and the last 8 bits are separated.
If you want to insert it with the statement above, the text file must exist, and there is a line: name, bank account 1, bank account 2 can then use the following statement to make a note file name and directory to modify according to your actual situation. Insert INTO OpenDataSource ('microsoft.jet.oledb.4.0', 'text; hdr = yes; data = 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 from the 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) uses BCP Document Import Export to Database: / * - BCP-Binary Import Export Support IMAGE, TEXT, NTEXT field Import / Export Image is suitable for binary files; text, ntext is suitable for text data file Note: When imported, When all rows that cover the conditions are overwritten, all the rows that meet the conditions will also come to the fingers. This stored procedure is only used by BCP only with BCP 2003.08 ----------------- * / / * - Call Example - Data Export EXEC P_BINARYIO 'ZJ', '' , '', 'ACC_ demo data ..tb', 'img', 'c: / zj1.dat' - Data export EXEC P_BINARYIO 'ZJ', '', '', 'ACC_ demo 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 @servename 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, Use / override during processing: @ filename .bak @tj varchar (1000) = '', - handle condition. For data import, if the condition contains @fdname Please specify a 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 file to be processed Size, @ m_tbname varchar (50) - Temporary table name, @ SQL VARCHAR (8000) - get the size of the import file if @ isout = 1 set @ fsize = '0' else begin create table #TB (optional Name VARCHAR (20), size INT, creation date varchar (10), creation time varchar (20), last written operation date varchar (10), last write operation time varchar (20), last visited date varchar (10 ), Last visited time varchar (20), feature int) INSERT INTO #TB EXEC MASTER..XP_GETFILEDETAILS @Fname Select @ fsize = Size from #tb Drop table #tb if @FSIZE IS NULL BEGIN PRINT 'File No' Return End end - Generate Data Process 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 CA SE1EN isnull (@username, ') =' 'TEN' 'ELSE' "/ u" ' @ username end ' "@password, '') '" / c' exec MASTER..XP_CMDSHELL @SQL - Delete Temporary Table Set @ SQL = 'Drop Table' @ m_tbname EXEC (@SQL) IF @ ISout = 1 Begin Set @ SQL = 'BCP "SELECT TOP 1' @ fdname 'from' @ TBNAME CASE ISNULL (@tj, ') when' 'TEN' ELSE 'Where' @ Tj end '"queryout"' @fname '"/ s" @ servename
