April 25, 2012 at 7:48 pm
Hello SQLCentral.com members and administrators,
A greeting of peace.
I want to ask help because I am having a problem in my stored procedure.
Before we head to the problem, these are the background of my table/s:
1. [font="Courier New"]PERSON[/font], it has four columns:
[font="Courier New"]person_id (int, PK, identity(1,1))
last_name (nvarchar(50))
first_name (nvarchar(50))
middle_name (nvarchar(50))[/font]
2. [font="Courier New"]STUDENT[/font], it has three columns:
[font="Courier New"]id (int, PK, identity(1,1))
person_id(int,FK)
student_number( nvarchar(20) )[/font]
(the same thing goes for FACULTY table, just uses faculty_number instead of student_number)
The relationship is the person_id in PERSON table is my private key and person_id in STUDENT is
a foreign key
This is my stored procedure for adding record to student:
CREATE PROCEDURE ADDSTUDENT
@num NVARCHAR(20),
@fn NVARCHAR(50),
@mn NVARCHAR(50),
@ln NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [PERSON] VALUES (@ln,@fn,@mn)
INSERT INTO [STUDENT] VALUES (@num)
END
This stored procedure parsed correctly, but when I clicked Execute, The error is:
Column name or number of supplied values does not match table definition.
This error points to the second INSERT statement.
Isn't that since [font="Courier New"]person_id[/font] in [font="Courier New"]STUDENT[/font] table is a foreign key "connected" to the [font="Courier New"]person_id[/font] in [font="Courier New"]PERSON[/font],whatever value of [font="Courier New"]person_id[/font] in [font="Courier New"]PERSON[/font] table will be automatically saved in [font="Courier New"]person_id[/font] in [font="Courier New"]STUDENT[/font] table?
The scenario would be, if the first 15 records on [font="Courier New"]PERSON[/font] table is a faculty, then if 16th record is a student, I am expecting a record in [font="Courier New"]STUDENT[/font] table like:
[font="Courier New"]
id | person_id | student_number
------------------------------------
1 | 17 | STUD000001
[/font]
How can I transfer the 17 from [font="Courier New"]PERSON[/font] table to [font="Courier New"]STUDENT[/font] table?
Please help me change my stored procedure or table if I miss something.
Thank you and more power.
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 25, 2012 at 7:55 pm
Duplicate post (with a little more info). Please direct all replies here.
Please do not start new threads for problems you have already posted.
This problem
This stored procedure parsed correctly, but when I clicked Execute, The error is:
Column name or number of supplied values does not match table definition.
This error points to the second INSERT statement.
Is answered in the thread referenced by the hyperlink above.
You have a problem with BOTH insert statements. Please reread my post on the other thread.
April 25, 2012 at 8:16 pm
I understand, but is there other way to erase the previous one? I can no longer change the title of my previous post (I sent a "request" to administrators of this site to change the title, or delete it, if possible. Sorry, this would never happen again.)
To Lynn:
Is it not if a column is "declared" as [font="Courier New"]IDENTITY(1,1)[/font], that column will have an auto-generated number? 🙂 Because if I run:
INSERT INTO PERSON VALUES('Parker','Peter','Piper')
Then
SELECT * FROM PERSON
the result is:
person_id | last_name | first_name | middle_name
-------------------------------------------------
1 | Parker | Peter | Piper
1 is automatically inserted to [font="Courier New"]person_id[/font]. If I insert again a data, I always see that [font="Courier New"]person_id[/font] is always incremented by 1.
My problem is the [font="Courier New"]person_id[/font] in [font="Courier New"]STUDENT[/font] table. How can I pass the data from [font="Courier New"]PERSON[/font] table to [font="Courier New"]STUDENT[/font] table?
[font="Courier New"]PERSON[/font] ([font="Courier New"]person_id[/font] is PK) -------> [font="Courier New"]STUDENT[/font] ([font="Courier New"]person_id[/font] is FK)
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 25, 2012 at 8:28 pm
I'm not reposting any of my responses. You'll have to go to that thread to see what I have done.
April 25, 2012 at 8:29 pm
You need to actually pull the pk value from the PERSON table. You can do this a number of ways.
declare @pkPerson int
select
@pkPerson = person_id
from Person
where first_name = 'John'
and last_name = 'Smith'
insert into Student
(
person_id,
student_id
)
values
(
@pkPerson,
@yourStudentId
)
Because you're actually inserting the person record right before, you can simply do this:
declare @pkPerson int
insert into Person
(
first_name,
middle_name,
last_name
)
values
(
@fn,
@mn,
@ln
)
select @pkPerson = scope_identity()
insert into Student
(
person_id,
student_number
)
values
(
@pkPerson,
)
April 25, 2012 at 9:33 pm
To Sir Thomas Stringer and Sir Lynn Pettis:
Thank you very much brothers. It works. :w00t: ... 😀 ... :kiss:
Perfect combination...
QUESTION: why [font="Courier New"]SCOPE_IDENTITY()[/font] was used? Is it because:
1. both tables have a same column name named [font="Courier New"]person_id[/font] alone (I mean ONLY just the name matters)?
2. [font="Courier New"]person_id[/font] in [font="Courier New"]PERSON[/font] table is a private key and "is connected" to [font="Courier New"]STUDENT[/font]'s [font="Courier New"]person_id[/font] which, in turn, is a foreign key?
3. Both reasons are true?
QUESTION: IF in case I will get two column data in [font="Courier New"]PERSON[/font] table ([font="Courier New"]person_id[/font] and [font="Courier New"]last_name[/font] for example) and transfer that data in [font="Courier New"]STUDENT[/font] table (assuming I have a same column named [font="Courier New"]last_name[/font]), will [font="Courier New"]SCOPE_IDENTITY()[/font] still the best option?
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 25, 2012 at 9:41 pm
Here is yet another alternative:
DECLARE @PERSON TABLE (
person_id int identity(1,1)
,last_name nvarchar(50)
,first_name nvarchar(50)
,middle_name nvarchar(50))
DECLARE @STUDENT TABLE (
id int identity(1,1)
,person_id int
,student_number nvarchar(20) )
INSERT INTO @PERSON VALUES ('Hanks', 'Tom', NULL)
INSERT INTO @PERSON VALUES ('Affleck', 'Ben', NULL)
INSERT INTO @PERSON VALUES ('Marx', 'Karl', NULL)
INSERT INTO @PERSON VALUES ('C', 'Dwain', NULL)
INSERT INTO @STUDENT VALUES (scope_identity(), 'DC001')
-- Alternative
INSERT INTO @PERSON
OUTPUT INSERTED.person_id, 'XX001'
INTO @STUDENT
VALUES ('Berenger', 'Tom', NULL)
SELECT * FROM @PERSON
SELECT * FROM @STUDENT
Whenever I hear INSERT/INSERT, UPDATE/INSERT or DELETE/INSERT, I think of OUTPUT.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 25, 2012 at 11:10 pm
marksquall (4/25/2012)
To Thomas Stringer and Lynn Pettis:Thank you very much Sir and Mam. It works. :w00t: ... 😀 ... :kiss:
Perfect combination...
QUESTION: why [font="Courier New"]SCOPE_IDENTITY()[/font] was used? Is it because:
1. both tables have a same column name named [font="Courier New"]person_id[/font] alone (I mean ONLY just the name matters)?
2. [font="Courier New"]person_id[/font] in [font="Courier New"]PERSON[/font] table is a private key and "is connected" to [font="Courier New"]STUDENT[/font]'s [font="Courier New"]person_id[/font] which, in turn, is a foreign key?
3. Both reasons are true?
QUESTION: IF in case I will get two column data in [font="Courier New"]PERSON[/font] table ([font="Courier New"]person_id[/font] and [font="Courier New"]last_name[/font] for example) and transfer that data in [font="Courier New"]STUDENT[/font] table (assuming I have a same column named [font="Courier New"]last_name[/font]), will [font="Courier New"]SCOPE_IDENTITY()[/font] still the best option?
Warm regards,
Mark Squall
Except I'm a sir.
April 26, 2012 at 9:13 am
To Thomas Stringer and Lynn Pettis:
Thank you very much as always. It worked like a charm. 😀
yahoo...
To dwain.c:
Thank you for another alternative. I am sure I will use your sample one way or another... 🙂
QUESTION: why [font="Courier New"]SCOPE_IDENTITY()[/font] was used? Is it because:
1. both tables have a same column name named [font="Courier New"]person_id[/font] alone (I mean ONLY just the name matters)?
2. [font="Courier New"]person_id[/font] column in [font="Courier New"]PERSON[/font] table is a private key and "is connected" to [font="Courier New"]STUDENT[/font]'s [font="Courier New"]person_id[/font] which, in turn, is a foreign key?
3. Both reasons are true?
QUESTION: IF in case I will get two column data in PERSON table (person_id and last_name for example) and transfer that data in STUDENT table (assuming I have a same column named last_name), will [font="Courier New"]SCOPE_IDENTITY()[/font] still the best option?
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 26, 2012 at 9:42 am
QUESTION: why SCOPE_IDENTITY() was used? Is it because:
1. both tables have a same column name named person_id alone (I mean ONLY just the name matters)?
2. person_id column in PERSON table is a private key and "is connected" to STUDENT's person_id which, in turn, is a foreign key?
3. Both reasons are true?
-- You can answer this question yourself by looking up @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT in Books Online.
QUESTION: IF in case I will get two column data in PERSON table (person_id and last_name for example) and transfer that data in STUDENT table (assuming I have a same column named last_name), will SCOPE_IDENTITY() still the best option?
-- SCOPE_IDENTITY only returns the most recent identity value in the same scope. If you also want to insert the last_name (in this case) into the student table, then you would probable expand what I wrote to this:
CREATE PROCEDURE ADDSTUDENT
@num NVARCHAR(20),
@fn NVARCHAR(50),
@mn NVARCHAR(50),
@ln NVARCHAR(50)
AS
BEGIN
DECLARE @IDs table (id int, LastName nvarchar(50));
SET NOCOUNT ON
INSERT INTO [PERSON](last_name,first_name,middle_name)
OUTPUT INSERTED.person_id, INSERTED.last_name into @IDs
VALUES (@ln,@fn,@mn);
INSERT INTO [STUDENT](person_id, last_name)
SELECT id, LastName FROM @IDs;
END
April 26, 2012 at 9:53 am
To Lynn Pettis:
Noted. 😉
Thanks.
Thank you everyone and hope to hear more from you.
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 27, 2012 at 4:32 am
Also as far as Scope_Identity() or @@Identity use is concerned you might also want to take a look at this:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply