How to insert multiple rows of one user with common ID (primary key) into SQL Server database?

Question

I want to take educational qualification records in my Asp.net form. As I have to take the record of class 10th, 12th, graduation, master. So I have created four rows for this... and 5 columns (year of exam, board, percentage, total mark, division).

Now I want to insert all rows in database with one button click by maintaining the primary key common for one user in all four records.

Please help me with the code (C#)

You can execute multiple insert queries. That's how you do it. — Chetan, Jul 22 '17 at 02:25
The problem is with multiple insert query is that... I have made studentID column a primary key and also identify key . And with multiple insert query it gets changed every time and it causes duplicate values. You can also suggest me another approach to get to my solution. Thank you — Danish, Jul 22 '17 at 02:41
You should have student table with primary key separate and use it as foreign key in studentqualification table. That way you don't face this problem. Its better if you can share your code and table structure so that proper solution of your problem can be provided. — Chetan, Jul 22 '17 at 02:47
Actually I m trying to upload my code and image of my form..but every time stack overflow app crashes. Until then...I will try to implement your idea by creating two table as you said. Thank you — Danish, Jul 22 '17 at 03:02
You might not need more than one table, depending on the intent of the table that you are currently working with. To clarify, I understand that there are six columns total: (StudentId, YearOfExam, Board, Percentage, TotalMark, Division). Is this correct? — Rob Hill, Jul 22 '17 at 03:40
#robhill.. Yes u are correct. So how can I insert 4 rows of data of a single user with studentId as a primary key ? If you have any logic then please suggest me. — Danish, Jul 22 '17 at 03:48
Posting code as part of the original question will be good enough. Don't post code as image. Put it in the question and format it. That will be fine. — Chetan, Jul 22 '17 at 03:49

score 1 · Answer 1 · answered Jul 22 '17 at 04:07

You may want to look at using a Composite Primary Key. This is a Primary Key that uses multiple columns to compose a single key. There are arguments for and against this strategy. See: What are the pros and cons of using multi column primary keys?

As an example, if your table looks like this:

CREATE TABLE [dbo].[StudentExam]
(
    [StudentId] INT NOT NULL PRIMARY KEY, 
    [Year] INT NOT NULL, 
    [Board] SOMEDATATYPE NOT NULL, 
    [Percentage] FLOAT NOT NULL, 
    [TotalMark] INT NOT NULL, 
    [Division] SOMEDATATYPE NOT NULL, 
)

You can alter the schema to look like this instead:

CREATE TABLE [dbo].[StudentExam]
(
    [StudentId] INT NOT NULL, 
    [Year] INT NOT NULL, 
    [Board] SOMEDATATYPE NOT NULL, 
    [Percentage] FLOAT NOT NULL, 
    [TotalMark] INT NOT NULL, 
    [Division] SOMEDATATYPE NOT NULL, 
    CONSTRAINT [PK_StudentExam] PRIMARY KEY ([StudentId], [Year])
)

By doing this, you are declaring that for any given row in this table, it is uniquely identified by the Student and Year together. You can still query on just the student, or just the year, but only together will they identify a row.

For more information on primary keys, see Create Primary Keys

Snack'Eyes · Answer 2 · 2017-07-22T06:49:29.700

Create Table Type in Sql

 CREATE TYPE [dbo].[TypeName] AS TABLE(
[name1] [varchar](1000) NULL,
[name2] [varchar](1000) NULL,
[name3] [varchar](max) NULL
)
GO

Create Procedure in SQL :

 ALTER PROCEDURE [dbo].[InsertData]
 @TableType TypeName readonly
 AS 

   INSERT INTO [dbo].[Table_Master]
   (Tname1,Tname2,Tname3)
    select name1,name2,name3 from @TableType

Then Go To Code Behind

        OpenConnection();
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.CommandText = spName;
        sqlcmd.Connection = sqlconn;

        SqlParameter tvpParam = sqlcmd.Parameters.AddWithValue("@Type", Your 
        Datatable); 
        tvpParam.SqlDbType = SqlDbType.Structured; 
        SqlParameter returnParameter = sqlcmd.Parameters.Add("RetVal", 
         SqlDbType.Int);
        returnParameter.Direction = ParameterDirection.ReturnValue;
        sqlcmd.ExecuteNonQuery();

        int Result = (int)returnParameter.Value;

        sqlcmd.Dispose();
        return Result;

Pass your DT in Uper Code...It Will Work Completely

How to insert multiple rows of one user with common ID (primary key) into SQL Server database?

2 Answers2