Import Export Data Statement summary

Export Data | statement/* 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, @s varchar (1000)
Set @fn = ' C:\Test.xls '
Set @s = ' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' + @fn + '; User id=admin; password=; Extended Properties=excel 5.0 ""
Set @s = ' SELECT * from OpenDataSource (' +@s+ ') ... sheet1$ '
EXEC (@s)
*/SELECT Cast (CAST (account number 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 leads to remote SQL
Insert OpenDataSource (
' SQLOLEDB ',
' Data source= remote IP; User Id=sa; password= Password '
). The library 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 File
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.. xp_cmdshell ' bcp ' select * from dbname. TableName "Queryout c:\DT.txt-c-sservername-usa-ppassword" exported to txt text, separated by commas
EXEC master.. xp_cmdshell ' bcp ' library name ... Table 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
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' DBase IV; Hdr=no;imex=2;database=c:\ ', ' select * from [Customer profile 4.dbf] ')
--*/--/* DBase III Document
SELECT * FROM
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' DBase III; Hdr=no;imex=2;database=c:\ ', ' select * from [Customer profile 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!= ' The USA ' ORDER by country ')
Go
/***************** exported to DBF ***************/
If you want to export data to an already generated structure (that is, an existing) FoxPro table, you can insert into OPENROWSET (' MSDASQL ') directly with the following SQL statement.
' 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 resides
Aa. DBF Specifies the filename 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 ... Table B/************* Import access********************/
Insert into B table Selet * FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0 '),
' X:\A.mdb '; ' Admin '; ', a table) file name as 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 int
DECLARE @doc varchar (1000)
--sample XML Document
SET @doc = '
<root>
<customer cid= "C1" name= "Janine" city= "Issaquah" >
<order oid= "O1" date= "1/20/1996" amount= "3.5"/>
<order oid= "O2" date= "4/30/1997" amount= "13.4" >customer was very satisfied
</Order>
</Customer>
<customer cid= "C2" name= "Ursula" city= "Oelde" >
<order oid= "O3" date= "7/14/1999" amount= "note=" Wrap it Blue
White red ">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<order oid= "O4" date= "1/20/1996" amount= "10000"/>
</Customer>
</root>
'
--Create An internal representation 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??????? /**********************excel leads to txt****************************************/
Want to use
SELECT * Into OpenDataSource (...) from OpenDataSource (...)
Implementation to import an Excel file content into a text file assuming that there are two columns in Excel, the first column is the name, and the second column is a very line account number (16-bit)
And the bank account number is exported to the text file in two parts, the first 8 digits and the latter 8 bits separately.
Kingjian:
If you want to insert the above statement, the text file must exist with one line: Name, bank account 1, Bank account 2
You can then insert it with the following statement
Note that the file name and directory are modified according to your actual situation. 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 a text file directly, use BCP declare @sql varchar (8000), @tbname varchar (50)--first import the contents of the Excel table 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 export to a text file with bcp from a global temporary table
Set @sql = ' bcp ' + @tbname + ' ' Out ' c:\aa.txt '/S ' (local) '/p '/C '
EXEC master.. xp_cmdshell @sql--Delete temporary tables
EXEC (' drop table ' + @tbname)
/******************** Guide the entire database *********************************************/stored procedures implemented with BCP
/*
Implementing stored procedures for data import/export
You can import/export an entire database/single table based on different parameters
Call Example:
--Export Call example
----Export a single table
exec file2table ' zj ', ', ', ' Xzkh_sa. Regional information ', ' 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. Regional 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 File2table
Go
CREATE PROCEDURE File2table
@servername varchar (200)--server name
, @username varchar (200)--user name, NULL if NT authenticated
, @password varchar (200)--Password
, @tbname varchar (500)--database. dbo. Table name, if you do not specify:. dbo. Table name, export all user tables for the database
, @filename varchar (1000)--import/Export the path/file name, if the @tbname parameter indicates that the entire database is exported, this parameter is the file store path, the file name automatically with the table name. txt
, @isout bit--1 for export, 0 for import
As
DECLARE @sql varchar (8000) If @tbname like '%.%.% '--If a table name is specified, export a single table directly
Begin
Set @sql = ' bcp ' + @tbname
+case when @isout the =1 then ' out ' else ' in '
+ ' "' + @filename + '"/w '
+ '/S ' + @servername
+case when IsNull (@username, ') = ' Then ' Else '/u ' + @username end
+ '/P ' +isnull (@password, ')
EXEC master.. xp_cmdshell @sql
End
Else
Begin--Export the entire database, define the cursor, and remove all the user tables
DECLARE @m_tbname varchar (250)
If Right (@filename, 1) <> ' Set @filename = @filename + ' Set @m_tbname = ' Declare #tb cursor for select name from ' + @tb Name+ '.. 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 the =1 then ' out ' else ' in '
+ ' "' + @filename + @m_tbname + ' txt '/w '
+ '/S ' + @servername
+case when IsNull (@username, ') = ' Then ' Else '/u ' + @username end
+ '/P ' +isnull (@password, ')
EXEC master.. xp_cmdshell @sql
FETCH NEXT from #tb into @m_tbname
End
Close #tb
Deallocate #tb
End
Go
/************* Oracle **************/
EXEC sp_addlinkedserver ' Oraclesvr ',
' Oracle 7.3 ',
' Msdaora ',
' Orcldb '
Go Delete from openquery (Mailser, ' select * to Yulin ') SELECT * from OPENQUERY (mailser, ' select * from Yulin ') Update O Penquery (Mailser, ' select * from Yulin where id=15 ') set disorder=555,catago=888 insert INTO OpenQuery (mailser, ' Select Dis Order,catago from Yulin ') VALUES (333,777) added: For export with bcp, there is no field name. To export with OPENROWSET, you need to build a table beforehand. Import with OPENROWSET, do not support non-native data import except Access and Excel