/ ******* 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 proties = excel 5.0 ') ... xactions / ** Import text file EXEC MASTER..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 .. XP_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 ',' Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDb = C: / ',' Select * from [aa.dbf] ') SELECT * FROM Table Description: SourceDb = C: / Specify the folder where the FoxPro table is located 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 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 ',' '' '' ';' '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 intDeclare @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 text files 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) / ***************************************************************************************************************** ************************************************************** Call example: - Export call Example ---- Export single table exec file2table 'zj', '', '', 'xzkh_sa .. Area,' 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 .. area 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 - Username, if used in NT authentication, @ Password varchar (200) - password, @ TBNAME VARCHAR (500) - Database .dbo. Table name, if not specified: .dbo. Table name, export all user tables of the database, @ 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, file name automatic table Name .txt, @ isout bit --1 is exported, 0 is imported into an asdeclare @SQL varchar (8000) if @tbname like '%.%.%' - If the table name is specified, then 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 (@ 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'
