SQL語句匯入匯出大全 （摘自互連網）

/*******  匯出到excel
EXEC master..xp_cmdshell ’bcp SettleDB.dbo.shanghu out c:/temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""’

/***********  匯入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(科目編號 as numeric(10,2)) as nvarchar(255))+’　’ 轉換後的別名
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
  ’Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions

/** 匯入文字檔
EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:/DT.txt -c -Sservername -Usa -Ppassword’

/** 匯出文字檔
EXEC master..xp_cmdshell ’bcp "dbname..tablename" out c:/DT.txt -c -Sservername -Usa -Ppassword’
或
EXEC master..xp_cmdshell ’bcp "Select * from dbname..tablename" queryout c:/DT.txt -c -Sservername -Usa -Ppassword’

匯出到TXT文本，用逗號分開
exec master..xp_cmdshell ’bcp "庫名..表名" out "d:/tt.txt" -c -t ,-U sa -P password’

BULK INSERT 庫名..表名
FROM ’c:/test.txt’
WITH (
    FIELDTERMINATOR = ’;’,
    ROWTERMINATOR = ’/n’
)

--/* dBase IV檔案
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’
,’dBase IV;HDR=NO;IMEX=2;DATABASE=C:/’,’select * from [客戶資料4.dbf]’)
--*/

--/* dBase III檔案
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’
,’dBase III;HDR=NO;IMEX=2;DATABASE=C:/’,’select * from [客戶資料3.dbf]’)
--*/

--/* FoxPro 資料庫
select * from openrowset(’MSDASQL’,
’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/’,
’select * from [aa.DBF]’)
--*/

/**************匯入DBF檔案****************/
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
/***************** 匯出到DBF ***************/
如果要匯出資料到已經產生結構(即現存的)FOXPRO表中,可以直接用下面的SQL語句

insert into openrowset(’MSDASQL’,
’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/’,
’select * from [aa.DBF]’)
select * from 表

說明:
SourceDB=c:/  指定foxpro表所在的檔案夾
aa.DBF        指定foxpro表的檔案名稱.

 

/*************匯出到Access********************/
insert into openrowset(’Microsoft.Jet.OLEDB.4.0’,
   ’x:/A.mdb’;’admin’;’’,A表) select * from 資料庫名..B表

/*************匯入Access********************/
insert into B表 selet * from openrowset(’Microsoft.Jet.OLEDB.4.0’,
   ’x:/A.mdb’;’admin’;’’,A表)

*********************  匯入 xml　檔案

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

/********************導整個資料庫*********************************************/

用bcp實現的預存程序

