MS SQL Server——SQL語句導入導出大全

2020-09-10 安布達爾

/******* 導出到excel

EXEC master..xp_cmdshell &34;GNETDATA/GNETDATA&34;sa&34;&39;

/*********** 導入Excel

SELECT *

FROM OpenDataSource( &39;,

&34;c:\test.xls&39;)...xactions

SELECT cast(cast(科目編號 as numeric(10,2)) as nvarchar(255))+&39; 轉換後的別名

FROM OpenDataSource( &39;,

&34;c:\test.xls&39;)...xactions

select * from OPENROWSET(&39;,&39;,Sheet1$)

HDR=YES;Excel第一行當成標題行

HDR=NO;第一行不當成標題行

/** 導入文本文件

EXEC master..xp_cmdshell &34;dbname..tablename&39;

/** 導出文本文件

EXEC master..xp_cmdshell &34;dbname..tablename&39;

或

EXEC master..xp_cmdshell &34;Select * from dbname..tablename&39;

導出到TXT文本，用逗號分開

exec master..xp_cmdshell &34;庫名..表名&34;d:\tt.txt&39;

BULK INSERT 庫名..表名

FROM &39;

WITH (

FIELDTERMINATOR = &39;,

ROWTERMINATOR = &39;

)

--/* dBase IV文件

select * from

OPENROWSET(&39;

,&39;,&39;)

--*/

--/* dBase III文件

select * from

OPENROWSET(&39;

,&39;,&39;)

--*/

--/* FoxPro 資料庫

select * from openrowset(&39;,

&39;,

&39;)

--*/

/**************導入DBF文件****************/

select * from openrowset(&39;,

&39;,

&34;USA&39;)

go

/***************** 導出到DBF ***************/

如果要導出數據到已經生成結構(即現存的)FOXPRO表中,可以直接用下面的SQL語句

insert into openrowset(&39;,

&39;,

&39;)

select * from 表

說明:

SourceDB=c:\ 指定foxpro表所在的文件夾

aa.DBF 指定foxpro表的文件名.

/*************導出到Access********************/

insert into openrowset(&39;,

&39;;&39;;&39;,A表) select * from 資料庫名..B表

/*************導入Access********************/

insert into B表 selet * from openrowset(&39;,

&39;;&39;;&39;,A表)

********************* 導入 xml　文件

DECLARE @idoc int

DECLARE @doc varchar(1000)

--sample XML document

SET @doc =&34;C1&34;Janine&34;Issaquah&34;O1&34;1/20/1996&34;3.5&34;O2&34;4/30/1997&34;13.4&34;C2&34;Ursula&34;Oelde&34;O3&34;7/14/1999&34;100&34;Wrap it blue

white red&34;O4&34;1/20/1996&34;10000&39;

-- 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, &39;, 1)

WITH (oid char(5),

amount float,

comment ntext &39;)

EXEC sp_xml_removedocument @idoc

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

用bcp實現的存儲過程

/*

實現數據導入/導出的存儲過程

根據不同的參數,可以實現導入/導出整個資料庫/單個表

調用示例:

--導出調用示例

----導出單個表

exec file2table &39;,&39;,&39;,&39;,&39;,1

----導出整個資料庫

exec file2table &39;,&39;,&39;,&39;,&39;,1

--導入調用示例

----導入單個表

exec file2table &39;,&39;,&39;,&39;,&39;,0

----導入整個資料庫

exec file2table &39;,&39;,&39;,&39;,&39;,0

*/

if exists(select 1 from sysobjects where name=&39; and objectproperty(id,&39;)=1)

drop procedure File2Table

go

create procedure File2Table

@servername varchar(200) --伺服器名

,@username varchar(200) --用戶名,如果用NT驗證方式,則為空&39;

,@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 &39; --如果指定了表名,則直接導出單個表

begin

set @sql=&39;+@tbname

+case when @isout=1 then &39; else &39; end

+&34;&39;&39;

+&39;+@servername

