FTP連線bat

@echo off

::FTP連線資訊
set server=10.10.10.10
set username=FTPUSER
set password=FTPPWD

::FTP連線資訊儲存檔名稱
set ftp_connect=ftp_connect.txt

::取得當日時間
set YYYY=%date:~0,4%
set MM=%date:~5,2%
set DD=%date:~8,2%

::檔案格式
::set FileFolder=%YYYY%-%MM%
::set File=%YYYY%-%MM%-%DD%.txt

::來源/目的
::set source=來源路徑(%FileFolder%/%File%)
::set purpose=目標路徑

:Filestart
echo open %server%>>%ftp_connect%
echo user %username%>>%ftp_connect%
echo %password%>>%ftp_connect%
echo prompt>>%ftp_connect%
::echo cd %purpose%>>%ftp_connect%
::echo mput %source%>>%ftp_connect%
::echo bye>>%ftp_connect%

::執行FTP連線
ftp -s:%ftp_connect% -n

::刪除FTP連線資訊儲存檔
del %ftp_connect% /Q

[MS SQL]不足左補0

select RIGHT('0000000000' + CAST(123 as VARCHAR),10) ;

select REPLICATE('0', (10-LEN('123')))+'123';

select RIGHT(REPLICATE('0',10) + CAST(123 as VARCHAR),10);

PS. Oracle 用 LPAD/RPAD即可完成

[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;

[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 '

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

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

連線資訊

https://www.connectionstrings.com/

[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