do something

2018年5月7日星期一

[MS SQL]SQL Script Group by pivot

線上SQL Script 測試 http://sqlfiddle.com/

WITH tmp
AS
(SELECT
A.GROUP_ID
,C.GROUP_NAME
,B.USER_ID
FROM USER_GROUP_RELATION A
LEFT JOIN USER_PROFILE B
ON A.user_id = B.user_id
LEFT JOIN GROUP_SET C
ON A.GROUP_ID = C.GROUP_ID
)
SELECT
user_id
,SUM(CASE
WHEN group_id = 'CBMS001' THEN 1
ELSE 0
END) [GROUP001]
,SUM(CASE
WHEN group_id = 'GROUP002' THEN 1
ELSE 0
END) [GROUP002]
,COUNT(*) [ALL]
FROM tmp
GROUP BY user_id;

2018年4月20日星期五

[MS SQL] query後產出insert script

參考來源：https://stackoverflow.com/questions/4526461/converting-select-results-into-insert-script-sql-server

CREATE PROCEDURE dbo.ConvertQueryToInsert (@input NVARCHAR(max), @target NVARCHAR(max)) AS BEGIN

DECLARE @fields NVARCHAR(max);
DECLARE @select NVARCHAR(max);

-- Get the defintion from sys.columns and assemble a string with the fields/transformations for the dynamic query
SELECT
@fields = COALESCE(@fields + ', ', '') + '[' + name +']',
@select = COALESCE(@select + ', ', '') + ''''''' + ISNULL(CAST([' + name + '] AS NVARCHAR(max)), ''NULL'')+'''''''
FROM tempdb.sys.columns
WHERE [object_id] = OBJECT_ID(N'tempdb..'+@input);

-- Run the a dynamic query with the fields from @select into a new temp table
CREATE TABLE #ConvertQueryToInsertTemp (strings nvarchar(max))
DECLARE @stmt NVARCHAR(max) = 'INSERT INTO #ConvertQueryToInsertTemp SELECT '''+ @select + ''' AS [strings] FROM '+@input
exec sp_executesql @stmt

-- Output the final insert statement
SELECT 'INSERT INTO ' + @target + ' (' + @fields + ') VALUES (' + REPLACE(strings, '''NULL''', 'NULL') +')' FROM #ConvertQueryToInsertTemp

-- Clean up temp tables
DROP TABLE #ConvertQueryToInsertTemp
SET @stmt = 'DROP TABLE ' + @input
exec sp_executesql @stmt
END

-- Example table

-- Run query and procedure
SELECT * INTO #TempTableForConvert FROM TABLE_NAME WHERE CON_YEAR='2017';
EXEC dbo.ConvertQueryToInsert '#TempTableForConvert', 'apuser.TABLE_NAME '

2018年3月7日星期三

[旅遊筆記]詢問飯店可否代收範例

Dear Sir,
My name is ' 訂房姓名 '.
I booked a room through '訂房網站' from ' 住宿日期 ' to ' 退房日期 ' and the reservation number is ' 訂房編號 '.

I need help for additional service.
I was wondering could I have a parcel delivered to the hotel?

Thanks for help and look forward to hearing from you soon.
Best regards.

參考https://kikinote.net/139720

2018年1月16日星期二

連線資訊

https://www.connectionstrings.com/

2017年12月22日星期五

[MS SQL]停用/啟用 Foreign Key

-- Disable all table constraints

ALTER TABLE [table_name] NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE [table_name] WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE [table_name] NOCHECK CONSTRAINT [fk_name]

-- Enable single constraint

ALTER TABLE [table_name] WITH CHECK CHECK CONSTRAINT [fk_name]

https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql

2017年11月22日星期三

[MS SQL] loop

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
{...statements...}
SET @cnt = @cnt + 1;
END;

2017年11月1日星期三

[MS SQL]Patition

https://dotblogs.com.tw/jamesfu/archive/2012/12/25/partitiontable.aspx?fid=77758
https://dotblogs.com.tw/ricochen/2012/05/04/71971

https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-partition-scheme-transact-sql

-- 依據 Partition Function 建立 Partition Schema

create partition scheme psPartitionTest

as partition pfPartitionTest

ALL TO ([ PRIMARY ] );

--create partition function

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000);

--create partition scheme

CREATE PARTITION SCHEME myRangePS1

AS PARTITION myRangePF1

ALL TO ( [PRIMARY] );

--檢查是否建立成功

select * from sys.partition_functions;

select * from sys.partition_schemes;

-- 建立 Table 一開始就使用 Partition Schema

CREATE TABLE [apuser].[A](

[MOUDLE] [nvarchar](4) NOT NULL,

[SEQ] int NOT NULL,

[CONTENT] [nvarchar](100),

CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED

(

[MOUDLE] ASC,

[SEQ] ASC

)

) ON myRangePS1(SEQ);

--insert 測試資料