+case when isnull(@username,&39;)=&39; then &39; else &39;+@username end

+&39;+isnull(@password,&39;)

exec master..xp_cmdshell @sql

end

else

begin --導出整個資料庫,定義遊標,取出所有的用戶表

declare @m_tbname varchar(250)

if right(@filename,1)<>&39; set @filename=@filename+&39;

set @m_tbname=&tb cursor for select name from &39;..sysobjects where xtype=&39;U&39;&tb

fetch next from 39;bcp &39;..&39; out &39; in &39; &39;+@filename+@m_tbname+&34; /w&39; /S &39;&39;&39;&39; /U &39; /P &39;&tb into @m_tbname

end

close tb

end

go

/**********************Excel導到Txt****************************************/

想用

select * into opendatasource(...) from opendatasource(...)

實現將一個Excel文件內容導入到一個文本文件

假設Excel中有兩列，第一列為姓名，第二列為銀行帳號(16位)

且銀行帳號導出到文本文件後分兩部分，前8位和後8位分開。

如果要用你上面的語句插入的話,文本文件必須存在,而且有一行:姓名,銀行帳號1,銀行帳號2

然後就可以用下面的語句進行插入

注意文件名和目錄根據你的實際情況進行修改.

insert into

opendatasource(&39;

,&39;

)...[aatxt)

--*/

select 姓名,銀行帳號1=left(銀行帳號,8),銀行帳號2=right(銀行帳號,8)

from

opendatasource(&39;

,&39;

--,Sheet1$)

)...[Sheet1$]

如果你想直接插入並生成文本文件,就要用bcp

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

--首先將excel表內容導入到一個全局臨時表

select @tbname=&39;+cast(newid() as varchar(40))+&39;

,@sql=&39;+@tbname+&39;&39;&39;&39;&39;

exec(@sql)

--然後用bcp從全局臨時表導出到文本文件

set @sql=&34;&39;&34;c:\aa.txt&34;(local)&34;&39;

exec master..xp_cmdshell @sql

--刪除臨時表

exec(&39;+@tbname)

用bcp將文件導入導出到資料庫的存儲過程:

/*--bcp-二進位文件的導入導出

支持image,text,ntext欄位的導入/導出

image適合於二進位文件;text,ntext適合於文本數據文件

注意:導入時,將覆蓋滿足條件的所有行

導出時,將把所有滿足條件的行也出到指定文件中

此存儲過程僅用bcp實現

鄒建 2003.08-----------------*/

/*--調用示例

--數據導出

exec p_binaryIO &39;,&39;,&39;,&39;,&39;,&39;

--數據導出

exec p_binaryIO &39;,&39;,&39;,&39;,&39;,&39;,&39;,0

--*/

if exists (select * from dbo.sysobjects where id = object_id(N&39;) and OBJECTPROPERTY(id, N&39;) = 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)=&39;, --處理條件.對於數據導入,如果條件中包含@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=&39;

else

begin

create table tb

exec master..xp_getfiledetails @fname

select @fsize=大小 from tb

if @fsize is null

begin

print &39;

return

end

end

--生成數據處理應答文件

set @m_tbname=&39;+cast(newid() as varchar(40))+&39;

set @sql=&39;+@m_tbname+&39;+@fsize+&39;

exec(@sql)

select @fname_in=@fname+&39;

,@sql=&34;&39;&34;&39;&34;&39;&39;&39;&39;&34;&39;&34;&39;&39;&39;

exec master..xp_cmdshell @sql

--刪除臨時表

set @sql=&39;+@m_tbname

exec(@sql)

if @isout=1

begin

set @sql=&34;select top 1 &39; from &39;&39;&39;&39; where &39;&34;&39;&34;&39;&39;&39;&39;&34;&39;&34;&39;&39;&34;&39;&39;

exec master..xp_cmdshell @sql

end

else

begin

--為數據導入準備臨時表

set @sql=&39;+@fdname+&39;

+@m_tbname+&39; +@tbname

exec(@sql)

