SQL statement Import and Export (from the Internet)

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

 

----------------------

How to Convert the Convert function to date in SQL

 

Select convert (varchar (100), GETDATE (), 0): 05 16 2006 AM
Select convert (varchar (100), GETDATE (), 1): 05/16/06
Select convert (varchar (100), GETDATE (), 2): 06.05.16
Select convert (varchar (100), GETDATE (), 3): 16/05/06
Select convert (varchar (100), GETDATE (), 4): 16.05.06
Select convert (varchar (100), GETDATE (), 5): 16-05-06
Select convert (varchar (100), GETDATE (), 6): 16 05 06
Select convert (varchar (100), GETDATE (), 7): 05 16, 06
Select convert (varchar (100), GETDATE (), 8): 10:57:46
Select convert (varchar (100), GETDATE (), 9): 05 16 2006 10: 57: 46: 827AM
Select convert (varchar (100), GETDATE (), 10): 05-16-06
Select convert (varchar (100), GETDATE (), 11): 06/05/16
Select convert (varchar (100), GETDATE (), 12): 060516
Select convert (varchar (100), GETDATE (), 13): 16 05 2006 10: 57: 46: 937
Select convert (varchar (100), GETDATE (), 14): 10: 57: 46: 967
Select convert (varchar (100), GETDATE (), 20): 10:57:47
Select convert (varchar (100), GETDATE (), 21): 10:57:47. 157
Select convert (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM
Select convert (varchar (100), GETDATE (), 23 ):
Select convert (varchar (100), GETDATE (), 24): 10:57:47
Select convert (varchar (100), GETDATE (), 25): 10:57:47. 250
Select convert (varchar (100), GETDATE (), 100): 05 16 2006 AM
Select convert (varchar (100), GETDATE (), 101): 05/16/2006
Select convert (varchar (100), GETDATE (), 102): 2006.05.16
Select convert (varchar (100), GETDATE (), 103): 16/05/2006
Select convert (varchar (100), GETDATE (), 104): 16.05.2006
Select convert (varchar (100), GETDATE (), 105): 16-05-2006
Select convert (varchar (100), GETDATE (), 106): 16 05 2006
Select convert (varchar (100), GETDATE (), 107): 05 16,200 6
Select convert (varchar (100), GETDATE (), 108): 10:57:49
Select convert (varchar (100), GETDATE (), 109): 05 16 2006 10: 57: 49: 437AM
Select convert (varchar (100), GETDATE (), 110): 05-16-2006
Select convert (varchar (100), GETDATE (), 111): 2006/05/16
Select convert (varchar (100), GETDATE (), 112): 20060516
Select convert (varchar (100), GETDATE (), 113): 16 05 2006 10: 57: 49: 513
Select convert (varchar (100), GETDATE (), 114): 10: 57: 49: 547
Select convert (varchar (100), GETDATE (), 120): 10:57:49
Select convert (varchar (100), GETDATE (), 121): 10:57:49. 700
Select convert (varchar (100), GETDATE (), 126): 2006-05-16T10: 57: 49.827
Select convert (varchar (100), GETDATE (), 130): 18 ???? ?????? 1427 10: 57: 49: 907AM
Select convert (varchar (100), GETDATE (), 131): 18/04/1427 10: 57: 49: 920AM
Note:
Use CONVERT:
CONVERT (data_type [(length)], expression [, style])
Parameters
Expression
Is any valid Microsoft SQL Server "expression ..
Data_type
The data types provided by the target system, including bigint and SQL _variant. User-Defined data types cannot be used.
Length
Optional parameters of the nchar, nvarchar, char, varchar, binary, or varbinary data type.
Style
Date Format style to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type); or string format style, to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type ).
SQL Server supports the data format in the Arabic style using the Kuwait algorithm.
In the table, the two columns on the left represent converting datetime or smalldatetime to the style value of the character data. Add 100 to the style value to obtain the four-digit year (yyyy) of the century ).
Without Century digital (yy) with Century digital (yyyy)
Standard
Input/Output **
-0 or 100 (*) Default Value: mon dd yyyy hh: miAM (or PM)
1 101 us mm/dd/yyyy
2 102 ANSI yy. mm. dd
3 103 UK/French dd/mm/yy
4 104 German dd. mm. yy
5 105 Italian dd-mm-yy
6 106-dd mon yy
7 107-mon dd, yy
8 108-hh: mm: ss
-9 or 109 (*) Default Value + millisecond mon dd yyyy hh: mi: ss: mmmAM (or PM)
10 110 US mm-dd-yy
11 111 yy/mm/dd in Japan
12 112 ISO yymmdd
-13 or 113 (*) European default value + millisecond dd mon yyyy hh: mm: ss: mmm (24 h)
14 114-hh: mi: ss: mmm (24 h)
-20 or 120 (*) ODBC specification yyyy-mm-dd hh: mm: ss [. fff]
-21 or 121 (*) ODBC specifications (in milliseconds) yyyy-mm-dd hh: mm: ss [. fff]
-126 (***) ISO8601 yyyy-mm-dd Thh: mm: ss. mmm (without spaces)
-130 * Hijri ***** dd mon yyyy hh: mi: ss: mmmAM
-131 * Hijri ***** dd/mm/yy hh: mi: ss: mmmAM

* The default value (style 0, 100, 9, 109, 13, 113, 20, 120, 21, or 121) always returns century digits (yyyy ).
** Input when converted to datetime; output when converted to character data.
* ** It is specially used for XML. For the conversion from datetime or smalldatetime to character data, the output format is shown in the table. For data conversion from float, money, smallmoney to character, the output is equivalent to style 2. For data conversion from real to character, the output is equivalent to style 1.
* *** Hijri is a calendar system with several variations. Microsoft SQL Server "2000 uses the Kuwait algorithm.

Important by default, SQL Server interprets two-digit years based on the end year 2049. That is, the year 49 with two digits is interpreted as 2049, and the year 50 with two digits is interpreted as 1950. Many client applications (such as client applications based on OLE automation objects) Use 2030 as the end year. SQL Server provides a configuration option ("") to change the end year used by SQL Server and process the date in a consistent manner. However, the safest way is to specify a four-digit year.

When converting from smalldatetime to character data, the style that contains seconds or milliseconds will display zero at these locations. When converting from the datetime or smalldatetime value, you can use the appropriate char or varchar data type length to cut off the date part that is not required.