/*
 實現資料匯入/匯出的預存程序
         根據不同的參數,可以實現匯入/匯出整個資料庫/單個表
 調用樣本:
--匯出調用樣本
----匯出單個表
exec file2table ’zj’,’’,’’,’xzkh_sa..地區資料’,’c:/zj.txt’,1
----匯出整個資料庫
exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:/docman’,1

--匯入調用樣本
----匯入單個表
exec file2table ’zj’,’’,’’,’xzkh_sa..地區資料’,’c:/zj.txt’,0
----匯入整個資料庫
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)  --伺服器名
,@username varchar(200)   --使用者名稱,如果用NT驗證方式,則為空白’’
,@password varchar(200)   --密碼
,@tbname varchar(500)   --資料庫.dbo.表名,如果不指定:.dbo.表名,則匯出資料庫的所有使用者表
,@filename varchar(1000)  --匯入/匯出路徑/檔案名稱,如果@tbname參數指明是匯出整個資料庫,則這個參數是檔案存放路徑,檔案名稱自動用表名.txt
,@isout bit      --1為匯出,0為匯入
as
declare @sql varchar(8000)

if @tbname like ’%.%.%’ --如果指定了表名,則直接匯出單個表
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_cmdshell @sql
end
else
begin --匯出整個資料庫,定義遊標,取出所有的使用者表
 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=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_cmdshell @sql
  fetch next from #tb into @m_tbname
 end
 close #tb
 deallocate #tb  
end
go

/**********************Excel導到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
實現將一個Excel檔案內容匯入到一個文字檔

假設Excel中有兩列，第一列為姓名，第二列為很行帳號(16位)
且銀行帳號匯出到文字檔後分兩部分，前8位和後8位分開。

如果要用你上面的語句插入的話,文字檔必須存在,而且有一行:姓名,銀行帳號1,銀行帳號2
然後就可以用下面的語句進行插入
注意檔案名稱和目錄根據你的實際情況進行修改.

insert into
opendatasource(’MICROSOFT.JET.OLEDB.4.0’
,’Text;HDR=Yes;DATABASE=C:/’
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,銀行帳號1=left(銀行帳號,8),銀行帳號2=right(銀行帳號,8)
from
opendatasource(’MICROSOFT.JET.OLEDB.4.0’
,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’
--,Sheet1$)
)...[Sheet1$]

如果你想直接插入並產生文字檔,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

--首先將excel表內容匯入到一個全域暫存資料表
select @tbname=’[##temp’+cast(newid() as varchar(40))+’]’
 ,@sql=’select 姓名,銀行帳號1=left(銀行帳號,8),銀行帳號2=right(銀行帳號,8)
into ’+@tbname+’ from
opendatasource(’’MICROSOFT.JET.OLEDB.4.0’’
,’’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’’
)...[Sheet1$]’
exec(@sql)

--然後用bcp從全域暫存資料表匯出到文字檔
set @sql=’bcp "’+@tbname+’" out "c:/aa.txt" /S"(local)" /P"" /c’
exec master..xp_cmdshell @sql

--刪除暫存資料表
exec(’drop table ’+@tbname)

用bcp將檔案匯入匯出到資料庫的預存程序:

/*--bcp-二進位檔案的匯入匯出

 支援image,text,ntext欄位的匯入/匯出
 image適合於二進位檔案;text,ntext適合於文本資料檔案

 注意:匯入時,將覆蓋滿足條件的所有行
  匯出時,將把所有滿足條件的行也出到指定檔案中

 此預存程序僅用bcp實現
鄒建 2003.08-----------------*/

