Mixing
How to programmatically generate ID for table using stored procedure and insert?
1
I have created a Student table in my database, and I need to use a stored procedure to generate unique StudentID when inserting new rows, which should start at 005 up to and including 025.
create procedure FillStudent
as
insert into Student(StudentID)
values (005)
go
But then I am thinking, what if it becomes 2 digits? Would it automatically do 010? Or would it be 0010? If it would be the last one, What's the best approach to make it so like '010'? I am using SQL Server.
sql-server stored-procedures insert edited Nov 21 '18 at 6:00
marc_s
576k12811111258
asked Nov 21 '18 at 5:48
denisedenise
217
add a comment |
1
I have created a Student table in my database, and I need to use a stored procedure to generate unique StudentID when inserting new rows, which should start at 005 up to and including 025.
create procedure FillStudent
as
insert into Student(StudentID)
values (005)
go
But then I am thinking, what if it becomes 2 digits? Would it automatically do 010? Or would it be 0010? If it would be the last one, What's the best approach to make it so like '010'? I am using SQL Server.
sql-server stored-procedures insert edited Nov 21 '18 at 6:00
marc_s
576k12811111258
asked Nov 21 '18 at 5:48
denisedenise
217
Typically these sorts of questions relate back to the most recent topics covered in your course. So - what are they? Did you discuss identity columns? Sequences? Something else. In addition, the goal - as defined - is suspiciously stated. One generally does not put an upper bound on an ID column since that will limit the number of rows that can exist in the table (assuming that column has uniqueness enforced).
– SMor
Nov 21 '18 at 13:37
add a comment |
1
1
1
I have created a Student table in my database, and I need to use a stored procedure to generate unique StudentID when inserting new rows, which should start at 005 up to and including 025.
create procedure FillStudent
as
insert into Student(StudentID)
values (005)
go
But then I am thinking, what if it becomes 2 digits? Would it automatically do 010? Or would it be 0010? If it would be the last one, What's the best approach to make it so like '010'? I am using SQL Server.
sql-server stored-procedures insert edited Nov 21 '18 at 6:00
marc_s
576k12811111258
asked Nov 21 '18 at 5:48
denisedenise
217
I have created a Student table in my database, and I need to use a stored procedure to generate unique StudentID when inserting new rows, which should start at 005 up to and including 025.
create procedure FillStudent
as
insert into Student(StudentID)
values (005)
go
But then I am thinking, what if it becomes 2 digits? Would it automatically do 010? Or would it be 0010? If it would be the last one, What's the best approach to make it so like '010'? I am using SQL Server.
sql-server stored-procedures insert sql-server stored-procedures insert edited Nov 21 '18 at 6:00
marc_s
576k12811111258
asked Nov 21 '18 at 5:48
denisedenise
217
edited Nov 21 '18 at 6:00
marc_s
576k12811111258
asked Nov 21 '18 at 5:48
denisedenise
217
edited Nov 21 '18 at 6:00
marc_s
576k12811111258
edited Nov 21 '18 at 6:00
marc_s
576k12811111258
edited Nov 21 '18 at 6:00
marc_s
576k12811111258
576k12811111258
asked Nov 21 '18 at 5:48
denisedenise
217
asked Nov 21 '18 at 5:48
denisedenise
217
asked Nov 21 '18 at 5:48
denisedenise
217
217
Typically these sorts of questions relate back to the most recent topics covered in your course. So - what are they? Did you discuss identity columns? Sequences? Something else. In addition, the goal - as defined - is suspiciously stated. One generally does not put an upper bound on an ID column since that will limit the number of rows that can exist in the table (assuming that column has uniqueness enforced).
– SMor
Nov 21 '18 at 13:37
add a comment |
Typically these sorts of questions relate back to the most recent topics covered in your course. So - what are they? Did you discuss identity columns? Sequences? Something else. In addition, the goal - as defined - is suspiciously stated. One generally does not put an upper bound on an ID column since that will limit the number of rows that can exist in the table (assuming that column has uniqueness enforced).
– SMor
Nov 21 '18 at 13:37
Typically these sorts of questions relate back to the most recent topics covered in your course. So - what are they? Did you discuss identity columns? Sequences? Something else. In addition, the goal - as defined - is suspiciously stated. One generally does not put an upper bound on an ID column since that will limit the number of rows that can exist in the table (assuming that column has uniqueness enforced).
– SMor
Nov 21 '18 at 13:37
Typically these sorts of questions relate back to the most recent topics covered in your course. So - what are they? Did you discuss identity columns? Sequences? Something else. In addition, the goal - as defined - is suspiciously stated. One generally does not put an upper bound on an ID column since that will limit the number of rows that can exist in the table (assuming that column has uniqueness enforced).
– SMor
Nov 21 '18 at 13:37
add a comment |
2 Answers
2
active
oldest
votes
0
Play with this code:
CREATE SEQUENCE [dbo].[StudentsID]
START WITH 5
INCREMENT BY 1
MAXVALUE 25;
CREATE TABLE [dbo].[Student]
(
[StudentID] CHAR(3)
);
GO
CREATE PROCEDURE [dbo].[FillStudent]
AS
BEGIN;
INSERT INTO [dbo].[Student] ([StudentID])
VALUES (RIGHT(NEXT VALUE FOR [dbo].[StudentsID] + 1000, 3));
END;
GO
GO
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
SELECT *
FROM [dbo].[Student];
DROP TABLE [dbo].[Student];
DROP PROCEDURE [dbo].[FillStudent];
DROP SEQUENCE [dbo].[StudentsID];
The idea is to use sequence object.
If you need to use identity column you can create the table with identity column, computed column (this is the value you need) and a check constraint to stop adding records after id = 25 is reach. Execute the script below:
CREATE TABLE [dbo].[Student]
(
[ID] TINYINT IDENTITY(5, 1)
,[StudentID] AS RIGHT([ID] + 1000, 3) PERSISTED
,CONSTRAINT CK_Student CHECK ([ID] < 26)
);
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
SELECT *
FROM [dbo].[Student]
DROP TABLE [dbo].[Student];
You can see after the record with ID = 25 you are getting an error.
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
Thank you for this. I've been playing with this code and it works perfectly fine. But we haven't come across 'sequence' topic.
– denise
Nov 21 '18 at 16:26
@denise is it helpful? let me know if you have any questions/issues
– gotqn
Nov 21 '18 at 16:29
in my case, i need another way to approach the problem. tho i tried to run the code you provided, it was working fine. I am thinking of declaring a variable then do 'while loop' then do an 'insert'?? do you think that will be a good idea?
– denise
Nov 22 '18 at 6:13
@denise You can do it in loop, too - it will be easier, but what's the final goal? You can addidentitycolumn to your table and then to add a compute column which is calculating theXXXvalue, also.
– gotqn
Nov 22 '18 at 6:16
the goal is to fill the Student table with 005-025 StudentID using insert and I should use a single multi-elementinsertto add theStudentID. I can sure use@@IDENTITYand do awhile loopwhile it is less than 26?? Also, another goal is to make theStudentIDformat like 005, 010 and not just plain 5,6,7,10.
– denise
Nov 23 '18 at 1:04
|
show 1 more comment
0
Just make your StudentID column an identity column. In your case set the seed value as 5 and increment as 1.
[StudentID] [int] IDENTITY(5,1) NOT NULL
Then SQL Server will automatically populate that column for you. Just ignore that column in your INSERT statement.
Never try to add leading zeros into the actual data stored in the table. That is not a good approach. Recommended way is to achieve such requirements via formatting the data upon retrieval. You can achieve it as follows.
REPLACE(STR([StudentID],3),' ','0')
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405941%2fhow-to-programmatically-generate-id-for-table-using-stored-procedure-and-insert%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Play with this code:
CREATE SEQUENCE [dbo].[StudentsID]
START WITH 5
INCREMENT BY 1
MAXVALUE 25;
CREATE TABLE [dbo].[Student]
(
[StudentID] CHAR(3)
);
GO
CREATE PROCEDURE [dbo].[FillStudent]
AS
BEGIN;
INSERT INTO [dbo].[Student] ([StudentID])
VALUES (RIGHT(NEXT VALUE FOR [dbo].[StudentsID] + 1000, 3));
END;
GO
GO
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
SELECT *
FROM [dbo].[Student];
DROP TABLE [dbo].[Student];
DROP PROCEDURE [dbo].[FillStudent];
DROP SEQUENCE [dbo].[StudentsID];
The idea is to use sequence object.
If you need to use identity column you can create the table with identity column, computed column (this is the value you need) and a check constraint to stop adding records after id = 25 is reach. Execute the script below:
CREATE TABLE [dbo].[Student]
(
[ID] TINYINT IDENTITY(5, 1)
,[StudentID] AS RIGHT([ID] + 1000, 3) PERSISTED
,CONSTRAINT CK_Student CHECK ([ID] < 26)
);
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
SELECT *
FROM [dbo].[Student]
DROP TABLE [dbo].[Student];
You can see after the record with ID = 25 you are getting an error.
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
Thank you for this. I've been playing with this code and it works perfectly fine. But we haven't come across 'sequence' topic.
– denise
Nov 21 '18 at 16:26
@denise is it helpful? let me know if you have any questions/issues
– gotqn
Nov 21 '18 at 16:29
in my case, i need another way to approach the problem. tho i tried to run the code you provided, it was working fine. I am thinking of declaring a variable then do 'while loop' then do an 'insert'?? do you think that will be a good idea?
– denise
Nov 22 '18 at 6:13
@denise You can do it in loop, too - it will be easier, but what's the final goal? You can addidentitycolumn to your table and then to add a compute column which is calculating theXXXvalue, also.
– gotqn
Nov 22 '18 at 6:16
the goal is to fill the Student table with 005-025 StudentID using insert and I should use a single multi-elementinsertto add theStudentID. I can sure use@@IDENTITYand do awhile loopwhile it is less than 26?? Also, another goal is to make theStudentIDformat like 005, 010 and not just plain 5,6,7,10.
– denise
Nov 23 '18 at 1:04
|
show 1 more comment
0
Play with this code:
CREATE SEQUENCE [dbo].[StudentsID]
START WITH 5
INCREMENT BY 1
MAXVALUE 25;
CREATE TABLE [dbo].[Student]
(
[StudentID] CHAR(3)
);
GO
CREATE PROCEDURE [dbo].[FillStudent]
AS
BEGIN;
INSERT INTO [dbo].[Student] ([StudentID])
VALUES (RIGHT(NEXT VALUE FOR [dbo].[StudentsID] + 1000, 3));
END;
GO
GO
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
SELECT *
FROM [dbo].[Student];
DROP TABLE [dbo].[Student];
DROP PROCEDURE [dbo].[FillStudent];
DROP SEQUENCE [dbo].[StudentsID];
The idea is to use sequence object.
If you need to use identity column you can create the table with identity column, computed column (this is the value you need) and a check constraint to stop adding records after id = 25 is reach. Execute the script below:
CREATE TABLE [dbo].[Student]
(
[ID] TINYINT IDENTITY(5, 1)
,[StudentID] AS RIGHT([ID] + 1000, 3) PERSISTED
,CONSTRAINT CK_Student CHECK ([ID] < 26)
);
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
SELECT *
FROM [dbo].[Student]
DROP TABLE [dbo].[Student];
You can see after the record with ID = 25 you are getting an error.
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
Thank you for this. I've been playing with this code and it works perfectly fine. But we haven't come across 'sequence' topic.
– denise
Nov 21 '18 at 16:26
@denise is it helpful? let me know if you have any questions/issues
– gotqn
Nov 21 '18 at 16:29
in my case, i need another way to approach the problem. tho i tried to run the code you provided, it was working fine. I am thinking of declaring a variable then do 'while loop' then do an 'insert'?? do you think that will be a good idea?
– denise
Nov 22 '18 at 6:13
@denise You can do it in loop, too - it will be easier, but what's the final goal? You can addidentitycolumn to your table and then to add a compute column which is calculating theXXXvalue, also.
– gotqn
Nov 22 '18 at 6:16
the goal is to fill the Student table with 005-025 StudentID using insert and I should use a single multi-elementinsertto add theStudentID. I can sure use@@IDENTITYand do awhile loopwhile it is less than 26?? Also, another goal is to make theStudentIDformat like 005, 010 and not just plain 5,6,7,10.
– denise
Nov 23 '18 at 1:04
|
show 1 more comment
0
0
0
Play with this code:
CREATE SEQUENCE [dbo].[StudentsID]
START WITH 5
INCREMENT BY 1
MAXVALUE 25;
CREATE TABLE [dbo].[Student]
(
[StudentID] CHAR(3)
);
GO
CREATE PROCEDURE [dbo].[FillStudent]
AS
BEGIN;
INSERT INTO [dbo].[Student] ([StudentID])
VALUES (RIGHT(NEXT VALUE FOR [dbo].[StudentsID] + 1000, 3));
END;
GO
GO
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
SELECT *
FROM [dbo].[Student];
DROP TABLE [dbo].[Student];
DROP PROCEDURE [dbo].[FillStudent];
DROP SEQUENCE [dbo].[StudentsID];
The idea is to use sequence object.
If you need to use identity column you can create the table with identity column, computed column (this is the value you need) and a check constraint to stop adding records after id = 25 is reach. Execute the script below:
CREATE TABLE [dbo].[Student]
(
[ID] TINYINT IDENTITY(5, 1)
,[StudentID] AS RIGHT([ID] + 1000, 3) PERSISTED
,CONSTRAINT CK_Student CHECK ([ID] < 26)
);
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
SELECT *
FROM [dbo].[Student]
DROP TABLE [dbo].[Student];
You can see after the record with ID = 25 you are getting an error.
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
Play with this code:
CREATE SEQUENCE [dbo].[StudentsID]
START WITH 5
INCREMENT BY 1
MAXVALUE 25;
CREATE TABLE [dbo].[Student]
(
[StudentID] CHAR(3)
);
GO
CREATE PROCEDURE [dbo].[FillStudent]
AS
BEGIN;
INSERT INTO [dbo].[Student] ([StudentID])
VALUES (RIGHT(NEXT VALUE FOR [dbo].[StudentsID] + 1000, 3));
END;
GO
GO
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
EXEC [dbo].[FillStudent];
SELECT *
FROM [dbo].[Student];
DROP TABLE [dbo].[Student];
DROP PROCEDURE [dbo].[FillStudent];
DROP SEQUENCE [dbo].[StudentsID];
The idea is to use sequence object.
If you need to use identity column you can create the table with identity column, computed column (this is the value you need) and a check constraint to stop adding records after id = 25 is reach. Execute the script below:
CREATE TABLE [dbo].[Student]
(
[ID] TINYINT IDENTITY(5, 1)
,[StudentID] AS RIGHT([ID] + 1000, 3) PERSISTED
,CONSTRAINT CK_Student CHECK ([ID] < 26)
);
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
INSERT INTO [dbo].[Student] DEFAULT VALUES;
SELECT *
FROM [dbo].[Student]
DROP TABLE [dbo].[Student];
You can see after the record with ID = 25 you are getting an error.
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
edited Nov 23 '18 at 9:42
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
answered Nov 21 '18 at 6:08
gotqngotqn
20.2k32115189
20.2k32115189
Thank you for this. I've been playing with this code and it works perfectly fine. But we haven't come across 'sequence' topic.
– denise
Nov 21 '18 at 16:26
@denise is it helpful? let me know if you have any questions/issues
– gotqn
Nov 21 '18 at 16:29
in my case, i need another way to approach the problem. tho i tried to run the code you provided, it was working fine. I am thinking of declaring a variable then do 'while loop' then do an 'insert'?? do you think that will be a good idea?
– denise
Nov 22 '18 at 6:13
@denise You can do it in loop, too - it will be easier, but what's the final goal? You can addidentitycolumn to your table and then to add a compute column which is calculating theXXXvalue, also.
– gotqn
Nov 22 '18 at 6:16
the goal is to fill the Student table with 005-025 StudentID using insert and I should use a single multi-elementinsertto add theStudentID. I can sure use@@IDENTITYand do awhile loopwhile it is less than 26?? Also, another goal is to make theStudentIDformat like 005, 010 and not just plain 5,6,7,10.
– denise
Nov 23 '18 at 1:04
|
show 1 more comment
Thank you for this. I've been playing with this code and it works perfectly fine. But we haven't come across 'sequence' topic.
– denise
Nov 21 '18 at 16:26
@denise is it helpful? let me know if you have any questions/issues
– gotqn
Nov 21 '18 at 16:29
in my case, i need another way to approach the problem. tho i tried to run the code you provided, it was working fine. I am thinking of declaring a variable then do 'while loop' then do an 'insert'?? do you think that will be a good idea?
– denise
Nov 22 '18 at 6:13
@denise You can do it in loop, too - it will be easier, but what's the final goal? You can addidentitycolumn to your table and then to add a compute column which is calculating theXXXvalue, also.
– gotqn
Nov 22 '18 at 6:16
the goal is to fill the Student table with 005-025 StudentID using insert and I should use a single multi-elementinsertto add theStudentID. I can sure use@@IDENTITYand do awhile loopwhile it is less than 26?? Also, another goal is to make theStudentIDformat like 005, 010 and not just plain 5,6,7,10.
– denise
Nov 23 '18 at 1:04
Thank you for this. I've been playing with this code and it works perfectly fine. But we haven't come across 'sequence' topic.
– denise
Nov 21 '18 at 16:26
Thank you for this. I've been playing with this code and it works perfectly fine. But we haven't come across 'sequence' topic.
– denise
Nov 21 '18 at 16:26
@denise is it helpful? let me know if you have any questions/issues
– gotqn
Nov 21 '18 at 16:29
@denise is it helpful? let me know if you have any questions/issues
– gotqn
Nov 21 '18 at 16:29
in my case, i need another way to approach the problem. tho i tried to run the code you provided, it was working fine. I am thinking of declaring a variable then do 'while loop' then do an 'insert'?? do you think that will be a good idea?
– denise
Nov 22 '18 at 6:13
in my case, i need another way to approach the problem. tho i tried to run the code you provided, it was working fine. I am thinking of declaring a variable then do 'while loop' then do an 'insert'?? do you think that will be a good idea?
– denise
Nov 22 '18 at 6:13
@denise You can do it in loop, too - it will be easier, but what's the final goal? You can add
identity column to your table and then to add a compute column which is calculating the XXX value, also.– gotqn
Nov 22 '18 at 6:16
@denise You can do it in loop, too - it will be easier, but what's the final goal? You can add
identity column to your table and then to add a compute column which is calculating the XXX value, also.– gotqn
Nov 22 '18 at 6:16
the goal is to fill the Student table with 005-025 StudentID using insert and I should use a single multi-element
insert to add the StudentID. I can sure use @@IDENTITY and do a while loop while it is less than 26?? Also, another goal is to make the StudentID format like 005, 010 and not just plain 5,6,7,10.– denise
Nov 23 '18 at 1:04
the goal is to fill the Student table with 005-025 StudentID using insert and I should use a single multi-element
insert to add the StudentID. I can sure use @@IDENTITY and do a while loop while it is less than 26?? Also, another goal is to make the StudentID format like 005, 010 and not just plain 5,6,7,10.– denise
Nov 23 '18 at 1:04
|
show 1 more comment
0
Just make your StudentID column an identity column. In your case set the seed value as 5 and increment as 1.
[StudentID] [int] IDENTITY(5,1) NOT NULL
Then SQL Server will automatically populate that column for you. Just ignore that column in your INSERT statement.
Never try to add leading zeros into the actual data stored in the table. That is not a good approach. Recommended way is to achieve such requirements via formatting the data upon retrieval. You can achieve it as follows.
REPLACE(STR([StudentID],3),' ','0')
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
add a comment |
0
Just make your StudentID column an identity column. In your case set the seed value as 5 and increment as 1.
[StudentID] [int] IDENTITY(5,1) NOT NULL
Then SQL Server will automatically populate that column for you. Just ignore that column in your INSERT statement.
Never try to add leading zeros into the actual data stored in the table. That is not a good approach. Recommended way is to achieve such requirements via formatting the data upon retrieval. You can achieve it as follows.
REPLACE(STR([StudentID],3),' ','0')
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
add a comment |
0
0
0
Just make your StudentID column an identity column. In your case set the seed value as 5 and increment as 1.
[StudentID] [int] IDENTITY(5,1) NOT NULL
Then SQL Server will automatically populate that column for you. Just ignore that column in your INSERT statement.
Never try to add leading zeros into the actual data stored in the table. That is not a good approach. Recommended way is to achieve such requirements via formatting the data upon retrieval. You can achieve it as follows.
REPLACE(STR([StudentID],3),' ','0')
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
Just make your StudentID column an identity column. In your case set the seed value as 5 and increment as 1.
[StudentID] [int] IDENTITY(5,1) NOT NULL
Then SQL Server will automatically populate that column for you. Just ignore that column in your INSERT statement.
Never try to add leading zeros into the actual data stored in the table. That is not a good approach. Recommended way is to achieve such requirements via formatting the data upon retrieval. You can achieve it as follows.
REPLACE(STR([StudentID],3),' ','0')
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
edited Nov 21 '18 at 6:28
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
answered Nov 21 '18 at 6:07
Chamika GoonetilakaChamika Goonetilaka
477312
477312
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405941%2fhow-to-programmatically-generate-id-for-table-using-stored-procedure-and-insert%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Typically these sorts of questions relate back to the most recent topics covered in your course. So - what are they? Did you discuss identity columns? Sequences? Something else. In addition, the goal - as defined - is suspiciously stated. One generally does not put an upper bound on an ID column since that will limit the number of rows that can exist in the table (assuming that column has uniqueness enforced).
– SMor
Nov 21 '18 at 13:37