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" "
/ *********** Import Excel Select * 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 SQL INSERT 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 2 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-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 [Page]
Declare @idoc int declare @doc varchar (1000) --Sample XML Document Set @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 INTO OpenDataSource ('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 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)
/ ****************************************************************************************************** ******************* / [Page]
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 File2Table go create 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 as Declare @SQL VARCHAR (8000) if @tbname like '%.%.%' - If the table name is specified, then derive a single table Begin Set directly @ SQL = 'bcp' @ TBNAME CASE WHEN @ ISOUT = 1 TEN 'OUT' ELSE 'IND ' "' @ filename '" / w ' ' / s' @ Servername Case When ISNULL ( @Username, '') = '' 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 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_CMDSHEC MASTER..XP_CMDSHELL @SQL fetch next from # @m_tbname endclose #tb deallocate #TB end go / **************************** *** / exec sp_addlinkedServer 'ORACLESVR', '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
