Dunk

    SQL練習20110919
	參考網址 http://kb.cnblogs.com/page/106421/ */
/* 建立測試資料 */
use tempdb

CREATE TABLE [dbo].[Customer](
	[customer_no] [varchar](8) COLLATE Chinese_Taiwan_Stroke_BIN NOT NULL,
	[name] [varchar](60) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
	[address] [varchar](100) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
	[tel] [varchar](10) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
	[crc] [varchar](4) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
	[empid] [varchar](6) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
	[brno] [varchar](4) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[customer_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--輸入測試資料
insert into Customer
select '1','a1','b1','c1','d1','e1','f1' union
select '2','a2','b2','c2','d2','e2','f2' union
select '3','a3','b3','c3','d3','e3','f3' union
select '4','a4','b4','c4','d4','e4','f4'

select * from Customer
/* 建立測試資料完畢*/

/*以下為測試function*/
--使用select into寫入資料

select top 1 * into CustomerBackUp 
from Customer
select * from CustomerBackUp 


--將刪除的資料備份到table(delete output into)

delete Customer output deleted.*
into CustomerBackUp
where customer_no='2'
select * from Customer
select * from CustomerBackUp 

--多筆資料新增(insert into ... select ... union)
delete from CustomerBackUp
insert into CustomerBackUp
select '1','a','b','c','d','e','f' union
select '2','a','b','c','d','e','f' union
select '3','a','b','c','d','e','f' union
select '4','a','b','c','d','e','f'
select * from CustomerBackUp

--多筆資料新增(SQL Server 2008 以後適用)
delete from CustomerBackUp
insert into CustomerBackUp
insert into CustomerBackUp values
('1','a','b','c','d','e','f'),
('2','a','b','c','d','e','f'),
('3','a','b','c','d','e','f'),
('4','a','b','c','d','e','f')
select * from CustomerBackUp


--刪除表格
drop table Customer
drop table CustomerBackUp