9

Coding4Fun - 將 SSMS 產生 INSERT/UPDATE Script 轉成程式應用格式

 1 year ago
source link: https://blog.darkthread.net/blog/ssms-sql-transformer/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

將 SSMS 產生 INSERT/UPDATE Script 轉成程式應用格式-黑暗執行緒

SSMS 有個好用功能，在資料表按右鍵能自動產生 INSERT/UPDATE 用的 SQL 指令範例：

Fig1_638229975836900826.png

不過呢，轉換結果原本是提供在 SSMS 中修改後執行，有點過度貼心跟囉嗦：

INSERT INTO [HumanResources].[Employee]
           ([BusinessEntityID]
           ,[NationalIDNumber]
           ,[LoginID]
           ,[OrganizationNode]
           ,[JobTitle]
           ,[BirthDate]
           ,[MaritalStatus]
           ,[Gender]
           ,[HireDate]
           ,[SalariedFlag]
           ,[VacationHours]
           ,[SickLeaveHours]
           ,[CurrentFlag]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           (<BusinessEntityID, int,>
           ,<NationalIDNumber, nvarchar(15),>
           ,<LoginID, nvarchar(256),>
           ,<OrganizationNode, hierarchyid,>
           ,<JobTitle, nvarchar(50),>
           ,<BirthDate, date,>
           ,<MaritalStatus, nchar(1),>
           ,<Gender, nchar(1),>
           ,<HireDate, date,>
           ,<SalariedFlag, [dbo].[Flag],>
           ,<VacationHours, smallint,>
           ,<SickLeaveHours, smallint,>
           ,<CurrentFlag, [dbo].[Flag],>
           ,<rowguid, uniqueidentifier,>
           ,<ModifiedDate, datetime,>)

資料表、欄位名稱都有加上 [ ] 防止名稱含空白字元被曲解(實務上很少人命名硬加空白自找麻煩吧？)，VALUES 部分用欄位名稱加逗號附上型別，讓你知道要給數字、字串還是日期。

我有時會用它產生 SqlCommand 或 Dapper 要用的指令，此時就需要一些加工：移掉 [ ] 及換行讓指令精簡一點，將 VALUES 部分換成 @BusinessEntityID, @NationalIDNumber... 的參數列表形式，整理成類以以下結果：

INSERT INTO HumanResources.Employee
           (BusinessEntityID, NationalIDNumber, LoginID, OrganizationNode, JobTitle, BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate)
     VALUES
           (@BusinessEntityID, @NationalIDNumber, @LoginID, @OrganizationNode, @JobTitle, @BirthDate, @MaritalStatus, @Gender, @HireDate, @SalariedFlag, @VacationHours, @SickLeaveHours, @CurrentFlag, @rowguid, @ModifiedDate)

做這種沒營養的手工實在沒意義，那就寫幾行程式自動化吧!

我計劃做成網頁放在線上方便使用，而程式碼超級簡單，在 textarea 貼上 SSMS 產生的 INSERT/UPDATE 指令，按鈕後取值經過三次 RegExp 轉換，順利達成目的：

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title>SSMS SQL Script Transformer</title>
  <style>
    textarea,button { display: block; margin-bottom: 3px; }
    textarea { width: 640px; height: 480px; }
  </style>
</head>
<body>
  <button onclick="convert()">Convert</button>
  <textarea id=raw></textarea>
  <pre id=res>
  </pre>
  <script>
    function convert() {
      const result = 
            raw.value.replace(/\[([^\]]+)\]/g, '$1')
              .replace(/<([^,]+),[^>]+>/g, '@$1')
              .replace(/\n\s+,/g, ', ');
      res.textContent = result;
    }
  </script>

</body>
</html>

線上展示

Fig2_638229975838766344.png

寫程式提升工作效率再 +1。

and has 1 comment

Comments

Post a comment

Comment
Name Captcha 93 - 87 =

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK