顯示具有 MSSQL 標籤的文章。 顯示所有文章
顯示具有 MSSQL 標籤的文章。 顯示所有文章

SQL Server move data by Copy Paste 秘密大公開!


也許是我下的標題太聳動,禁不起學長再次要求我要公開SQL Server可用Copy&Paste方式搬移資料...,說實在的以下的做法,很不值得讓我公開給大家知道,真是沒FU,讓每次搬移資料都還要下很精確的指令才能搬移的我...看到旁邊同事這樣的做法 真是ORZ...

當然我不確定如果資料型態或不符合會如何,還有當筆數如果是上千筆的時候 可能就不能這樣做了,我只是很不能夠忍受 把SQLServer當作Excel操作的人,每天都有同事問我 為什麼EXCEL可以,SQL這邊就不行? SQL真難用耶...要不就是"搬資料有這麼難嗎?" 現在我才知道他們原來是這樣操作的... (真想罵人....#$%#$%^#$)

看兩個完全不同欄位的資料表


選編輯前200筆資料....




選取你要的資料筆數,你可以像EXCEL般這樣的直覺


貼上.... |||



不合的欄位就用NULL替補....

寫到這,還是覺得很不削咧....

以上就是SQL C&P秘密大公開.

MSSQL內的RANK 用法範例

以下介紹兩種 MS-SQL的Rank用法:
有時候我們會遇到再同一個搜尋結果中要產生不同的排序欄位,也就是同一個select搜尋datarow中可以依需求新增兩個排序後的欄位。

如下所示:藍色字體按照Amount欄位產生,紅色字體除了以QtyKG排序,再以年度yy來區分(partition by..)。

select
'年度區域銷售' as fName
,rank() OVER ( ORDER BY Amount desc, yy) as rankAmount
,dense_rank() OVER ( PARTITION BY yy ORDER BY QtyKG desc) as rankQtyKG
,* from [mytable]
order by yy, area, QtyKG desc



這是從MS-SQL 2005版本以上的功能,排序問題在資料庫中變的相當簡單。
感謝MavisLiao資料提供。



SQL2008 IntelliSence 失效 不能出現自己的預存程序?

IntelliSence是SQL2008最大的特色,可以列出成員、快速諮詢、自動完成文字等功能,當系統越寫越龐大,很多的預存程序、函數的參數名稱都不容易記住,對於愛寫T-SQL的我,這是微軟遲來的功能。



該功能有個地方要注意,當你寫好一支預存程序或是函數,你會發現IntelliSence並不會自動提示你剛定義的東西,這並不是失效或有問題,而是需要去按下Ctrl+Shift+R,執行"重新整理本機快取"後,就可以顯示你剛定義的函數了。(我覺得Visual Studio都沒這樣的問題,感覺SQL2008相對變的比較笨,為什麼不會一次就做好?)

SQL 2008 安裝步驟

SQL2008 的安裝步驟,TechNet已經寫的很完整了,我只是再提醒該注意的地方:
需要準備
  1. Windows Installer 4.5
  2. Windows powerShell 1.0
  3. .net framework 3.5
  4. .net framework 3.5 sp1
可以從SQL2000、SQL2005直接昇級上去,或是要SQL2005、SQL2008各自分開獨立的執行個體也可以,大致上安裝其實都差不多,不過時間需要長一點。通常第一次安裝的時候會發現沒有裝好執行個體,所以看不到SSMS(SQL Server Management Studio),可以移除掉再重新設定執行個體(instance)後就可以正常顯示。

SQL Server 定序衝突 無法解析equal to 作業中"Chinese_Taiwan_Stroke_CI_AS"

我同事阿梅今天下SQL出現這樣的情況:

無法解析equal to 作業中"Latin1_General_CI_AI" 與"Chinese_Taiwan_Stroke_CI_AS" 之間的定序衝突

解法很簡單只要將定序不統一的欄位用COLLAPE轉換就可以了:假使你資料庫的定序是"Chinese_Taiwan_Stroke_CI_AS",則只要不是該定序的全把它轉回來就好了。

範例如下:

select *
from tmpa as a left join tmpb as b
on (a.entrid COLLATE Chinese_Taiwan_Stroke_CI_AS)=b.empyno
left join tmpb as c
on (a.updaid COLLATE Chinese_Taiwan_Stroke_CI_AS)=c.empyno
order by a.fundcd

在MSSQL內快速產生insert、update 的指令碼的好用自訂工具

我自己寫了個T-SQL指令,可讓我在MSSQL內在查詢視窗內以指令方式,輕易掌握整體資料庫的內容,也可以產生常用的INSERT、UPDATE、SELECT等常用指令。讓我先來簡單DEMO一下操作過程。


請複製以下的程式碼


create table [dbo].[tablename](
[class] [nvarchar] (200) null
,[TableName] [nvarchar] (400) not null
,[Trad] [nvarchar] (400) null
,[FieldCount] [int] null
,[DataCount] [int] null
,[note] [nvarchar] (800) null
,[InsertDate] [datetime] null
,[UpdateDate] [datetime] null)
GO

create table [dbo].[tablename_bak](
[class] [nvarchar] (200) null
,[TableName] [nvarchar] (400) not null
,[Trad] [nvarchar] (400) null
,[FieldCount] [int] null
,[DataCount] [int] null
,[note] [nvarchar] (800) null
,[InsertDate] [datetime] null
,[UpdateDate] [datetime] null)
GO

create table [dbo].[tabledesc](
[id] [int] not null
,[TableName] [nvarchar] (800) not null
,[Trad] [nvarchar] (200) null
,[Eng] [nvarchar] (2000) not null
,[DataType] [nchar] (80) null
,[Length] [int] null
,[xprec] [int] null
,[xscale] [int] null
,[IsNullable] [int] null
,[GVReadOnly] [nvarchar] (40) null
,[GVisible] [nvarchar] (40) null
,[GVtxtLen] [int] null
,[DVsys] [char] (3) null
,[Control] [nchar] (40) null
,[identiy] [int] null
,[tips] [nvarchar] (800) null)
GO

create table [dbo].[tabledesc_bak](
[id] [int] not null
,[TableName] [nvarchar] (800) not null
,[Trad] [nvarchar] (200) null
,[Eng] [nvarchar] (2000) not null
,[DataType] [nchar] (80) null
,[Length] [int] null
,[xprec] [int] null
,[xscale] [int] null
,[IsNullable] [int] null
,[GVReadOnly] [nvarchar] (40) null
,[GVisible] [nvarchar] (40) null
,[GVtxtLen] [int] null
,[DVsys] [char] (3) null
,[Control] [nchar] (40) null
,[identiy] [int] null
,[tips] [nvarchar] (800) null)
GO

sp_dbcmptlevel wg,80 GO

create proc myfn(
@order as char(1) = null
)
as
if @order is null
begin
select 'sp_helptext '+name as '自訂function:'
,'dbo.'+name+'()'
,crdate
from [sysobjects] where xtype in ('fn')
and substring (name,1,2) not in ('SP','DT')
and substring (name,1,6) not in ('ASPNET')
order by 1
end
else
select 'sp_helptext '+name as '自訂function:'
,'dbo.'+name+'()'
,crdate
from [sysobjects] where xtype in ('fn')
and substring (name,1,2) not in ('SP','DT')
and substring (name,1,6) not in ('ASPNET')
order by case when @order=1 then name else crdate end desc
--end
GO

create function fn_myInsert(
@tablename as nvarchar(50),
@variable as char(1)
)
returns varchar(3000)
as
begin
--cursor 逐一記錄每張卡號
declare cardlist cursor for
select b.name from [sysobjects] a, [syscolumns] b
where a.id=b.id and a.name=@tablename

open cardlist; --開啟Cursor

declare @i as int;
declare @cardno as varchar(60);
declare @cardnos as varchar(3000); set @cardnos='';--累積值
fetch next from cardlist into @cardno;

while (@@fetch_status=0) --0表示有資料。
begin
if(@variable<>'@') set @cardnos=@cardnos+@cardno+','
if(@variable='@')
if(lower(@cardno) not in ('insertdate','updatetime'))
begin set @cardnos=@cardnos+'@'+@cardno+',' end
else
begin set @cardnos=@cardnos+'getdate(),' end

fetch next from cardlist into @cardno;
end

set @cardnos=substring(@cardnos,1,len(@cardnos)-1)

close cardlist; --關閉Cursor與資料表的連結
deallocate cardlist; --移除Cursor
return @cardnos;
end
--end
GO

create proc myInsert(
@tablename as varchar(100)
)
as
select ' insert into ['+@tablename+']' as '請複製該段SQL碼,加快開發速度,減少打錯字因素'
union select '('+dbo.fn_myInsert(@tablename,'')+') '
union select 'values ('+dbo.fn_myInsert(@tablename,'@')+')'
--end
GO

create proc mysp(
@order as char(1) = null
)
as
if @order is null
begin
select 'sp_helptext '+name as '自訂預存程序名稱:---------', crdate
from sysobjects
where xtype in ('P')
and substring (name,1,2) not in ('SP','DT')
--and substring (name,1,6) not in ('ASPNET')
order by 1
end
else
select 'sp_helptext '+name as '自訂預存程序名稱:---------', crdate
from sysobjects
where xtype in ('P')
and substring (name,1,2) not in ('SP','DT')
--and substring (name,1,6) not in ('ASPNET')
order by case when @order=1 then name else crdate end desc
--end
GO

create proc mytable(
@TableName22 as varchar(100) =null
)
as
declare @maxid as int;
select @maxid=max(colorder) from syscolumns a ,sysobjects c
where a.id=c.id and c.name=@TableName22

if @TableName22 is not null
begin
exec sp_spaceused @TableName22;
SET QUOTED_IDENTIFIER ON;
select
sn=0,欄位='',型態='',長度=0,精確值='',建立日期='',
'更新用'='update ['+@TableName22 +'] set ',
'更新用2'='update ['+@TableName22 +'] set ',
'逗號'='',
',@變數'='',
',[欄位]=@變數',
'Query'='select',
'SP'='create proc [](',
'CreateTable'='create table [dbo].['+@TableName22+'](',
'MYSQL'='create table '+@TableName22+' ('
union all
select
/*序號*/a.colorder,
/*欄位*/a.name,
/*型態*/b.name,
/*長度*/a.length,
/*精確*/case when b.name in ('int','decimal') then '('+convert(varchar(10), a.xprec)+','+convert(varchar(10),a.xscale)+')' else '' end,
/*建立日期*/convert(varchar(20),c.crdate,111),
/*更新用*/
'+'',['+a.name+']=''''''+'+
case
when b.name in ('float','money','smallint','decimal') then 'convert(varchar(20),IsNull(Rtrim('+a.name+'),0))+'
when b.name in ('datetime') then 'convert(varchar(20),IsNull('+a.name+',''''),120)+'
else 'IsNull(Rtrim('+a.name+'),'''')+'
end+'''''',
/*更新用2*/',['+a.name+']=@'+a.name,
/*逗號*/','+a.name,
/*@變數*/',@'+a.name,
/*[欄位]+@變數*/
'+'', ['+a.name+']=''+'+
case
when b.name in ('float','money','smallint','decimal') then 'convert(varchar(10),IsNull(@'+a.name+',0),111)'
when b.name in ('datetime') then 'convert(varchar(10),IsNull(@'+a.name+',''),111)'
else 'IsNull(@'+a.name+','''')'
end+'+'+'''''''',
/*select*/
case when a.colorder=1 then '' else ',' end +
case
when b.name in ('datetime','float','money') then 'convert(varchar(10),'+a.name+',111) as '+a.name
when b.name not in ('datetime','float','money') then a.name+' as '+a.name
end,
/*StoreProcedure*/
case when a.colorder=1 then '' else ',' end
+case
when b.name='decimal' then '@'+a.name+' as decimal '+'('+convert(varchar(10),a.xprec)+','+convert(varchar(10),a.xscale)+')'
when b.name in ('nvarchar','varchar','nchar','char') then '@'+a.name+' as '+b.name+' ('+convert(varchar(10),a.length)+') '
when b.name in ('float','smallint','int','money','datetime') then '@'+a.name+' as '+b.name+''
end
+case when @maxid=a.colorder then ')' else '' end,
/*CreateTable*/
case when a.colorder=1 then '' else ',' end
+'['+a.name+'] ['+b.name+'] '
+case
when b.name='decimal' then '('+convert(varchar(10),a.xprec)+','+convert(varchar(10),a.xscale)+')'
when b.name in ('nvarchar','varchar','nchar','char') then '('+convert(varchar(10),a.length)+') '
when b.name in ('float','smallint','int','money','datetime') then ''
end+
+case when a.isnullable=0 then ' not null' else 'null' end
+case when @maxid=a.colorder then ')' else '' end,
/*MySQL*/
case when a.colorder=1 then '' else ',' end
+''+a.name+' '
+case
when b.name='decimal' then b.name+' ('+convert(varchar(10), a.xprec)+','+convert(varchar(10),a.xscale)+')'
when b.name in ('varchar','nvarchar') then 'varchar ('+convert(varchar(10), a.length)+') '
when b.name in ('char','nchar') then 'char ('+convert(varchar(10), a.length)+') '
when b.name in ('float','smallint','int','money','datetime') then b.name
end
+case when a.isnullable=0 then ' not null' else ' null' end
+case when @maxid=a.colorder then ')' else '' end
from [syscolumns] a ,[systypes] b, [sysobjects] c
where
a.id=c.id and a.xtype=b.xtype
and c.type='U' and b.name<>'sysname'
and c.name=@TableName22
order by 1;
end
else
select b.class as '資料表分類',a.name as '英文',b.trad as '中文',b.fieldCount as '欄位數目',crdate
from [sysobjects] a,[tablename] b
where a.type='U' and a.name=b.tablename order by 5 desc;
--end
GO

create proc mytable_update
as
delete from tabledesc_bak; --清空備份資料
insert into tabledesc_bak select * from tabledesc --先備份資料

delete from TableDesc; --清空實體資料庫
insert into TableDesc --從實體資料庫組合部分Tabledesc
select
id=b.colorder,
'資料表'=a.name,
'中文'='',
'英文'=b.name,
'型別'=c.name,
'長度'=b.length,
'整數'=b.xprec,
'小數'=isNull(b.scale,0),
'isNullable'=b.isnullable,
'GVReadOnly'=null,
'GVisible'=null,
'GVtxtLen'=null,
'DVSys'=null,
'Control'='txt',--預設
'identity'=b.colstat,
'tips'=null
from sysobjects a,syscolumns b,systypes c
where
a.id=b.id and b.xtype=c.xtype
and a.xtype='U'
and c.name<>'sysname'
order by 1
;
/* 從備份資料修改已存在欄位*/
update tabledesc
set
trad=b.trad,
GVReadOnly=isnull(b.GVReadOnly,'False'),
GVisible=b.GVisible,
GVtxtLen=b.GVtxtLen,
DVsys=b.DVsys,
control=isnull(b.control,'txt'),
tips=b.tips
from [tabledesc] a,[tabledesc_bak] b
where a.tablename=b.tablename
and a.eng=b.eng
;
/*沒有中文的部份預設英文*/
update tabledesc
set trad=eng
where trad='';

/*統計變更前後欄位差異數量*/
select
b.tablename ,
'變更前'=isNull(a.cc,0),
'變更後'=b.dd,
'欄差異量'=b.dd-isNull(a.cc,0)
from
(select tablename,count(*) as cc from tabledesc_bak group by tablename) as a,
(select tablename,count(*) as dd from tabledesc group by tablename) as b
where a.tablename=*b.tablename
order by 4 desc,1
;
/*TableName維護說明*/
delete from TableName_bak;--清除備份
insert into TableName_bak --備份資料
select *
from TableName --清除實體資料
;
delete from [TableName]
;
insert into [TableName]
select
null,TableName,null,count(*),null,null,null,null
from TableDesc
group by TableName
;
update a
set
class=b.class,
Trad=b.Trad, --補充中文說明
DataCount=c.rows, --補充資料筆數
InsertDate=c.crdate, --建立日期
UpdateDate=getdate() --變更資料日期
from
TableName a,
TableName_bak b,
(select
a.name as tablename,
max(b.rows) as rows,
max(a.crdate) as crdate
from sysobjects a,sysindexes b
where
a.id=b.id
and a.xtype='U'
group by a.name) as c
where
a.TableName=b.TableName
and a.TableName=c.TableName
;
--select '新報表',* from TableName where class is null
--select '舊報表',* from TableName where class is not null

--end
GO

create proc mytabledesc (
@tn as nvarchar(100)
)as
select
id,trad,eng,
rtrim(datatype)+'('+convert(varchar(10),length)+')' as datatype,isnullable
,gvreadonly ,gvisible,gvtxtlen,dvsys,control,identiy,tips
from tabledesc where tablename=@tn
--end
GO

--end



複製完了之後,先執行 exec update_tabledesc 指令,將所有必要的資料更新入暫存檔內,接下來就可在MSSQL內的查詢視窗內調出你需要的所有指令了。

exec mytable 'test'
exec myinsert 'test'
exec mytabledesc 'test'
exec mysp
exec myfn

範例一:假設你想查閱你的資料庫內有多少個資料表,各有多少欄位,你可以指令下列mytalbe指令,其中我擴充了資料表分類、中英文對照的欄位,預設就是NULL沒有。
exec mytable
--查詢結果-----------------------
資料表分類 英文 中文 欄位數目 產生日期
NULL tablename NULL 8 2008-10-01 13:37:20.420
NULL tabledesc NULL 16 2008-10-01 13:37:20.420
NULL issue NULL 7 2008-10-01 12:34:45.483
NULL issues NULL 5 2008-09-26 17:09:06.890
NULL billboard NULL 4 2008-09-18 10:05:22.950

自動產生唯一號碼

在MSSQL中可以產生唯一不重複的號碼:

例如:
select newid() --> 0EC2002D-B4B4-4290-8AB7-ECE1887A215E

1.因為該函數參考了時間及硬體代碼等所以絕對不會重複,用來當key很有效.
2.若要儲存該資料使用uniqueidentifier 資料型別儲存.

MSSQL中怎樣知道IDENTITY 的 next value 與如何更改?

知道下一個值如下...
use tablename;
SELECT IDENT_CURRENT('tablename') + IDENT_INCR('[tablename]');

要更改為從0開始:
DBCC CHECKIDENT (tablename, RESEED, 0);

如何將 MSSQL2005 資料轉到到 ORACLE 7.2

轉檔的方法有很多種,由於我是在MSSQL2005轉到ORACLE 7.2去的,所以SSMS執行過程不是很順利,我在網路上找到其他比較特殊的寫法來解決轉檔的問題,做法步驟如下:
  1. 將MSSQL中的「作業」中設定要在什麼時間進行哪些新增刪除修改的動作,該指令如下spool translog.txt;
    insert into temp (COD,KTYP,TYP,UNIT,UPRICE,SMUNIT,BUPRICE) values ('sdfasdf',....
    update stcktmp set KTYP='C',TYP=' ',UNIT='KG ',UPRICE=0,SMUNIT='NT',BUPRICE=0,...
    commit;
    exit;
  2. 第一行spool translog.txt; 將執行結果條列寫在文字檔中我們將該檔轉在「作業」中設定存放在某個目錄下,例如D:/result.sql
  3. 接著寫一個batch檔案設定oracle該去哪個目錄下去抓指令檔案並且執行該串列程式。Batch檔內容只寫這兩行就可以...設定資料庫名稱、密碼、帳號、執行哪個檔案
    sqlplus DBNAME/passwd@login @test2.sql
    exit
  4. 在WINDOWS中 開始/程式集/附屬應用程式/系統工具/排定的工作 中設定什麼時間去執行該BATCH檔案 這樣就完成了
當然第一步驟中的將所有指令排列整理好寫在某個TABLE內是我下一系列TSQL指令寫出來的,怎麼寫就下次在PO了.

MSSQL - DateTime 相關的format參數

以下MSSQL時間格式轉換為文字的常用參數,因為每次都記不起來乾脆就寫在這邊提供大家參考.

select convert(varchar(20),getdate(),StyleID)

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

StyleID StyleType
100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 or 20 yyyy-mm-dd hh:mi:ss(24h)
121 or 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM


MSSQL - DateDiff 的應用,相對時間差的表現法

GMAIL的時間表達方式我一項很喜歡,他會用比較直覺方式讓你知道該文件到底存在多久了.如果我們要寫這樣的效果,如果我們也同樣想在asp.net上寫出同樣的效果該怎樣寫才好咧? 我習慣將類似計算寫資料庫端的FUNCTION上,效能快,好維護且可套用在所有的查詢上.

我的寫法如下:
create function myDateDiff(
@dt as datetime
)
returns nvarchar(50)
as
begin
declare @result as nvarchar(50);
declare @g as datetime; set @g=getdate();
select @result=
case when datediff(dd,@dt,@g)>2 then convert(varchar(10),@dt,101) else
case when datediff(dd,@dt,@g)=2 then '前天' else '' end+
case when datediff(dd,@dt,@g)=1 then '昨天' else '' end+
case when datediff(hh,@dt,@g)>0 then convert(varchar(2),datediff(hh,@dt,@g)%24)+'小時又' else '' end+
case when datediff(mi,@dt,@g)>0 then convert(varchar(2),datediff(mi,@dt,@g)%60)+'分' else '' end+
case when datediff(ss,@dt,@g)>0 then convert(varchar(2),datediff(ss,@dt,@g)%60)+'秒前' else '' end
end
return @result
end
--end


我們可以SQL查詢畫面下執行,可以有下列幾種的表現方式:

select dbo.mydatediff('2008/3/25 11:17:55') -->35秒前
--------------------------------------------------
select dbo.mydatediff('2008/3/25 11:15:55') -->
2分鐘35秒前
--------------------------------------------------
select dbo.mydatediff('2008/3/24 11:15:55') -->昨天2分鐘35秒前
--------------------------------------------------
select dbo.mydatediff('2008/3/23 11:15:55') -->前天2分鐘35秒前
--------------------------------------------------
select dbo.mydatediff('2008/3/21 11:15:55') -->03/21/2008
--------------------------------------------------

其他的format方式就看自己怎樣做變化了....


清除或壓縮MSSQL LDF日誌資料,省磁碟空間必看!!

通常很多人在操作資料庫的過程中會忽略日誌檔案
資料庫在建立了之後往往會因為新增、刪除、修改資料而在log檔中留下紀錄
如果資料操作頻繁,佔用實體空間往往超出你想像,
當我硬碟儲存空間不夠的時候,該搬移的程式或檔案都移除掉後後來才想起了資料庫的log應該要檢查一下
你可以在(路徑因人而異,不過通常都找的到)
C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\test.dmf
C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\test.ldf

只要在查詢下輸入:
backup log test with no_log;
DBCC SHRINKFILE ('test_log',0); 檔案總管下看,.. 我原本test.ldf大約1.5GB,瞬間縮成1MB.
突然多了好多空間可使用,建議你試試看.