Use autogenerated column to populate another column
How can I use an auto-generated column to populate another column during a statement INSERT
?
In short: we are reusing the database table and associated ASP page to display completely different data than originally intended.
I have a table that is similar in structure to the following. This structure is beyond my control.
ID int NON-NULL, IDENTITY(1,1)
OrderNo varchar(50) NON-NULL, UNIQUE
More ...
The table has been repurposed and we are not using the column OrderNo
. However, he NON-NULL
and UNIQUE
. As dummy data, I want to fill it with a column ID
.
I have the following SQL so far, but cannot work out how to use the generated string ID
.
INSERT INTO MyTable (OrderNo, More)
OUTPUT INSERTED.ID
VALUES (CAST(ID AS varchar(50)))
It just gives:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'ID'.
source to share
Here is a solution using the OUTPUT clause. Unfortunately, you cannot do this in one application.
CREATE TABLE Orders (
ID int not null identity(1,1),
OrderNo varchar(50) not null unique
)
CREATE TABLE #NewIDs ( ID int )
INSERT Orders (OrderNo)
OUTPUT INSERTED.ID INTO #NewIDs
SELECT 12345
UPDATE o
SET o.OrderNo = i.ID
FROM Orders o
JOIN #NewIDs i
ON i.ID = o.ID
SELECT * FROM Orders
source to share
Given this table:
CREATE TABLE dbo.Example
(
ID integer NOT NULL IDENTITY(1,1),
OrderNo varchar(50) NOT NULL,
More varchar(50) NULL
);
Single row inserts
If everyone INSERTs
that omits the column OrderNo
will be one-liner , a constraint can be used DEFAULT
:
ALTER TABLE dbo.Example
ADD DEFAULT CONVERT(varchar(50), IDENT_CURRENT('dbo.Example'))
FOR OrderNo;
Example (only one-line inserts):
INSERT dbo.Example (More) VALUES ('More');
INSERT dbo.Example (More) VALUES ('More');
After that, the contents of the table:
Multiline INSERT
In this case, an AFTER trigger can be used, but another option is a trigger INSTEAD OF
that executes a single INSERT
(no secondary UPDATE
):
CREATE TRIGGER trg ON dbo.Example
INSTEAD OF INSERT
AS
BEGIN
INSERT dbo.Example (OrderNo, More)
SELECT
CONVERT(varchar(50),
CONVERT(bigint, IDENT_CURRENT('dbo.Example')) +
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +
CASE
WHEN IDENT_CURRENT('dbo.Example') = IDENT_SEED('dbo.Example')
THEN CONVERT(bigint, -1)
ELSE CONVERT(bigint, 0)
END),
More
FROM INSERTED;
END;
The multiline INSERT
can now be successfully processed:
INSERT dbo.Example (More)
VALUES ('More 1'), ('More 2'), ('More 3');
Table content:
The disadvantage of this method is that it will require maintenance if the table schema changes.
source to share
One of the options:
create trigger YourTable_Trigger
on YourTable
INSTEAD OF INSERT
as begin
INSERT INTO YourTable (OrderNo, AnotherField)
SELECT 0, AnotherField FROM Inserted
UPDATE YourTable SET OrderNo = SCOPE_IDENTITY() WHERE ID = SCOPE_IDENTITY()
end;
And here's the Fiddle .
Good luck.
source to share