Lchzh's column
9CBSBLOG | Contact | Polymer | Login 3 Articles | 0 Collection | 1 Comments | 0 TrackBacks
article
Delphi
(RSS)
PB (RSS)
Archive
January 2005 (3)
(MS SQL Server) SQL Statement Import Export Daquan
SQL statement import export
/ ******* Export to Excel ****** /
EXEC MASTER.. cP_CMDSHEC '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 (Subject No. AS Numeric (10, 2)) AS NVARCHAR (255)) '' Convert 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-Password'
/ ** Export text file *** /
Exec master..xp_cmdshell 'BCP "DBNAME..TABLENAME" OUT C: /DT.TXT -C -SSERVERNAME -USA-Password'
or
Exec master..xp_cmdshell 'bcp "Select * from dbname..tablename" Queryout C: /Dt.txt -c -ssrvername -usa -ppassword "
Export to TXT text, separate with commas
Exec master..xp_cmdshell 'bcp "library name .. Name" D: /TT.TXT "-c -t, -u sa -p password'
Bulk INSERT library name .. 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 Data 4.DBF]')
- * /
- / * DBASE III file
SELECT * FROM
OpenRowSet ('Microsoft.jet.Oledb.
4.0
'
, 'DBASE III; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Information 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! = "USA" ORDER BY Country')
Go
/ **************** Export to DBF *************** /
If you want to export data to a structure (ie, existing) FoxPro list, 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
Description:
SOURCEDB = C: / Specify the folder where the FoxPro table is located
AA.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 Sleet * from OpenRowSet ('Microsoft.jet.Oledb.
4.0
'
,
'X: /a.mdb'; 'admin'; '', a table)
******************* Import XML file
Declare @IDOC INT
Declare @doc varchar (1000)
--Sample XML Document
Set @doc = '
Happy Customer.
'
- 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
/ ****************************************************************************************************** ****************** /
Stored procedure implemented with BCP
/ *
Implement data import / export stored procedure
Import / export throughout the database / single table can be implemented according to different parameters
Call example:
- Export call example
---- Export a single table
Exec file2table 'zj', '', '', 'xzkh_sa .. Area,' c: /zj.txt' ,1
---- Export the entire database
Exec file2table 'zj', '', '', 'xzkh_sa', 'C: / DOCMAN', 1
- Imported call example
---- Import a single table
Exec file2table 'zj', '', '', 'xzkh_sa .. Area,' 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) - Username, if using NT authentication mode, it is empty ''
, @ Password Varchar (200) - Password
, @ TBNAME VARCHAR (500) - Database. DBO. Table name, if not specified: .dbo. Download, export all user tables for 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 .txt
@ isout bit --1 is exported, 0 is imported
AS
Declare @SQL VARCHAR (8000)
If @tbname like '%.%.%' - If the table name is specified, the single table is exported directly.
Begin
Set @ SQL = 'BCP' @ TBNAME
Case when @ ixout = 1 TEN 'OUT' ELSE 'IN' END
'"' @ Filename '" / w'
'/ S' @ ServerName Case When ISNULL (@username, '') = '' TEN 'ELSE' / U ' @ username end
'/ P' isnull (@password, '')
Exec master..xp_cmdshell @SQL
end
Else
Begin exports the entire database, define a cursor, remove all 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 @ ixout = 1 TEN 'OUT' ELSE 'IN' END
'"' @ Filename @ m_tbname '. Txt" / w'
'/ S' @ ServerName
Case When Isnull (@username, '') = '' TEN '' 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 is guided to txt ********************* ***************** /
I want to use
Select * inTo OpenDataSource (...) from openDataSource (...)
Import an Excel file content into a text file
Suppose there is two columns in Excel, the first list is named, the second list is a very account (16 digits)
And the bank account is exported to the text file and divided by two parts, and the first 8 bits and the last 8 bits were separated.
If you want to insert the statement above, the text file must exist, and there is a line: name, bank account 1, bank account 2
Then you can insert 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 a text file directly, you must use bcpdeclare @sql varchar (8000), @ tbname varchar (50)
- Import 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)
- Then use BCP from global temporary table to text files
Set @ SQL = 'BCP "' @ TBNAME '" OUT "C: /AA.TXT" / S "(local)" / p "" / c'
Exec master..xp_cmdshell @SQL
- Delete temporary table
EXEC ('Drop Table' @ TBNAME)
Import files into the stored procedure exported to the database with BCP:
/ * - Import export of BCP-binary files
Support Import / Export of Image, Text, NTEXT field
Image is suitable for binary; Text, NText is suitable for text data files
Note: When importing, all rows that meet the conditions are covered
When exported, all the rows that meet the conditions are also available to the specified file.
This stored procedure is implemented in only BCP
Zou Jian 2003.08 ----------------- * /
- / * - Call example
--Data output
EXEC P_BINARYIO 'ZJ', '', '', 'ACC_ Demo Data ..tb', 'IMG', 'C: /ZJ1.DAT'
--Data output
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), - Username
@password varchar (30), - password
@tbname varchar (500), - Database .. Name
@fdname varchar (30), - field name
@fname varchar (1000), - Directory file name, use / override during processing: @ filename .bak
@tj varchar (1000) = '', - Processing Conditions. For data imports, if you contain @fdname, specify the table name prefix
@isOUT bit = 1 --1 Export ((default), 0 import
AS
Declare @fname_in varchar (1000) --BCP processing answer file name
, @ fsize varchar (20) - the size of the file to be processed
, @ m_tbname varchar (50) - temporary table name
, @ SQL VARCHAR (8000)
-, get the size of the imported file
IF @ isout = 1set @ fsize = '
0 '
Else
Begin
Create Table #TB (optional VARCHAR (20), size INT
Create a date VARCHAR (10), create time varchar (20)
, Last written operation date varchar (10), last write operation time varchar (20)
, Last visited date varchar (10), last visited 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 'files have not found'
Return
end
end
- Generate data processing response files
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, '') = '' TEN ''
Else '"/ u" @ username end
'"/ P"' isnull (@password, '') '"/ c'
Exec master..xp_cmdshell @SQL
- Delete 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 '' Ten ''
Else 'Where' @ tj end
'"Queryout" @ fname
'"/ S" @ servename
Case When Isnull (@USERNAME, '') = '' TEN ''
Else '"/ u" @ username end
'"/ P"' isnull (@password, '')
'"/ I"' @ fname_in '""
Exec master..xp_cmdshell @SQL
end
Else
Begin
- Prepare temporary table for data import
Set @ SQL = 'SELECT TOP0
'
@ 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, '') = '' TEN ''
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 '' Ten ''
Else 'Where' @ tj end
EXEC (@SQL)
- Delete 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-Password'
Change to the following, no quotation
EXEC MASTER..XP_CMDSHEC 'BCP DBNAME..TABLENAME IN C: /DT.TXT -C -SSERVERNAME -USA-Password'
/ ** Export text file ** /
Exec master..xp_cmdshell 'BCP "DBNAME..TABLENAME" OUT C: /DT.TXT -C -SSERVERNAME -USA-Password'
This sentence needs an extension number
Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
[Click here to favor this article]
Published on
2005
Year 01
Month 24
day
10:48 AM
comment
# 回:: (MS SQL Server) SQL statement import export 2005-02-02 9:18 AM BILL
Good!
Comment
title:
Please enter the title
Name:
Please enter the name of the name
Website:
comment
Please enter a comment
remember me?
Powered by:
Copyright? Lchzh