/*--調用樣本
--資料匯出
 exec p_binaryIO ’zj’,’’,’’,’acc_示範資料..tb’,’img’,’c:/zj1.dat’

--資料匯出
 exec p_binaryIO ’zj’,’’,’’,’acc_示範資料..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),--伺服器名稱
@username varchar (30), --使用者名稱
@password varchar (30), --密碼
@tbname varchar (500),  --資料庫..表名
@fdname varchar (30),  --欄位名
@fname varchar (1000), --目錄+檔案名稱,處理過程中要使用/覆蓋:@filename+.bak
@tj varchar (1000)=’’,  --處理條件.對於資料匯入,如果條件中包含@fdname,請指定表名首碼
@isout bit=1   --1匯出((預設),0匯入
AS
declare @fname_in varchar(1000) --bcp處理應答檔案名稱
 ,@fsize varchar(20)   --要處理的檔案的大小
 ,@m_tbname varchar(50)  --暫存資料表名
 ,@sql varchar(8000)

--則取得匯入檔案的大小
if @isout=1
 set @fsize=’0’
else
begin
 create table #tb(可選名 varchar(20),大小 int
  ,建立日期 varchar(10),建立時間 varchar(20)
  ,上次寫操作日期 varchar(10),上次寫操作時間 varchar(20)
  ,上次訪問日期 varchar(10),上次訪問時間 varchar(20),特性 int)
 insert into #tb
 exec master..xp_getfiledetails @fname
 select @fsize=大小 from #tb
 drop table #tb
 if @fsize is null
 begin
  print ’檔案未找到’
  return
 end

end

--產生資料處理應答檔案
set @m_tbname=’[##temp’+cast(newid() as varchar(40))+’]’
set @sql=’select * into ’+@m_tbname+’ from(
 select null as 類型
 union all select 0 as 首碼
 union all select ’+@fsize+’ as 長度
 union all select null as 結束
 union all select null as 格式
 ) 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
--刪除暫存資料表
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
 --為資料匯入準備暫存資料表
 set @sql=’select top 0 ’+@fdname+’ into ’
  +@m_tbname+’ from ’ +@tbname
 exec(@sql)

 --將資料匯入到暫存資料表
 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
 
 --將資料匯入到正式表中
 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)

 --刪除資料處理暫存資料表
 set @sql=’drop table ’+@m_tbname
end

--刪除資料處理應答檔案
set @sql=’del ’+@fname_in
exec master..xp_cmdshell @sql

go

/** 匯入文字檔
EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:/DT.txt -c -Sservername -Usa -Ppassword’

改為如下，不需引號
EXEC master..xp_cmdshell ’bcp dbname..tablename in c:/DT.txt -c -Sservername -Usa -Ppassword’

/** 匯出文字檔
EXEC master..xp_cmdshell ’bcp "dbname..tablename" out c:/DT.txt -c -Sservername -Usa -Ppassword’
此句需加引號    

 

－－－－－－－－－－－－－－－－－－－－－－

SQL中Convert轉換函式對日期的轉換用法

 

SELECT CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
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): 2006-05-16 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
SELECT CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
SELECT CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
SELECT CONVERT(varchar(100), GETDATE(), 24): 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
SELECT CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
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, 2006
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): 2006-05-16 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 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
說明:
使用 CONVERT：
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
參數
expression
是任何有效 Microsoft SQL Server" 運算式。。
data_type
目標系統所提供的資料類型，包括 bigint 和 sql_variant。不能使用使用者定義的資料類型。
length
nchar、nvarchar、char、varchar、binary 或 varbinary 資料類型的選擇性參數。
style
日 期格式樣式，藉以將 datetime 或 smalldatetime 資料轉換為字元資料（nchar、nvarchar、char、varchar、nchar 或 nvarchar 資料類型）；或者字串格式樣式，藉以將 float、real、money 或 smallmoney 資料轉換為字元資料（nchar、nvarchar、char、varchar、nchar 或 nvarchar 資料類型）。
SQL Server 支援使用科威特演算法的阿拉伯樣式中的資料格式。
在表中，左側的兩列表示將 datetime 或 smalldatetime 轉換為字元資料的 style 值。給 style 值加 100，可獲得包括世紀數位的四位年份 (yyyy)。
不帶世紀數位 (yy) 帶世紀數位 (yyyy)
標準
輸入/輸出**
- 0 或 100 (*)  預設值 mon dd yyyy hh:miAM（或 PM）
1 101 美國 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英國/法國 dd/mm/yy
4 104 德國 dd.mm.yy
5 105 意大利 dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 或 109 (*)  預設值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10 110 美國 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd
- 13 或 113 (*)  歐洲預設值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 或 120 (*)  ODBC 規範 yyyy-mm-dd hh:mm:ss[.fff]
- 21 或 121 (*)  ODBC 規範（帶毫秒） yyyy-mm-dd hh:mm:ss[.fff]
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss.mmm（不含空格）
- 130* Hijri**** dd mon yyyy hh:mi:ss:mmmAM
- 131* Hijri**** dd/mm/yy hh:mi:ss:mmmAM

*    預設值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始終返回世紀數位 (yyyy)。
** 當轉換為 datetime時輸入；當轉換為字元資料時輸出。
*** 專門用於 XML。對於從 datetime或 smalldatetime 到 character 資料的轉換，輸出格式如表中所示。對於從 float、money 或 smallmoney 到 character 資料的轉換，輸出等同於 style 2。對於從 real 到 character 資料的轉換，輸出等同於 style 1。
****Hijri 是具有幾種變化形式的日曆系統，Microsoft SQL Server" 2000 使用其中的科威特演算法。

重要  預設情況下，SQL Server 根據截止年份 2049 解釋兩位元字的年份。即，兩位元字的年份 49 被解釋為 2049，而兩位元字的年份 50 被解釋為 1950。許多用戶端應用程式（例如那些基於 OLE Automation 物件的用戶端應用程式）都使用 2030 作為截止年份。SQL Server 提供一個配置選項（"兩位元字的截止年份"），藉以更改 SQL Server 所使用的截止年份並對日期進行一致性處理。然而最安全的辦法是指定四位元字年份。

當從 smalldatetime 轉換為字元資料時，包含秒或毫秒的樣式將在這些位置上顯示零。當從 datetime 或 smalldatetime 值進行轉換時，可以通過使用適當的 char 或 varchar 資料類型長度來截斷不需要的日期部分。