Detailed T-SQL operation file

Source: Internet
Author: User
Tags dbase

/******* Export to Excel
EXEC Master. xp_cmdshell ' bcp SettleDB.dbo.shanghu out c:\temp1.xls-c-q-s "Gnetdata/gnetdata"-u "sa"-P "" '


/*********** Importing 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 '
). 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




/** Importing text files
EXEC Master. xp_cmdshell ' bcp dbname. TableName in C:\DT.txt-c-sservername-usa-ppassword '


/** Exporting text files
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"


Export 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 File
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] ')
--*/


/************** Importing DBF files ****************/
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 an already generated structure (that is, an existing) FoxPro table, you can use the following SQL statement directly


INSERT INTO OPENROWSET (' MSDASQL ',
' 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
Cal DBF Specifies 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 table Selet * FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0 ',
' X:\A.mdb '; ' Admin '; ', table A)


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


importing XML files


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 lead to txt****************************************/
Want to use
SELECT * Into OpenDataSource (...) from OpenDataSource (...)
To import the contents of an Excel file into a text file


Suppose there are two columns in Excel, the first column is the name, and the second column is a row account number (16-bit)
And the bank account number is exported to a text file divided into two parts, the first 8 and the last 8 bits apart.




Kingjian:
If you want to insert the above statement, the text file must exist, and there is a 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 number 1=left (bank account, 8), Bank account 2=right (bank account number, 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, you will need to use BCP


DECLARE @sql varchar (8000), @tbname varchar (50)


--First import the contents of Excel table into a global temp 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 to a text file from a global temp 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




/*
A stored procedure that implements data import/export
Depending on the parameters, you can import/export the entire database/single table
Invocation Example:
--Export Invocation 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 Invocation 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 using NT Authentication method
, @password varchar (200)--Password
, @tbname varchar (500)-database. dbo. Table name, if you do not specify:. dbo. Table name, export all user tables of the database
, @filename varchar (1000)--Import/export path/file name, if the @tbname parameter indicates that the entire database is exported, this parameter is the file storage path, the file name is automatically used as 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 =1 and then ' off ' else ' in ' End
+ ' "' + @filename + '"/w '
+ '/S ' + @servername
+case when IsNull (@username, ") = ' Then ' Else ' u ' + @username end
+ '/P ' +isnull (@password, ')
EXEC master. xp_cmdshell @sql
End
Else
Begin--Exports the entire database, defines the cursor, and takes out all the user tables
DECLARE @m_tbname varchar (250)
If Right (@filename, 1) <> ' \ ' set @[email protected]+ ' \ '


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 and then ' off ' else ' in ' End
+ ' ' + @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 * 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)





Add:


For export with bcp, there is no field name.


To export with OPENROWSET, you need to build a table beforehand.


Import with OpenRowset, non-native data import is not supported except Access and Excel

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.