/ ******* 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 / * Dynamic file name declare @fn varchar (20), @ s varchar (1000) set @fn =' c: /test.xls'Set @s = '' 'Microsoft. JET.OLEDB.4.0 ',' DATA SOURCE = "' @ fn '"; user ID = admin; password =; extended proties = excel 5.0 '' set @S = 'SELECT * from OpenDataSource (' @ S ') ... Sheet1 $' Exec (@S) * / SELECT CAST (Cast) AS NVARCHAR (255)) '' Re-converted alias from OpenDataSource ('Microsoft. Jet. OleDb.4.0 ',' Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0 ') ... XActionS / ********* ************ EXCEL is derived to remote SQLINSERT OpenDataSource ('sqloledb', 'data source = remote IP; user ID = sa; password = password'). Database name .dbo. Table name (column Name 1, column 2) SELECT column name 1, column name 2From OpenDataSource ('Microsoft.jet.OleDb.4.0', 'Data Source = "C: / Test.xls";user id = admin; password =; extended profment = excel 5.0 ') ... xactions / ** Import text file EXEC MASTER..TX_CMDSHELL' BCP DBNAME..TABLENAME IN C: /DT.TXT - c -sservername -usa -ppassword '/ ** Export text file EXEC MASTER..XP_CMDSHEC' BCP DBNAME..TABLENAME OUT C: /DT.TXT -C -SSERVERNAME -USA-Password 'or Exec Master "uP_cmdshell' bcp" Select * from dbname..tablename "
Queryout c: /dt.txt -c -sservername -usa -ppassword 'Exports to TXT text, separate the exec master..xp_cmdshell' bcp "in comma" library name .. Name "D: /tt.txt" -c -t, -u sa -p password'bulk INSERT library name .. Table 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 into the FoxPro list that has been generated (ie, existing) FoxPro, you can use the following SQL Statement Insert Into OpenRowSet ('MSDasql', 'Driv Er = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDb = C: / ',' Select * from [aa.dbf] ') Select * from: SourceDb = C: / Specify FoxPro Table Folder AA.DBF Specify 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 table selet * from OpenRowSet ('Microsoft.jet.OleDb.4.0', 'x: /a.mdb ';'; '
Admin ';' ', a table) file named parameter declare @fname varchar (20) set @fname =' d: /test.mdb'Exec ('SELECT A. * from OpenDataSource (' Microsoft.jet.Oledb. 4.0 '' '' ' @ fname ' ''; '' admin '' ';' '' ',' '' ', Topics AS A') Select * from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'Data Source = "f: /northwind.mdb"; Jet OLEDB: Database password = 123; user ID = admin; password =; ') ... Products ******************* ** Import XML file declare @idoc intendlare @doc varchar (1000) - sample XML DocumentSet @doc = '
1) with (Oid Char (5), Amount Float, Comment Ntext 'Text ()') EXEC SP_XML_REMOVEDOCUMENT @IDOC ??????? / ************************ ****** EXCEL is guided to txt *************************************************** / Want to use Select * Into OpenDataSource (...) from openDataSource to implement an Excel file content into a text file to assume two columns in Excel, the first list is name, the second list is a very account (16) and the bank account is derived from the text file and divided by two parts, the first 8 bits and the last 8 bits were separated.
Zou Jian: If you want to insert 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 pay attention file name and directory according to your actual situation Modify. 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, you must use bcpdeclare @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) / * *************************************************************************************************************************** ***************** The stored procedures implemented with BCP / * Implementing the data import / export stored procedure can implement import / export of the entire database / single table call according to different parameters. Example: - Export Call Example ---- Export a single table EXEC File2Table 'ZJ', '', '', 'Xzkh_sa .. Region Information', 'C: /Zj.txt', 1 ---- Exporting the entire database EXEC FILE2TABLE' ZJ ',' ',' ',' Xzkh_sa ',' C: / docman ', 1 - Import Call Example --- Import a single table EXEC FILE2TABLE' ZJ ',' ',' ',' Xzkh_sa .. Regional Information ',' 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 File2Tablegocreate procedure File2Table @ servername varchar (200) - the server name, @ username varchar (200) - the user name, if the NT authentication mode, null ', @ password varchar (200) - password, @ TBNAME VARCHAR (500) - Database .dbo. Table name, if not specified: .dbo. Download all user tables, export all user tables, @ filename varchar 1000) Import / Export Path / File Name, if the @TBName parameter indicates to export the entire database, this parameter is the file storage path, the file name is automatically used .txt, @ isout bit --1 is exported, 0 is Import asclare @sql varchar (8000) if @tbname like '%.%.%' - If the table name is specified, directly export a single table beginset @ SQL = 'bcp' @ TBName Case When @ ixout = 1 'out' else 'in' end '"' @ filename '" / w' '/ s' @ Servername Case When ISNULL (@usename,') = '' TEN '' 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 #tbfetch next from #tb @m_tbnamewhile @@ fetc h_status = 0begin 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, '') =
