T-SQL to PL/SQL (IDENTITY)
- by folone
I've got a T-SQL script, that converts field to IDENTITY (in a weird way).
How do I convert it to PL/SQL? (and, probably, figure out, if there is a simpler way to do this - without creating a temporary table).
The T-SQL script:
-- alter table ts_changes add TS_THREADID VARCHAR(100) NULL;
-- Change Field TS_ID TS_NOTIFICATIONEVENTS to IDENTITY
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TS_NOTIFICATIONEVENTS
(
TS_ID int NOT NULL IDENTITY (1, 1),
TS_TABLEID int NOT NULL,
TS_CASEID int NULL,
TS_WORKFLOWID int NULL,
TS_NOTIFICATIONID int NULL,
TS_PRIORITY int NULL,
TS_STARTDATE int NULL,
TS_TIME int NULL,
TS_WAITSTATUS int NULL,
TS_RECIPIENTID int NULL,
TS_LASTCHANGEDATE int NULL,
TS_ELAPSEDCYCLES int NULL
) ON [PRIMARY]
SET IDENTITY_INSERT dbo.Tmp_TS_NOTIFICATIONEVENTS ON
GO
IF EXISTS(SELECT * FROM dbo.TS_NOTIFICATIONEVENTS)
EXEC('INSERT INTO dbo.Tmp_TS_NOTIFICATIONEVENTS (TS_ID, TS_TABLEID, TS_CASEID, TS_WORKFLOWID, TS_NOTIFICATIONID, TS_PRIORITY, TS_STARTDATE, TS_TIME, TS_WAITSTATUS, TS_RECIPIENTID, TS_LASTCHANGEDATE, TS_ELAPSEDCYCLES)
SELECT TS_ID, TS_TABLEID, TS_CASEID, TS_WORKFLOWID, TS_NOTIFICATIONID, TS_PRIORITY, TS_STARTDATE, TS_TIME, TS_WAITSTATUS, TS_RECIPIENTID, TS_LASTCHANGEDATE, TS_ELAPSEDCYCLES FROM dbo.TS_NOTIFICATIONEVENTS WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TS_NOTIFICATIONEVENTS OFF
GO
DROP TABLE dbo.TS_NOTIFICATIONEVENTS
GO
EXECUTE sp_rename N'dbo.Tmp_TS_NOTIFICATIONEVENTS', N'TS_NOTIFICATIONEVENTS', 'OBJECT'
GO
ALTER TABLE dbo.TS_NOTIFICATIONEVENTS ADD CONSTRAINT
aaaaaTS_NOTIFICATIONEVENTS_PK PRIMARY KEY NONCLUSTERED
(
TS_ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