--將數據導入到臨時表

set @sql=&34;&39;&34;&39;&34;&39;&39;&39;&39;&34;&39;&34;&39;&39;&34;&39;&39;

exec master..xp_cmdshell @sql

--將數據導入到正式表中

set @sql=&39;+@tbname

+&39;+@fdname+&39;+@fdname

+&39;+@tbname+&39;

+@m_tbname+&39;

+case isnull(@tj,&39;) when &39; then &39;

else &39;+@tj end

exec(@sql)

--刪除數據處理臨時表

set @sql=&39;+@m_tbname

end

--刪除數據處理應答文件

set @sql=&39;+@fname_in

exec master..xp_cmdshell @sql

go

/** 導入文本文件

EXEC master..xp_cmdshell &34;dbname..tablename&39;

改為如下，不需引號

EXEC master..xp_cmdshell &39;

/** 導出文本文件

EXEC master..xp_cmdshell &34;dbname..tablename&39;

此句需加引號

相關焦點

  • SQL語句大全
    一、基礎1、說明：創建資料庫CREATE DATABASE database-name2、說明：刪除資料庫drop database dbname3、說明：備份sql server9、說明：創建視圖：create view viewname as select statement 刪除視圖：drop view viewname10、說明：幾個簡單的基本的sql語句選擇：select *
  • 經典SQL語句大全
    end int @sql nvarchar(600) set @sql=’select top』+str(@end-@start+1)+』+from T where rid not in(select top』+str(@str-1)+』Rid from T where Rid>-1)』 exec sp_executesql @sql 1234567注意：在top後不能直接跟一個變量，所以在實際應用中只有這樣的進行特殊的處理
  • SQL語句大全，所有的SQL都在這裡
    9、說明：創建視圖：create view viewname as select statement刪除視圖：drop view viewname10、說明：幾個簡單的基本的sql語句選擇：select * from
  • SQL Server 命令
    sqlcmd -S localhost\sqlserver_name連接資料庫sqlcmd -S localhost\sqlserver_name -d database_name執行SQL語句ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL; GO設置SQL server實例運行內存，以設置4G為例sp_configure &39;, 1;GORECONFIGURE;GOsp_configure &39;, 4096
  • 15000字的SQL語句大全
    9、說明：創建視圖：create view viewname as select statement刪除視圖：drop view viewname10、說明：幾個簡單的基本的sql語句選擇：select * from
  • 如何把SQL Server的資料庫導為sql文件
    前言在學習資料庫這門課程的過程中，被資料庫的導入導出這個問題給困擾了很多次。在SQL Server軟體中導入導出資料庫的方法很多種，但是很多是比較複雜麻煩的，甚至報錯了從網上也不方便找到解決方法。這使三橋君平時寧願直接編譯代碼實現項目，也不願去折騰導入導出了。
  • SQL SERVER 2014如何跟蹤程序執行哪些SQL語句
    SQL SERVER 2014如何跟蹤程序執行哪些SQL語句？SQL SERVER 2014自帶有SQL SERVER Profiler工具（tools>sql server profiler，如圖1)，我們利用這個工具就可以達到我們想要的效果：
  • 15000 字的 SQL 語句大全
    一、基礎1、說明：創建資料庫CREATE DATABASE database-name2、說明：刪除資料庫drop database dbname3、說明：備份sql server9、說明：創建視圖：create view viewname as select statement刪除視圖：drop view viewname10、說明：幾個簡單的基本的sql語句
  • SQL語句大全，趕快收藏吧
    9、說明：創建視圖：create view viewname as select statement 刪除視圖：drop view viewname10、說明：幾個簡單的基本的sql語句選擇：select
  • MySQL導入導出
    導出、導入表中的記錄語句：（1）從DB中導出到文件mysql>select * into outfile '/home/outfilename.txt' fromdbtest;（2）從文件導入到DBmysql>load data infile '/home/outfilename.txt ' into table
  • SQL Server數據的導入導出
    SQL Server 2008的導入導出服務可以實現不同類型的資料庫系統的數據轉換。為了讓用戶可以更直觀的使用導入導出服務，微軟提供了導入導出嚮導。導入和導出嚮導提供了一種從源向目標複製數據的最簡便的方法，可以在多種常用數據格式之間轉換數據，還可以創建目標資料庫和插入表。
  • SQL Server命令行導數據的2種方式
    Filed under 資料庫技術 Leave a comment SQL Server命令行導數據兩種方式bcp和sqlcmd 先說一下bcp: BCP是SQL Server中負責導入導出數據的一個命令行工具，它是基於DB-Library的，並且能以並行的方式高效地導入導出大批量的數據
  • MySQL導入導出常用命令
    導出、導入表中的記錄語句：（1）從DB中導出到文件mysql>select * into outfile '/home/outfilename.txt' fromdbtest;（2）從文件導入到DBmysql>load data
  • mysql資料庫導入和導出
    以下的文章主要介紹的是MySQL導入sql 文件，即MySQL資料庫導入導出sql 文件的實際操作步驟，我們主要是將其分成5大步驟對其進行講述。MySQL->SHOW TABLES; 顯示錶信息，有那些可用的表MySQL->DESCRIBE tablename; 顯示創建的表的信息三.從資料庫導出資料庫文件：1.將資料庫mydb導出到e:\MySQL\mydb.sql文件中：打開開始->運行->輸入cmd 進入命令行模式
  • 運維程式設計師:運用SQL Server Profiler監視查看自己寫的sql語句
    SQL Server Profiler的中文意思是SQL Server事件探查，一個Sql的監視工具，可以具體到每一行Sql語句，每一次操作，和每一次的連接。為什麼要使用SQL Server Profiler?
  • Shell應用：批量導入SQL文件，你還在複製粘貼文件名？
    背景從一個資料庫中導出了 N 個表的 SQL 語句，匯總到一個文件夾下。然後，再導入到目標資料庫中。，得到目錄下所有 SQL 文件的名稱，循環執行導入語句。其實也就選擇地導出了 8 張表的 SQL 語句，逐個敲文件名也不是個事兒，這也是最簡單的處理方法，但是這不符合好開發解決問題的風格啊，就是一個遍歷文件名的命令，網上沒找到現成的腳本，就自己寫一個！
  • 最常用的SQL語句大全來了，快收藏起來吧
    一、基礎1、說明：創建資料庫CREATE DATABASE database-name2、說明：刪除資料庫drop database dbname3、說明：備份sql server9、說明：創建視圖：create view viewname as select statement刪除視圖：drop view viewname10、說明：幾個簡單的基本的sql語句
  • Shell應用:批量導入SQL文件,你還在複製粘貼文件名?
    Shell應用：批量導入SQL文件，你還在複製粘貼文件名？ 從一個資料庫中導出了 N 個表的 SQL 語句，匯總到一個文件夾下。然後，再導入到目標資料庫中。這個過程中，如果逐個敲 source 命令太繁瑣了，不如寫個腳本來搞定、順便練練手。本文將介紹一個遍歷文件夾下文件的 Shell 應用。
  • 最強解讀MyBatis是如何執行SQL語句的?
    MyBatis 如何獲取 sql 語句？MyBatis 如何執行 sql 語句？MyBatis 如何實現不同類型數據之間的轉換？在過去程式設計師使用JDBC連接資料庫，總會帶來諸多不便。MyBatis 如何獲取 sql 語句？ 與獲取資料庫源類似，只要解析Mapper配置文件中的對應標籤，就可以獲得對應的sql語句。
  • 實戰手記:讓百萬級數據瞬間導入SQL Server
    想必每個DBA都喜歡挑戰數據導入時間，用時越短工作效率越高，也充分的能夠證明自己的實力。實際工作中有時候需要把大量數據導入資料庫，然後用於各種程序計算，本文將向大家推薦一個挑戰4秒極限讓百萬級數據瞬間導入SQL Server實驗案例。