SQL statement import export
/ ******* 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; passwo Rd =; extended Properties = Excel 5.0 ') ... XActionS / ** Import text file EXEC MASTER..XP_CMDSHELL' BCP DBNAME..TABLENAME IN C: /DT.TXT-C: / ** Export text file EXEC MASTER..XP_CMDSHELL 'BCP DBNAME..TABLENAME OUT C: /DT.TXT -C -SSERVERNAME-ISA-Password' or Exec Master "Select * from dbname..tablename" Queryout C: /Dt.txt -c -sservername -usa -ppassword '
Export to TXT text, separate the exec master..xp_cmdshell 'bcp name .. Name "OUT" 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 operating.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 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; SOUR CEDB = C: / ',' Select * from [aa.dbf] ") SELECT * FROM table Description: SourceDb = c: / Specifies the file name of the FoxPro table in the FoxPro table. / *** ********** Export to Access ******************* / INSERT INTO OpenRowSet ('Microsoft.jet.OleDb.4.0', 'X /; F 数据 数据... 表 名 导 导 表 导 导 表 表 表 表 表 表 表 入 表 表 表 表 表 表 表 表 表 表 表 表 表************ / INSERT INTO B Table Selet * from OpenRowSet ('Microsoft.jet.OleDb.4.0', 'x: /a.mdb'; 'admin'; '', a table ) File name is 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 = '
IDOC ??????? / ********************* EXCEL is guided to txt *************** ************************ / I want to use Select * Into OpenDataSource (...) from openDataSource to implement the contents of an Excel file Import to a text file assumes two columns in Excel, the first list is named, the second list is a very account (16-bit) and the bank account is exported to the text file, and the first 8 bits and the last 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 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 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 .. 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) - the user name, if the NT authentication, was Empty '', @ Password Varchar (200) - Password, @ TBName Varchar (500) - Database .dbo. Table name, if not specified: .dbo. Download, export all user tables for 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 To introduce asclare @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 (@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 @@ 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' '
