(MS SQL Server) SQL Statement Import Export Encyclopedia

server| 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

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

/** 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:\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 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
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

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


/******************** 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 ' + @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


/**********************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.


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)


To import a file to a stored procedure that is exported to a database by using bcp:


Import export of/*--bcp-binary files

Support for import/export of Image,text,ntext fields
Image is suitable for binary files; text,ntext for text data files

Note: When importing, all rows that meet the criteria are overwritten
When exporting, all rows that meet the criteria are also shown in the specified file

This stored procedure is implemented in BCP only
Jiangjian 2003.08-----------------* *

/*--Call Example
--Data export
exec p_binaryio ' zj ', ', ', ' acc_ demo data. TB ', ' img ', ' C:\zj1.dat '

--Data export
exec p_binaryio ' zj ', ', ', ' acc_ demo data. TB ', ' img ', ' c:\zj1.dat ', ', 0
--*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_binaryio] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_binaryio]
Go

Create proc P_binaryio
@servename varchar,--server name
@username varchar (30),--User name
@password varchar (30),--Password
@tbname varchar (500),--database. Table name
@fdname varchar (30),--field name
@fname varchar (1000),--directory + filename, to be used/overwritten during processing: @filename +.bak
@tj varchar (1000) = ',--processing condition. For data import, specify a table name prefix if the condition contains @fdname
@isout bit=1--1 Export ((default), 0 import
As
DECLARE @fname_in varchar (1000)--BCP processing answer file name
, @fsize varchar (20)--size of the file to be processed
, @m_tbname varchar (50)--Temporary table name
, @sql varchar (8000)

--Gets the size of the import file
If @isout =1
Set @fsize = ' 0 '
Else
Begin
CREATE table #tb (optional name varchar (20), size int
, Creation date varchar (10), Creation time varchar (20)
, last write operation date varchar (10), last write operation time varchar (20)
, Last accessed date varchar (10), last access time varchar (20), attribute int)
INSERT INTO #tb
EXEC master.. Xp_getfiledetails @fname
Select @fsize = size from #tb
drop table #tb
If @fsize is null
Begin
print ' File not found '
Return
End

End

--Generate Data processing answer file
Set @m_tbname = ' [# #temp ' +cast (NEWID () as varchar (40)) + '] '
Set @sql = ' select * into ' + @m_tbname + ' from (
Select NULL as Type
UNION ALL select 0 as prefix
UNION ALL SELECT ' + @fsize + ' as length
UNION ALL select NULL as End
UNION ALL select NULL as Format
A
EXEC (@sql)
Select @fname_in = @fname + ' _temp '
, @sql = ' bcp ' + @m_tbname + ' out ' + @fname_in
+ ' "/S" ' + @servename
+case when IsNull (@username, ') = ' Then '
Else '/u ' + @username end
+ ' "/P" ' +isnull (@password, ') + ' "/C '
EXEC master.. xp_cmdshell @sql
--Delete temporary tables
Set @sql = ' drop table ' + @m_tbname
EXEC (@sql)

If @isout =1
Begin
Set @sql = ' bcp ' select top 1 ' + @fdname + ' from '
+ @tbname +case isnull (@tj, ') when ' then '
Else ' where ' + @tj end
+ ' "queryout" ' + @fname
+ ' "/S" ' + @servename
+case when IsNull (@username, ') = ' Then '
Else '/u ' + @username end
+ ' "/P" ' +isnull (@password, ')
+ ' "/I" ' + @fname_in + ' "'
EXEC master.. xp_cmdshell @sql
End
Else
Begin
--Prepare temporary tables for data import
Set @sql = ' Select top 0 ' + @fdname + ' into '
+ @m_tbname + ' from ' + @tbname
EXEC (@sql)


--Import data into a temporary table
Set @sql = ' bcp ' + @m_tbname + ' in ' + @fname
+ ' "/S" ' + @servename
+case when IsNull (@username, ') = ' Then '
Else '/u ' + @username end
+ ' "/P" ' +isnull (@password, ')
+ ' "/I" ' + @fname_in + ' "'
EXEC master.. xp_cmdshell @sql

--Import data into a formal table
Set @sql = ' Update ' + @tbname
+ ' Set ' + @fdname + ' =b. ' + @fdname
+ ' from ' + @tbname + ' A, '
+ @m_tbname + ' B '
+case IsNull (@tj, ') when ' then '
Else ' where ' + @tj end
EXEC (@sql)

--Deleting a data-processing temporary table
Set @sql = ' drop table ' + @m_tbname
End

--delete Data processing answer file
Set @sql = ' del ' + @fname_in
EXEC master.. xp_cmdshell @sql

Go


/** Import Text File
EXEC Master.. xp_cmdshell ' bcp ' dbname. TableName "in C:\DT.txt-c-sservername-usa-ppassword"

Instead of quotes
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"
This sentence should be quoted