Jason 資料庫

使用 PIVOT 和 UNPIVOT

http://technet.microsoft.com/zh-tw/library/ms177410.aspx

DROP TABLE #pvt

CREATE TABLE #pvt (VendorID varchar(10), Emp1 int);
GO
INSERT INTO #pvt VALUES ('A121',4121);
INSERT INTO #pvt VALUES ('A121',4345);
INSERT INTO #pvt VALUES ('A121',4454);
INSERT INTO #pvt VALUES ('A122',1234);
INSERT INTO #pvt VALUES ('A122',1121);
INSERT INTO #pvt VALUES ('A123',5232);

select * from #pvt

SELECT *
FROM 
(SELECT VendorID,Emp1
	,ROW_NUMBER() OVER(partition by VendorID order by VendorID) RID
FROM #pvt) p
PIVOT
(
MAX(Emp1)
FOR RID IN --用RID對應要塞入的欄位
( [1], [2], [3])
) AS pvt1

輸出結果:

Jason SQL Server

[轉貼]將資料庫表格中的資料以 INSERT INTO 指令的方式匯出

原文網址：http://twg.idv.tw/dv_rss_xhtml_155_85551_2.html

通常我們從 SQL Server 匯出資料到另一台主機大致上有幾種方式：

  1. 使用 bcp.exe 匯出成純文字格式，在到另一台主機用 bcp.exe 匯入資料
  2. 使用 DTS (Data Transformation Services) 或 SSIS (SQL Server Integration Services) 將資料封裝成檔案，並帶到另一台 Server 匯入資料。
  3. 備份整個資料庫，將備份檔(*.bak)帶到另一台主機還原資料
  4. 卸載(Detach)資料庫，將資料庫檔(*.mdf 與 *.ldf)帶到另一台主機附加(Attach)資料庫

我以前常用 MySQL 資料庫，匯出資料庫的時候都是 DDL ( CREATE TABLE, … ), DML (INSERT INTO, … ) 的格式儲存，要移轉資料到其他主機都很方便。但改用 SQL Server 時這些東西都不見了，讓我困擾了好久，有時後為了轉移一些資料都要花費很大的力氣將資料轉移到遠端的主機，因為有些遠端的 SQL Server 是無法直接用 Management Studio 遠端連接，只能用 Web 介面管理時，就很很麻煩。

但我今天在這裡發現一個神奇的預儲程序(Stored Procedure)，作者是一位叫做 Narayana Vyas Kondreddi 的仁兄，光看名字實在看不出是哪一國人 :-p

他寫的 T-SQL 可以將表格中的資料全部都轉成 INSERT INTO 的形式，方便你將資料帶到另一台主機，只要執行 INSERT INTO 指令就可以將資料新增到另一台主機了。

這個預儲程序的優點有：

  • Data from both tables and views can be scripted
  • No CURSORs are used
  • Table names and column names with spaces are handled
  • All datatypes are handled except images, large text and binary columns with more than 4 bytes
  • NULLs are gracefully handled
  • Timestamp columns are handled
  • Identity columns are handled
  • Very flexible and configurable
  • Non-dbo owned tables are handled
  • Computed columns are handled
  • You can filter the rows for which you want to generate INSERTs

下載 Stored Procedure：

在原作者的網站有 14 個使用說明範例(Usage Example)，各位可以上去看看。執行完下載的 T-SQL Script 之後會在 master 資料庫中新增一個 dbo.sp_generate_inserts 預儲程序物件，如下圖示：

執行完下載的 T-SQL Script=

不過缺點是這個方法沒辦法適用於有文字欄位內容超過 8KB 的資料，因為輸出的結果是顯示在 SSMS ( SQL Server Management Studio ) 中，輸出的欄位長度是有限制的。

另外，這個預儲程序我有發現一個小問題，就是當執行完畢後，匯出的資料中如果內容中有「斷行符號」的話，全部都會被置換成「空白」字元，導致匯入的時候所有斷行字元都會被換成空白符號，資料就被破壞掉了。

我個人花了 2 個小時將這個 Script 研究過一遍並將問題給修正了（只改了兩行），用 T-SQL 產生 T-SQL 的程式碼真的會讓人眼花撩亂，一大堆單引號 Quote 來 Quote 去的，你自己看程式碼就知道了，我將更新過的 T-SQL Script 放在以下檔案中，我只有改寫 SQL 2005 的版本而已：

Jason ORACLE

ORACLE語法

  • 排名： 
    rank()over(partition by TABLE.COLUMN_1 order by count(distinct TABLE.COLUMN_2)desc)

    這是常需要用,但不太會用的語法

  • 取得TABLE及COLUMN的資訊： 
    select distinct table_name from all_tab_cols
where owner='XXX'
  • CREAT TABLE時加大空間： 
    STORAGE    (
INITIAL 512K
MINEXTENTS 1
MAXEXTENTS unlimited
)
  • Truncate_Partition：
    若資料有依年月切PARTITION分的,
    當要DELETE某年月的資料時,
    可採 TRUNCATE PARTITION的方式,可提高清資料的效率

    sql_stmt_01 := 'alter table DM_CSV.F_CSV_ACCT_HOLDINGS truncate partition ' || 'P_' || v_SNAP_YYYYMM;
EXECUTE IMMEDIATE sql_stmt_01;