******* Export to Excel
EXEC MASTER.. cP_CMDSHEC '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), @fn = 'c: /test.xls'Set @S =' '' Microsoft.jet.Oledb.4.0 ',' ' Data Source = "' @ fn '"; user ID = admin; password =; extended profr 5.0 '' set @s = 'SELECT * OPENDATASOURCE (' @ S ') ... Sheet1 $' EXEC (@S) * /
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
/ ********************** EXCEL is derived to remote SQLInsert OpenDataSource ('sqloledb', 'data source = remote IP; user ID = sa; password = password' ). Tin name .dbo. Table name (column name 1, column name 2) SELECT column name 1, column name 2From 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-PSSWORD'
/ ** 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 .. 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 list, 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 ';' ', 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 =;') ... Product
******************* Import XML file
Declare @IDOC INTDECLARE @doc varchar (1000) - sample XML DocumentSet @doc = '
???????
/ ********************* EXCEL is guided to txt ********************* ***************** / want to use Select * Into OpenDataSource (...) from openDataSource to import 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.
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, 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 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)
/ ****************************************************************************************************** ****************** /
Stored procedure implemented with BCP
/ * 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 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 File2Tablegocreate procedure File2Table @ servername varchar (200) - the server name, @ username varchar (200) - - Username, if you use NT authentication mode, it is empty ', @ 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, the file name is automatically used. TXT, @ isout bit --1 is exported, 0 is imported into the asDeclare @SQL varchar (8000) if @tbname like '%.%.%' - If the table name is specified, then directly export a single table beginset @ SQL = ' BCP ' @ TBNAME CASE WHEN @ isout = 1 Then' out 'else' in 'end ' "' @ filename '" / w ' ' / s' @ Servername Case When ISNULL (@username, '' ) = '' TEN 'ELSE' / u ' @ username end ' / p ' isnull (@password,' ') exec master..xp_cmdshell @sqlendelsebegin - Export the entire database, define a cursor, remove all user tables 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 into @m_tbnamewhile @@ fetch_status = 0beginset @ 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 @ SQLFETCH Next from #TB INTO @m_tbnamendclose #tbdeallocate #TB endgo / ************************************** Oracle 7.3 ',' MSDara ',' OrCldb'go
Delete from OpenQuery (Mailser, 'Select * from Yulin')
Select * from OpenQuery (Mailser, 'Select * from Yulin')
Update OpenQuery (mailser, 'select * from yulin where id = 15') set disorder = 555, cataGO = 888
INSERT INTO OpenQuery (Mailser, 'Select Disorder, catago from yulin "Values (333, 777)
supplement:
For exporting with BCP, there is no field name.
Export with OpenRowSet and need to be built in advance.
Import in OpenRowSet, except for Access and Excel, non-public data imports are not supported
