April 25, 2012 at 7:21 pm
Note: The title of this forum is (I can not change the title anymore 🙁 )
How to transfer data (from one column) from one table to another. Please help.
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. PERSON, it has four columns:
person_id (int, PK, identity(1,1))
last_name (nvarchar(50))
first_name (nvarchar(50))
middle_name (nvarchar(50))
2. STUDENT, it has three columns:
id (int, PK, identity(1,1))
person_id(int,FK)
student_number( nvarchar(20) )
(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:32 pm
marksquall (4/25/2012)
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. PERSON, it has four columns:
person_id (int, PK, identity(1,1))
last_name (nvarchar(50))
first_name (nvarchar(50))
middle_name (nvarchar(50))
2. STUDENT, it has three columns:
id (int, PK, identity(1,1))
person_id(int,FK)
student_number( nvarchar(20) )
(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
Both INSERT statements have problems.
This one:
INSERT INTO [PERSON] VALUES (@ln,@fn,@mn)
It is attempting to insert 3 values into a table that is expecting 4 values. The first column of your table is an integer field defined with the Identity property. You have to account for this column in your insert statement. You do this by explicitly naming the columns that you are inserting values into in the insert statement.
This one:
INSERT INTO [STUDENT] VALUES (@num)
Has a similar issue. The first column is also defined with the identity property. However, you are also only inserting one value. You need to identify that column into which the value is being inserted. You have a third column in this table. If it does not accept null values and does not have a default value defined, you must enter a value into it as well.
Best practices when working with INSERT statements is to full define what columns you are inserting data. The order of the column definitions does not have to match the order in which the columns are defined in the table, but they must match the order of the values (either a values clause or select statement).
April 25, 2012 at 8:06 pm
Silver spoon time:
CREATE PROCEDURE ADDSTUDENT
@num NVARCHAR(20),
@fn NVARCHAR(50),
@mn NVARCHAR(50),
@ln NVARCHAR(50)
AS
BEGIN
DECLARE @IDs table (id int);
SET NOCOUNT ON
INSERT INTO [PERSON](last_name,first_name,middle_name)
OUTPUT INSERTED.person_id into @IDs
VALUES (@ln,@fn,@mn);
INSERT INTO [STUDENT](person_id)
SELECT id FROM @IDs;
END
The first insert should work no problems. I'm not sure about the second as I am not sure of the complete definition of the third column. I don't know if it allows NULL values or if it has a default value.
April 26, 2012 at 6:46 am
I apologise, Lynn, but my inner pedant insists on butting in here.
The first column of your table is an integer field defined with the Identity property. You have to account for this column in your insert statement
You don't have to account for identity columns unless you're specifying the other columns. So this:-
INSERT INTO [PERSON] VALUES (@ln,@fn,@mn)
will work as long as there are three non identity columns in the table in the right order. I believe (though the memory grows dim and I haven't double checked) that it doesn't matter where in the column order the identity field is; just as long as removing the identity fields would leave the remaining fields in the same order as the values in the insert.
That said this
Best practices when working with INSERT statements is to full define what columns you are inserting data.
is absolutely true and soooo important that the above issue becomes moot. You should always be specifying column lists fully in any sql statement.
I'm actually a little ashamed of my inner pedant right now but he has a very loud voice I'm afraid.:-)
April 26, 2012 at 8:46 am
FunkyDexter (4/26/2012)
I apologise, Lynn, but my inner pedant insists on butting in here.The first column of your table is an integer field defined with the Identity property. You have to account for this column in your insert statement
You don't have to account for identity columns unless you're specifying the other columns. So this:-
INSERT INTO [PERSON] VALUES (@ln,@fn,@mn)
will work as long as there are three non identity columns in the table in the right order. I believe (though the memory grows dim and I haven't double checked) that it doesn't matter where in the column order the identity field is; just as long as removing the identity fields would leave the remaining fields in the same order as the values in the insert.
That said this
Best practices when working with INSERT statements is to full define what columns you are inserting data.
is absolutely true and soooo important that the above issue becomes moot. You should always be specifying column lists fully in any sql statement.
I'm actually a little ashamed of my inner pedant right now but he has a very loud voice I'm afraid.:-)
lol True. The main ssue here ws the second insert which only supplied 1 non-identity value and it should have had 2. Again, though, should qualify the insert columns.
Jared
CE - Microsoft
April 26, 2012 at 8:51 am
SQLKnowItAll (4/26/2012)
FunkyDexter (4/26/2012)
I apologise, Lynn, but my inner pedant insists on butting in here.The first column of your table is an integer field defined with the Identity property. You have to account for this column in your insert statement
You don't have to account for identity columns unless you're specifying the other columns. So this:-
INSERT INTO [PERSON] VALUES (@ln,@fn,@mn)
will work as long as there are three non identity columns in the table in the right order. I believe (though the memory grows dim and I haven't double checked) that it doesn't matter where in the column order the identity field is; just as long as removing the identity fields would leave the remaining fields in the same order as the values in the insert.
That said this
Best practices when working with INSERT statements is to full define what columns you are inserting data.
is absolutely true and soooo important that the above issue becomes moot. You should always be specifying column lists fully in any sql statement.
I'm actually a little ashamed of my inner pedant right now but he has a very loud voice I'm afraid.:-)
lol True. The main ssue here ws the second insert which only supplied 1 non-identity value and it should have had 2. Again, though, should qualify the insert columns.
Can't find the thread, but another poster had the same issue with a table having an identity column. Solution there was to enumerate all the columns.
I guess I just always enumerate my columns so I don't have to worry about the underlying structure of the table changing (such as columns changing position due to datatype changes, dropping/adding columns, etc). I seen code break due to things changing and developers being lazy and dependent on how things have been declared.
April 26, 2012 at 8:53 am
Lynn Pettis (4/26/2012)
SQLKnowItAll (4/26/2012)
FunkyDexter (4/26/2012)
I apologise, Lynn, but my inner pedant insists on butting in here.The first column of your table is an integer field defined with the Identity property. You have to account for this column in your insert statement
You don't have to account for identity columns unless you're specifying the other columns. So this:-
INSERT INTO [PERSON] VALUES (@ln,@fn,@mn)
will work as long as there are three non identity columns in the table in the right order. I believe (though the memory grows dim and I haven't double checked) that it doesn't matter where in the column order the identity field is; just as long as removing the identity fields would leave the remaining fields in the same order as the values in the insert.
That said this
Best practices when working with INSERT statements is to full define what columns you are inserting data.
is absolutely true and soooo important that the above issue becomes moot. You should always be specifying column lists fully in any sql statement.
I'm actually a little ashamed of my inner pedant right now but he has a very loud voice I'm afraid.:-)
lol True. The main ssue here ws the second insert which only supplied 1 non-identity value and it should have had 2. Again, though, should qualify the insert columns.
Looking for it... It was yesterday 🙂
Can't find the thread, but another poster had the same issue with a table having an identity column. Solution there was to enumerate all the columns.
I guess I just always enumerate my columns so I don't have to worry about the underlying structure of the table changing (such as columns changing position due to datatype changes, dropping/adding columns, etc). I seen code break due to things changing and developers being lazy and dependent on how things have been declared.
Looking for it... It was yesterday 🙂
Jared
CE - Microsoft
April 26, 2012 at 9:03 am
I guess I just always enumerate my columns so I don't have to worry about the underlying structure of the table changing (such as columns changing position due to datatype changes, dropping/adding columns, etc). I seen code break due to things changing and developers being lazy and dependent on how things have been declared.
Yeah me too. Then, to cover it up, someone brings in a policy that you can't remove unwanted columns and you can only add new columns to end of a structure and the cruft spirals from there...:crazy:
Best Practices and generally Best Practices for a reason.
April 26, 2012 at 9:32 am
To all:
Thank you for the added information. 😀
I will take note of your ideas, tips and suggestions for better construction of my stored procedures. :satisfied:
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply