March 17, 2009 at 12:58 pm
Hi I am new to SQL server and I am working on a project that needs to insert id's say UID into a user table.How do I do this.Can you refer to me to any article that will help me learn this.Thanks in advance.
Regards
Raviteja Chilakamarthi
March 17, 2009 at 1:12 pm
Raviteja,
I think you are looking for something like this:
alter table dbo.test
add testID int identity(1,1) not null
March 17, 2009 at 1:36 pm
Are you talking about how to add a column to a table that could be used as an Identity or are you asking how to insert a value into an identity column. Please be a bit more specific, then people here can give you a good answer.
-Roy
March 17, 2009 at 1:39 pm
I already have a column name UID in a table called USER, for which I need to generate the ID numbers.
March 17, 2009 at 1:47 pm
USE [test]
GO
CREATE TABLE [dbo].[test1](
[idcol] [int] IDENTITY(1,1) NOT NULL,
[Somecol] [char](10) NULL
) ON [PRIMARY]
GO
If your table is like this, You just need to insert into Test1 the value of second column.
Insert into test1 (SomeCol)
values 'dfsddfdsd'
This statement will create a record with idcol as 1. Next time you enter another value, the value of idcol will be 2.
Roy
-Roy
March 17, 2009 at 2:27 pm
I want the generated values to be of this format
U0001
U0002
Can I do this with the format that you have given.
March 17, 2009 at 8:45 pm
ravi.chilakamarthi (3/17/2009)
I want the generated values to be of this formatU0001
U0002
Can I do this with the format that you have given.
You could define a computed column with the formatted user id something like this:
CREATE TABLE SystemUser (
UserId INT IDENTITY(1,1) NOT NULL,
FormattedUserId AS ('U' + RIGHT('0000000000' + CAST(UserId AS VARCHAR(10)),10)),
UserName VARCHAR(50)
)
INSERT INTO SystemUser VALUES ('Test1')
INSERT INTO SystemUser VALUES ('Test2')
INSERT INTO SystemUser VALUES ('Test3')
INSERT INTO SystemUser VALUES ('Test4')
SELECT * FROM SystemUser
DROP TABLE SystemUser
I chose 10 as the maximum numeric length because INTs can be 10 digits.
If you were using BIGINT or DECIMAL you'd have to choose a different size.
The output is:
UserId FormattedUserId UserName
1 U0000000001 Test1
2 U0000000002 Test2
3 U0000000003 Test3
4 U0000000004 Test4
Of course, you'll need to define some further constraints if you want your Ids to be unique and non-negative.
March 18, 2009 at 6:39 am
This looks great but I dont want the USER ID to be in the table......how can I avoid that??
March 18, 2009 at 4:09 pm
ravi.chilakamarthi (3/18/2009)
This looks great but I dont want the USER ID to be in the table......how can I avoid that??
SELECT FormattedUserId, UserName FROM SystemUser
Why don't you want the UserId in the table?
July 3, 2012 at 5:16 am
I had the same problem.
I need to create custom ids for each table, it's a direct order from the boss.
Until know i got this function to format the IDs (i use REPLICATE it like the excel REPT function):
CREATE FUNCTION [dbo].[ge_fn_formatID] (
@Prefix char(1),
@Id bigint
)
RETURNS NVARCHAR(10)
AS
BEGIN
declare @Length int
set @Length = 9
RETURN ( SELECT @Prefix + RIGHT(REPLICATE('0', @Length) + CAST(@Id as nvarchar(10)), @Length))
END
For this tabe:
CREATE TABLE [dbo].[teste](
[id] [nvarchar](50) NOT NULL,
[teste] [nvarchar](50) NULL,
CONSTRAINT [PK_teste] PRIMARY KEY CLUSTERED
(
[id] ASC
))
When i do the insert, i have to be unsure that no one are selecting or creating a new item, soo i think i have to lock the Transaction, like this:
begin tran
begin try
--
insert into [dbo].[teste]
select dbo.[ge_fn_formataID] ('T', count(id)+1), 'Lisbon'
from [dbo].[teste]
with (HOLDLOCK)
end try
begin catch -- start catch 1
DECLARE @msg nvarchar(max)
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity nvarchar(10);
set @msg=N'Erro: %s | Severity: %s'
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = cast(str(ERROR_SEVERITY()) as nvarchar(10))
RAISERROR (@msg, 10, 1,@ErrorMessage, @ErrorSeverity)
IF (@@TRANCOUNT > 0) ROLLBACK
end catch --end catch 1
IF (@@TRANCOUNT > 0) COMMIT
There are any better ideas?
Thanks
Luis Costa
July 3, 2012 at 5:40 am
the way i've always tackled this situation is to go ahead and have an identity column, and then a persisted calculated column to create teh text-based userid;
For example, Invoices: this does not allow the person to select the Prefix, it gets calculated from another column:
the identity and the calculated column completely solve the concurrency issue you are fighting with.
CREATE TABLE Invoices(
InvoicesID int identity(1,1) not null PRIMARY KEY,
InvoiceType varchar(3) not null CHECK(InvoiceType IN('PO','RT','PR') ), --purchase order return or purchase request,
InvoiceNumber AS InvoiceType + RIGHT(REPLICATE('0', 10) + CONVERT(varchar,InvoicesID),10) PERSISTED,
OtherColumns varchar(30) )
INSERT INTO Invoices (InvoiceType,OtherColumns)
SELECT 'PO', 'A Purchase Order' UNION ALL
SELECT 'RT', 'A Return Document' UNION ALL
SELECT 'PR', 'A Purchase Request'
SELECT * FROM Invoices
/*
--Results
1 PO PO0000000001 A Purchase Order
2 RT RT0000000002 A Return Document
3 PR PR0000000003 A Purchase Request
*/
Lowell
July 3, 2012 at 5:40 am
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 3, 2012 at 9:50 am
I can't use a calculated field, must be only one column, and must be key.
I tested in 2 sessions 5000 inserts, each, without lock and I had 4000 errors
Then i did the same test with (HOLDLOCK), and i got 2 errors in one session, and zero on the other one.
Then i put a lable just before the begin tran
and a goto in the catch, after the rollback (with a counter, e.g. try 20x), and i got Zero errors... and 3 minutes to insert 5000 on each session... it's an horror of no errors!
AHHHHHHHHHHHHHHHHHHHHHHHHHHHH!
thanks....
July 3, 2012 at 10:01 am
well an identity will easily fix the contention issue, so if you arbitrarily eliminate that possibility, i'm not sure what you can do.
lfcost (7/3/2012)
I can't use a calculated field, must be only one column, and must be key.I tested in 2 sessions 5000 inserts, each, without lock and I had 4000 errors
Then i did the same test with (HOLDLOCK), and i got 2 errors in one session, and zero on the other one.
Then i put a lable just before the begin tran
and a goto in the catch, after the rollback (with a counter, e.g. try 20x), and i got Zero errors... and 3 minutes to insert 5000 on each session... it's an horror of no errors!
AHHHHHHHHHHHHHHHHHHHHHHHHHHHH!
thanks....
Lowell
July 3, 2012 at 10:28 am
Thanks.
You are right.
Back to identity and do all the job not to show the id column without a mask.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply