albert's blog: Microsoft SQL Server

Showing posts with label Microsoft SQL Server. Show all posts

2018/11/10

[SchemaSpy] How to generate SQL Server schema document via SchemaSpy

Steps
1. Download SchemaSpy jar from http://schemaspy.org/
2. Downnload and install Graphviz from https://www.graphviz.org/
3. Download JDBC jar file for your target database
4. Execute command as bellows:

java -jar "F:\lib\schemaspy-6.0.0-rc2.jar" -dp "F:\sqljdbc_6.0\sqljdbc42.jar" -t mssql05 -db MyDB -host 192.168.0.1 -port 1433 -u user -p secret -charset UTF-8 -o "F:\Schema" -hq

Commonly used parameters

-jar: The location of schemaspy jar file
-dp: NLooks for drivers here before looking in driverPath in [databaseType].properties. The drivers are usually contained in .jar or .zip files and are typically provided by your database vendor.
-t: Type of database (e.g. ora, db2, etc.)
-db: Name of database to connect to
-host: Databas URL to connect to
-port: Database port
-u: Valid database user id with read access.
-p: Valid password
-charset: Assign character set
-o: Directory to write the generated HTML/graphs to
-hq: Generate either higher-quality diagrams.

2018/11/09

[SQL Server] Utilize FOR XML PATH to merge multiple rows into one row

Problem
Assume I get two rows from the following SQL statement:

select t1.CASEID, t1.PID, t1.NAME, t2.QVALUE + CHAR(10) + t2.AVALUE as QA
from TEXT_MAIN t1, TEXT_QA t2
where t1.CASEID = 'W00000003944' and t1.RID = t2.RID

If I would like to merge the two rows into one row:

How-To
You can use FOR XML PATH to fulfill this requirement, here has the example

select t1.CASEID, t1.PID, t1.NAME,
    (select t2.QVALUE + CHAR(10) + t2.AVALUE + CHAR(10)
     from TEXT_QA t2
     where t1.RID = t2.RID
     FOR XML PATH('')) as QA
from TEXT_MAIN t1
where t1.CASEID = 'W00000003944'

2017/10/08

[Oracle to SQL Server Migration] NVL

Problem
In Oracle, NVL function lets you replace null (returned as a blank) with a string in the results of a query.

If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

The SQL statement is as follows:

SELECT DRAWING_ID,
       NVL(TARGET_INSFEE, 0)
FROM CAM_DRAWING

Owing to NVL is only for Oracle, but it cannot be executed in Microsoft SQL Server.

How-To
You can use ISNULL instead of NVL in Microsoft SQL Server.
The SQL statement is as bellows:

SELECT DRAWING_ID,
       ISNULL(TARGET_INSFEE, 0)
FROM CAM_DRAWING

2017/08/07

[SQL Server] How to find similar table / column name by SQL

I am using Microsoft SQL Server...

If I would like to get a list of table name which table name has %arap%, you can use this SQL statement to do query:

SELECT NAME 
FROM   sys.tables 
WHERE  NAME LIKE '%arap%'

If I would like to get a list of table which has column name %apply%, you can use this SQL statement to do query:

SELECT t.NAME AS 'TableName', 
       c.NAME AS 'ColumnName' 
FROM   sys.columns c 
       JOIN sys.tables t 
         ON c.object_id = t.object_id 
WHERE  c.NAME LIKE '%apply%' 
ORDER  BY t.NAME, 
          c.NAME;

2016/05/08

How to Get All Sequences in SQL Server

Problem
If I would like to get all sequences in Microsoft SQL Server, how to do it?

How-To
You can use this SQL statement to retrieve all sequences information as follows:

1	SELECT * FROM SYS.SEQUENCES;

Reference
[1] http://stackoverflow.com/questions/19035941/how-can-i-determine-if-a-sequence-exist-in-sql-server-2012

2016/02/05

[Microsoft SQL Server] Adding Rows by Using INSERT and SELECT

Problem
Assume I have a function, as user click a button it will copy last year's data and insert data into this year. How to use SQL statement to fulfill this requirement?

