Each database import SQL statement collection

Each database tutorial Import SQL statement Collection

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"

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 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:vfp98data;
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 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: Specifies 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 '; ', table A

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=; ') ... Products

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 (...)
Implement to import an Excel file content into a text file

Suppose 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 text files directly, you will 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 use BCP to export to a text file 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 Pro Cedure file2table
Go
CREATE PROCEDURE file2table
@servername varchar (200)-server name
, @username varchar (200) --User name, if NT Authentication is null '
, @password varchar (200)--Password
, @tbname varchar (500)--database. dbo. Table name, if you do not specify:. dbo. Table name, All users of the database are exported
, @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 location path, and the file name is automatically used 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, direct export of a single table
begin
Set @sql = ' bcp ' + @tbname
+case when @isout =1 then ' out ' E LSE ' 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, remove all user tables
Declare @m_tbname varchar
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 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 * 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)