CODEWITHSUNDEEP
sqlsql-servert-sql
GarudaLead
GarudaLead

Reputation: 479

Insert & Delete from SQL best practice

I have a database with 2 tables: CurrentTickets & ClosedTickets. When a user creates a ticket via web application, a new row is created. When the user closes a ticket, the row from currenttickets is inserted into ClosedTickets and then deleted from CurrentTickets. If a user reopens a ticket, the same thing happens, only in reverse.

The catch is that one of the columns being copied back to CurrentTickets is the PK column (TicketID)that idendity is set to ON.

I know I can set the IDENTITY_INSERT to ON but as I understand it, this is generally frowned upon. I'm assuming that my database is a bit poorly designed. Is there a way for me to accomplish what I need without using IDENTITY_INSERT? How would I keep the TicketID column autoincremented without making it an identity column? I figure I could add another column RowID and make that the PK but I still want the TicketID column to autoincrement if possible but still not be considered an Idendity column.

Upvotes: 0

Views: 224

Answers (3)

Stoleg
Stoleg

Reputation: 9310

To achieve this in latest versions of SQL Server use OUTPUT clause (definition on MSDN).

OUTPUT clause used with a table variable:

declare @MyTableVar (...)

DELETE FROM dbo.CurrentTickets
OUTPUT DELETED.* INTO @MyTableVar
WHERE <...>;

INSERT INTO ClosedTicket
Select * from @MyTableVar

Second table should have ID column, but without IDENTITY property. It is enforced by the other table.

Upvotes: 0

Tanner
Tanner

Reputation: 22743

This just seems like bad design with 2 tables. Why not just have a single tickets table that stores all tickets. Then add a column called IsClosed, which is false by default. Once a ticket is closed you simply update the value to true and you don't have to do any copying to and from other tables.

All of your code around this part of your application will be much simpler and easier to maintain with a single table for tickets.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69544

Simple answer is DO NOT make an Identity column if you want your influence on the next Id generated in that column.

Also I think you have a really poor schema, Rather than having two tables just add another column in your CurrentTickets table, something like Open BIT and set its value to 1 by default and change the value to 0 when client closes the Ticket.

And you can Turn it On/Off as many time as client changes his mind, with having to go through all the trouble of Insert Identity and managing a whole separate table.

Update

Since now you have mentioned its SQL Server 2014, you have access to something called Sequence Object.

You define the object once and then every time you want a sequential number from it you just select next value from it, it is kind of hybrid of an Identity Column and having a simple INT column.

Upvotes: 0

Related Questions