How To
We can use insert with a select subquery to implement this requirement
By using a SELECT subquery to specify the data values for one or more rows, such as:

INSERT INTO dbm031fa(sta_date, age, fund_id, fund_name, age_type, remark, user_id, update_date)
SELECT DATEADD(YEAR, 1, sta_date) sta_date,
       age,
       fund_id,
       fund_name,
       age_type,
       remark,
       user_id,
       CURRENT_TIMESTAMP update_date
FROM dbm031fa
WHERE year(sta_date) = year(CURRENT_TIMESTAMP)-2

Reference
[1] https://technet.microsoft.com/en-us/library/ms188263(v=sql.105).aspx

2016/02/04

[Microsoft SQL Server] How to subtract a year in SQL

Problem
I have a select SQL statement as bellows:

SELECT sta_date
FROM dbm031fa
WHERE year(sta_date) = year(CURRENT_TIMESTAMP)-2

If I would like to get the value of sta_date is the value in database subtract one year, how to do it?

How To
Microsoft SQL Server had a build-in function, DATEADD, to fulfill this requirements.
The SQL statement can be modified as following:

SELECT sta_date,
       DATEADD(YEAR, -1, sta_date)
FROM dbm031fa
WHERE year(sta_date) = year(CURRENT_TIMESTAMP)-2

Reference
[1] https://msdn.microsoft.com/zh-tw/library/ms186819(v=sql.120).aspx

2015/12/07

[Oracle to SQL Server Migration] MINUS

Problem
Here has a SQL statement which is working fine in Oracle:

select distinct fund_id
FROM dbm034fa

MINUS

select distinct fund_id
FROM dbm034fa
WHERE ( (mge_type='G' and (in_amt/out_amt)*100 >= 50) or
         (mge_type='F' and (in_amt/out_amt)*100 >= 20) )

But MINUS does not support in Microsoft SQL Server, how to translate it ?

How-To
You can utilize EXCEPT in Microsoft SQL Server instead.

select distinct fund_id
FROM dbm034fa

EXCEPT

select distinct fund_id
FROM dbm034fa
WHERE ( (mge_type='G' and (in_amt/out_amt)*100 >= 50) or
         (mge_type='F' and (in_amt/out_amt)*100 >= 20) )

Reference
[1] http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/

2015/12/02

How to utilize Oracle SQL Developer to Connect to Microsoft SQL Server

Problem
Oracle SQL Developer only support connection to Oracle and Access by default.
If I would like to connection to Microsoft SQL Server, how to configure it?

How-To
Step 1. Download jTDS - SQL Server and Sybase JDBC driver from http://sourceforge.net/projects/jtds/files/

Step 2. Tool => Preference => Third Party JDBC Driver. Set library file to Third Party JDBC Driver

Step 3. Click create new database connection. You can find out SQLServer option is the create new database connection dialog after forging configuration.

Step 4. Fill in the SQL Server database connection information and do test. It can connection to SQL Server database successfully via Oracle SQL Developer.

Reference
[1] https://www.dotblogs.com.tw/smartleos/archive/2013/09/16/118705.aspx

2015/11/16

[Oracle to SQL Server Migration] ROWNUM

Problem
We used rownum = 1 in Oracle as bellows:

If we migrate to Microsoft SQL Server, how to translate it?

SELECT to_char(STA_DATE, 'YYYY') - 1911 ACCOUNT_YR,
       age,
       fund_id,
       (select fund_name from dbm031fa where fund_id=dbm034fa.fund_id and rownum=1) fund_name,
       mge_type,
       IN_AMT,
       OUT_AMT,
       (IN_AMT/OUT_AMT)*100 RATIO
FROM dbm034fa
WHERE to_char(STA_DATE, 'YYYY') - 1911 BETWEEN '101' AND '103'
      AND age = '379000000A'
      AND fund_id='010401C00010'
      AND mge_type='G'

How-To
We can use TOP N to fulfill this requirement.
The SQL statement will be amended as following:

