MS SQL Import Export Daquan

Quote A big brother DE import exported Daquan ******* 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 OpenRowSet ('Microsoft.jet.OleDb.4.0', 'Excel 5.0; HDR = YES; Database = C: /Test.xls', SHEET1 $) - -------------------------------------------------- ----- Select * from OpenDataSource ('microsoft.jet.oledb.4.0', 'data source = "c: /test.xls"; user ID = admin; password =; extended profment = excel 5.0') .. .xactions

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

/ ** Import text files EXEC MASTER..XP_CMDSHELL 'BCP "DBNAME..TABLENAME" IN C: /DT.TXT -C -SSERVERNAME -USA-PPASSWORD'

/ ** Export text file EXEC MASTER..XP_CMDSHELL 'BCP "DBNAME..TABLENAME" OUT C: /DT.TXT -C -SSERVERNAME -USA-PPASSWORD' or EXEC MASTER HAINP_CMDSHELL 'BCP "SELECT * from DBNAME .. Tablename "Queryout C: /Dt.txt -c -sservername -usa -ppassword"

Export to TXT text, separate the exec master..xp_cmdshell 'bcp "with comma" library name .. Table 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 ';' ', a table) SELECT * FROM database name ..b table

/ ************* Import Access ******************************* / INSERT INTO B SELET * from OpenRowSet ('Microsoft.jet .Oledb.4.0 ',' x: /a.mdb ';' admin ';' ', a table)

TX reply: TXLICENHE () reputation: 167 2003-10-10 18: 52: 32Z score: 0 ***************** Import XML file

Declare @IDOC INTDECLARE @doc varchar (1000) - sample XML DocumentSet @doc = ' importantant Happy customer. '- Create An Internal Reperesentation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @ doc-- Execute a SELECT statement using OPENXML rowset provider.SELECT * FROM OPENXML (@idoc, '/ root / Customer / Order', 1) WITH (oid char (5), Amount Float, Comment Ntext 'Text ()) EXEC SP_XML_REMOVEDOCUMENT @IDOC

TX replihood: TXLICENHE () reputation: 167 2003-10-10 18: 54: 30Z score: 0 Zou Jian / ****************** * Guide the entire database ****************************************** /

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 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 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 asdeclare @SQL varchar (8000)

IF @tbname like '%.%.%' - If the table name is specified, 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 table declare @m_tbname varchar (250) if Right (@ filename, 1) <> '/' set @ filename = @ filename '/'