由于工作需要,需要對有自增列的表插入資料,帶自增列的表有很多,計劃使用查詢插入的方式來實作。
這種存在自增清單是無法直接插入資料的,需要設定IDENTITY_INSERT為ON。DM要求一個會話連接配接中隻有一個表的 IDENTITY_INSERT 屬性可以設定為 ON,當設定一個新的表IDENTITY_INSERT 屬性設定為ON時,之前已經設定為ON的表會自動還原為OFF。是以每次insert into一張表之前都要先執行一次set IDENTITY_INSERT on 的語句。
此外,insert into的語句必須要指明列名,例如:
insert into a select * from b;
上面這種方式是無法成功插入資料的,正确語句如下:
insert into a ("ID","NAME","EMAIL") select * from b;
當資料庫中有多張表時,則使用以下語句即可拼出所有的查詢插入語句
SELECT
' SET IDENTITY_INSERT "'
||B.NAME
||'" ON; INSERT INTO "'
||B.NAME
||'"("'
||LISTAGG(A.NAME, '","') WITHIN GROUP (
ORDER BY
A.COLID)
||'") SELECT * FROM "'
||B.NAME
||'";'
FROM
SYSCOLUMNS A
INNER JOIN SYSOBJECTS B
ON
A.ID=B.ID
WHERE
B.NAME IN
(
SELECT DISTINCT
S.NAME
FROM
SYSOBJECTS S,
SYSCOLUMNS C
WHERE
S.ID =C.ID
AND C.INFO2 =1
AND S.NAME IN
(
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT LIKE 'CTI%'
)
)
AND B.SCHID=
(
SELECT ID FROM SYSOBJECTS WHERE TYPE$='SCH' AND NAME='SYSDBA'
)
GROUP BY
B.NAME;