Add SQL Server Automatically to a Composite Key
I have a table with a composite key in SQL Server 2008 (this was done for various reasons / DB design, but the main one is b / c, we go to a new schema and have to save the data).
So I created a new table with a foreign key reference and was working on data transfer. Finding and editing data is simple. Now I would like to be able to insert new records (using a given FK value) and be able to automatically increment the newly inserted record IDs and handle it all in a stored procedure.
Example
Table 1: ( id, type make up the composite key
)
id data type(FK)
1 A 1
2 B 1
1 C 2
2 D 2
3 B 1
4 C 1
3 A 2
4 G 2
Now if I have another record (F data of type 2), I would like to be able to pass the record data ( data=F,type=2
) and have a stored proc (5, F, 2) insert .. in a thread-safe / concurrency / scalable way (i.e. . without execution SELECT MAX(id)...
). Any ideas / thoughts on the best way to do this from the SQL Server community?
source to share
The easiest and safest solution is to simply add a column INT IDENTITY
to the table and let SQL Server handle the intricacies of updating the identity value. This will give you the real, correct and accurate value of the ID - regardless of the column value type
.
In SQL Server 2012, you can use a database object SEQUENCE
to create a sequence for each value type
you need - but the 2008 version, unfortunately, is still nothing like that.
source to share
This is one way to do it, assuming that the data you want to insert is in another table (Table 2):
INSERT INTO Table1(id, data, type)
SELECT ISNULL(B.MaxId,0) + ROW_NUMBER() OVER(PARTITION BY A.type ORDER BY A.data) AS Id,
A.data,
A.type
FROM Table2 A
LEFT JOIN (SELECT type, MAX(id) MaxId
FROM Table1
GROUP BY type) B
ON A.type = B.type
As @marc_s said in a comment: Using the MAX (Id) approach is usually not safe in concurrency load
source to share
Why would you want to store this data when you can always get it at the time of the request and ensure it is accurate?
SELECT
[type], data,
id = ROW_NUMBER() OVER (PARTITION BY [type] ORDER BY data)
FROM dbo.table
ORDER BY [type], id;
How you want to do this, you will need a trigger (or more) to maintain the information in the table as the data changes. For example, what happens when you run the following queries:
DELETE dbo.table WHERE data = 'B';
UPDATE dbo.table SET data = 'Z' WHERE data = 'C';
Do not store redundant data. You think it will save you something, but all he is going to do is take extra storage and create unnecessary maintenance persecution.
source to share