(Ms SQL Server) SQL statement Import and Export Daquan (choose from lchzh blog)

SQL statement Import and Export

/****** Export to excel
Exec master.. xp_mongoshell '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 (subject number as numeric (255) as nvarchar () + ''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 a text file
Exec master .. xp_mongoshell 'bcp "dbname .. tablename" in C:/dt.txt-C-sservername-USA-ppassword'

/** Export a text file
Exec master .. xp_mongoshell 'bcp "dbname .. tablename" out C:/dt.txt-C-sservername-USA-ppassword'
Or
Exec master .. xp_mongoshell 'bcp "select * From dbname .. tablename" queryout C:/dt.txt-C-sservername-USA-ppassword'

Export to TXT text, separated by commas
Exec master.. xp_mongoshell 'bcp "database name... table name" out "D:/tt.txt"-c-t,-U sa-P password'

Bulk insert database 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] ')
--*/

/************** Import the 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 the generated structure (that is, the existing table) FoxPro table, 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

Note:
Sourcedb = C:/Specify the folder where the Foxpro table is located
AA. DBF specifies the name of The FoxPro table.

 

********************/
Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'X:/a. mdb '; 'admin'; '', table A) 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)

* ********************* Import 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 = "100" 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 procedure implemented with BCP

/*
Stored Procedure for Data Import/Export
You can import/export the entire database or a single table based on different parameters.
Call example:
-- Export call example
---- Export a single table
Exec file2table 'zj', '','', 'xzkh _ sa .. region 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 .. region 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. If you use the NT authentication method, it is null''
, @ Password varchar (200) -- Password
, @ Tbname varchar (500) -- database. DBO. Table name. If not specified:. DBO. Table Name, all user tables of the database will be exported.
, @ Filename varchar (1000) -- import/export path/file name. If @ tbnameverification indicates that the entire data warehouse is exported, the file name will automatically use table name .txt
, @ Isout bit -- 1 is for export, 0 is for Import
As
Declare @ SQL varchar (8000)

If @ tbname like '%. %. %' -- if the table name is specified, a single table is exported directly.
Begin
Set @ SQL = 'bcp' + @ tbname
+ Case when @ isout = 1 then 'out' else' in 'end
+ '"' + @ Filename + '"/W'
+ '/S' + @ servername
+ Case when isnull (@ username, '') = ''then'' 'else'/U' + @ username end
+ '/P' + isnull (@ password ,'')
Exec master .. xp_mongoshell @ SQL
End
Else
Begin -- export the entire database, define the cursor, and retrieve all user tables
Declare @ m_tbname varchar (250)
If right (@ filename, 1) <> '/'set @ filename = @ filename + '/'

Set @ m_tbname = 'Clare # 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 then 'out' 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_mongoshell @ SQL
Fetch next from # TB into @ m_tbname
End
Close # TB
Deallocate # TB
End
Go

/********************** Export to TXT ************* ***************************/
Intended Use
Select * into OpenDataSource (...) from OpenDataSource (...)
Import an Excel file to a text file

Suppose there are two columns in Excel, the first column is name, and the second column is a very row account (16 digits)
The exported bank accounts are divided into two parts: the first eight digits and the last eight digits.

If you want to insert the statement above, the text file must exist and have one line: name, bank account 1, bank account 2
Then you can use the following statement to insert
Note that the file name and directory are modified according to your actual situation.

Insert
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 directly insert and generate a text file, you need to use BCP

Declare @ SQL varchar (8000), @ tbname varchar (50)

-- First import the Excel table content to 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)

-- Use bcp to export data from a global temporary table to a text file
Set @ SQL = 'bcp "'+ @ tbname +'" out "C:/aa.txt"/s "(local)"/P ""/C'
Exec master .. xp_mongoshell @ SQL

-- Delete a temporary table
Exec ('drop table' + @ tbname)

The stored procedure of using bcp to import and export files to the database:

/* -- BCP-Import and Export of binary files

Supports import/export of image, text, and ntext Fields
Image is suitable for binary files; text and ntext are suitable for text data files.

Note: during import, all rows meeting the conditions will be overwritten.
During export, all rows that meet the conditions will also be exported to the specified file.

This stored procedure is only implemented using BCP
--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 (30), -- 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 + file name, use/overwrite during processing: @ filename +. Bak
@ TJ varchar (1000) = '', -- processing condition. For data import, if the condition contains @ fdname, specify the table name prefix.
@ Isout bit = 1 -- 1 Export (default), 0 Import
As
Declare @ fname_in varchar (1000) -- BCP processes the response file name
, @ Fsize varchar (20) -- size of the file to be processed
, @ M_tbname varchar (50) -- temporary table name
, @ SQL varchar (8000)

-- Obtains the size of the imported file.
If @ isout = 1
Set @ fsize = '0'
Else
Begin
Create Table # Tb (varchar (20), int size
, Created on varchar (10), created on varchar (20)
The last write operation date is varchar (10), and the last write operation time is varchar (20)
, Last access date varchar (10), last access time varchar (20), feature 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 a data processing response 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 ended
Union all select null as format
)'
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_mongoshell @ SQL
-- Delete a temporary table
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_mongoshell @ SQL
End
Else
Begin
-- Prepare a temporary table for Data Import
Set @ SQL = 'select Top 0' + @ fdname + 'into'
+ @ M_tbname + 'from' + @ tbname
Exec (@ SQL)

-- Import data to 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_mongoshell @ SQL

-- Import data to 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)

-- Delete a temporary data processing table
Set @ SQL = 'drop table' + @ m_tbname
End

-- Delete the data processing response File
Set @ SQL = 'del '+ @ fname_in
Exec master .. xp_mongoshell @ SQL

Go

/** Import a text file
Exec master .. xp_mongoshell 'bcp "dbname .. tablename" in C:/dt.txt-C-sservername-USA-ppassword'

As follows, no quotation marks are required.
Exec master .. xp_mongoshell 'bcp dbname .. tablename in C:/dt.txt-C-sservername-USA-ppassword'

/** Export a text file
Exec master .. xp_mongoshell 'bcp "dbname .. tablename" out C:/dt.txt-C-sservername-USA-ppassword'
Quotation marks are required for this sentence.