透過 SQL 查詢 Oracle 樹狀結構某節點下的所有節點

• ORACLE 限定方法: START WITH ... CONNECT BY PRIOR ...
• Oracle ex:

SELECT Id
FROM Category START WITH Id=2 CONNECT BY
PRIOR Id=Parentid

透過 SQL 查詢 MSSQL 樹狀結構某節點下的所有節點

• MSSQL 似乎沒有直接的查詢語法(?), 但有 with
• MSSQL ex:

WITH n (id, name) AS
    (SELECT id, name
    FROM category
    WHERE id = 2 --父節點 id
    UNION ALL SELECT child.id, child.name
    FROM Category AS child, n
    WHERE n.id = child.parentId)

SELECT * FROM n

如果要透過 n 再查詢, 例如查詢分類樹底下所有 binder
(假設 Binder 和 Category 透過 Binder_Category 來記錄關聯性)

WITH n (id, name) AS (SELECT id, name FROM category WHERE id = 2 --父節點 id UNION ALL SELECT child.id, child.name FROM Category AS child, n WHERE n.id = child.parentId) SELECT * FROM Binder bb LEFT JOIN Binder_Category bc ON bc.binder_id = bb.id WHERE bc.cat_id IN (SELECT id FROM n)

(感謝 April 大大教學)

Sql Server add auto increment primary key to existing table

No - you have to do it the other way around: add it right from the get go as INT IDENTITY - it will be filled with identity values when you do this:

ALTER TABLE dbo.YourTable
  ADD ID INT IDENTITY

and then you can make it the primary key:

ALTER TABLE dbo.YourTable
  ADD CONSTRAINT PK_YourTable
  PRIMARY KEY(ID)

REF: http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table

匯入大檔, 錯誤大概都會長這樣...

原文告訴大家改用一般檔案來源, 且匯入時對應的欄位型態要修改!

以下整理自原文的建議:

1. 資料來源選擇: 一般檔案來源

2. 選好欲匯入的檔案，左側點選 進階

3. 將文字欄位的 DataType 改成 文字資料流[DT_TEXT]
   

4. 將整數欄位的 DataType 改成 二位元組不帶正負號的整數[DT_UI2]

圖片來源: http://lanfar.pixnet.net

REF: SQL Server：匯入資料遭遇“文字已截斷"錯誤 from 蘭花❉心得報告

這就是 Stored Procedures, 不過這似乎要有權限才能完成!

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

The following SQL will get you the row count of all tables in a database:

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC

The output will be a list of tables and their row counts.
If you just want the total row count across the whole database, appending:

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

REF:
Fetch_row_count_for_all_tables_in_a_SQL_SERVER by adrianbanks from stackoverflow.com