SELECT YEAR(STA_DATE) - 1911 ACCOUNT_YR,
       age,
       fund_id,
       (select TOP 1 fund_name from dbm031fa where fund_id=dbm034fa.fund_id) fund_name,
       mge_type,
       IN_AMT,
       OUT_AMT,
       (IN_AMT/OUT_AMT)*100 RATIO
FROM dbm034fa
WHERE YEAR(STA_DATE) - 1911 BETWEEN '101' AND '103'
      AND age = '379000000A'
      AND fund_id='010401C00010'
      AND mge_type='G'

Reference
[1] http://www.w3schools.com/sql/sql_top.asp
[2] https://msdn.microsoft.com/zh-tw/library/ms189463(v=sql.120).aspx

[SQL Server] Year function

Problem
Here is my table schema in Microsoft SQL Server. If I would like to retrieve an integer that represents the year of the STA_DATE (with datetime data type).

How-To
We can use YEAR function, Microsoft SQL Server build-in function, to fulfill this requirement.

SELECT sta_date,
       year(sta_date) YEAR
FROM dbm034fa

Check the result :

Reference
[1] https://msdn.microsoft.com/en-us/library/ms186313.aspx

2015/10/12

[Oracle to SQL Server Migration] Outer Join

In Oracle, we use outer join, (+), in the SQL statement as bellows:

SELECT TO_CHAR(A.STA_DATE,'YYYYMMDD') AS STA_DATE,
       A.AGE,
       A.FUND_ID,
       A.MGE_TYPE,
       F_DBM_FIND_LAGE_NM(A.STA_DATE,A.AGE) AGE_NAME,
       F_DBM_FIND_FUND_NM(A.STA_DATE,A.AGE,A.FUND_ID) FUND_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE0,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2,
       (E.IN_AMT/E.OUT_AMT) * 100 AS RATE3
FROM DBM034FA A,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1910) C,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1909) D,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1908) E
WHERE TO_CHAR(A.STA_DATE,'YYYY')=:accountYr +1911
  AND A.AGE = C.AGE(+)
  AND A.FUND_ID = C.FUND_ID(+)
  AND A.MGE_TYPE=C.MGE_TYPE(+)
  AND A.AGE = D.AGE(+)
  AND A.FUND_ID = D.FUND_ID(+)
  AND A.MGE_TYPE=D.MGE_TYPE(+)
  AND A.AGE = E.AGE(+)
  AND A.FUND_ID = E.FUND_ID(+)
  AND A.MGE_TYPE=E.MGE_TYPE(+)

Owing to (+) is the specific approach to do outer join in Oracle, it does not work in SQL Server.
Hence, the foregoing SQL statement should be translated as following:

select t.STA_DATE as STA_DATE, 
       t.AGE, 
       t.FUND_ID, 
       t.MGE_TYPE, 
       t.AGE_NAME, 
       t.FUND_NAME, 
       t.RATE0, 
       t.RATE1, 
       t.RATE2, 
       t.RATE3
from
(
SELECT CONVERT(VARCHAR(4), A.STA_DATE, 112),
       A.AGE,
       A.FUND_ID,
       A.MGE_TYPE,
       AP_NTA.F_DBM_FIND_LAGE_NM(A.STA_DATE,A.AGE) AGE_NAME,
       AP_NTA.F_DBM_FIND_FUND_NM(A.STA_DATE,A.AGE,A.FUND_ID) FUND_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE0,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2,
       (E.IN_AMT/E.OUT_AMT) * 100 AS RATE3
FROM DBM034FA A

LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112)=:accountYr +1910) C
ON    
  A.AGE = C.AGE
  AND A.FUND_ID = C.FUND_ID
  AND A.MGE_TYPE=C.MGE_TYPE

LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112)=:accountYr +1909) D
ON
  A.AGE = D.AGE
  AND A.FUND_ID = D.FUND_ID
  AND A.MGE_TYPE=D.MGE_TYPE

LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112)=:accountYr +1908) E
ON   
  A.AGE = E.AGE
  AND A.FUND_ID = E.FUND_ID
  AND A.MGE_TYPE=E.MGE_TYPE
 ) t
 where t.STA_DATE=:accountYr +1911

2015/08/03