如何将两个表中的值插入第三个表中？ [英] How can I insert values from two tables into the third one?

问题描述

大家好，



我目前正在努力将一些值从寄存器表单插入到数据库中。我有2个表，我想在其中插入数据：RegisterTeacher和TeacherCourses.Now，我在这里遇到了逻辑问题，虽然我已经通过互联网搜索了不同的可能性。这些是表格：

Hello everyone,

I'm currently struggling with inserting some values from the register form into the database.I have 2 tables in which I want to insert data:RegisterTeacher and TeacherCourses.Now,I have a problem with the logic here,although I have searched over the internet to see different possibilities of doing this.These are the tables:

CREATE TABLE [dbo].[RegisterTeacher] (
    [SNTeacher] INT            NOT NULL,
    [UserName]  NVARCHAR (50)  NOT NULL,
    [pwd]       INT            NOT NULL,
    [fullName]  NVARCHAR (MAX) NOT NULL,
    [education] NVARCHAR (50)  NULL,
    PRIMARY KEY CLUSTERED ([SNTeacher] ASC)
);

CREATE TABLE [dbo].[AvailableCourses] (
    [courseName] NVARCHAR (MAX) NOT NULL,
    [education]  NVARCHAR (50)  NOT NULL,
    [ClassID]    NVARCHAR (50)  NULL,
    [courseID]   INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([courseID] ASC)
);

CREATE TABLE [dbo].[TeacherCourses] (
    [courseID]  INT        NOT NULL,
    [SNTeacher] INT        NOT NULL,
    [IDKey]     NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([IDKey] ASC),
    CONSTRAINT [FK_TeacherCourses_ToTable] FOREIGN KEY ([SNTeacher]) REFERENCES [dbo].[RegisterTeacher] ([SNTeacher]),
    CONSTRAINT [FK_TeacherCourses_ToTable_1] FOREIGN KEY ([courseID]) REFERENCES [dbo].[AvailableCourses] ([courseID])
);

现在我要做的是以下内容：对于RegisterTeacher表，它适用于保存值，因为我这样做：

Now what i what to achieve is the following:For the RegisterTeacher table it works to save the values,as I am doing it like this:

connectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Users\\User\\Desktop\\BackupVersion\\LastAndFinalVersion\\DatabaseStudents.mdf;Integrated Security=True";
          using (SqlConnection con = new SqlConnection(connectionString))
          {
              sql = "Insert into RegisterTeacher(SNTeacher,UserName,pwd,fullName,education)values(@SNTeacher,@UserName,@pwd,@fullName,@education)";

              con.Open();
              using (SqlCommand cmd = new SqlCommand(sql, con))
              {
                  cmd.Parameters.AddWithValue("@SNTeacher", SqlDbType.Int).Value=SNTeacher;
                  cmd.Parameters.AddWithValue("@UserName", SqlDbType.NVarChar).Value = UserName;
                  cmd.Parameters.AddWithValue("@pwd", SqlDbType.Int).Value = pwd ;
                  cmd.Parameters.AddWithValue("@fullName", SqlDbType.NVarChar).Value = fullName ;
                  cmd.Parameters.AddWithValue("@education", SqlDbType.NVarChar).Value=education;
                  cmd.ExecuteNonQuery();
              }

我的问题是当我想将courseName保存到表TeacherCourses.I想要保存SNTeacher和courseName-其中courseName取自AvailableCourses并使用courseC引用传递给TeacherCourses表以及来自RegisterTeacher表的SNTeacher。语法如何？



什么我试过了：



我尝试的是以下内容：我已经单独查询将数据插入到TeacherCourses中：

My problem is when I want to save the courseName into the table TeacherCourses.I would like to both save the SNTeacher and courseName-where courseName is taken from AvailableCourses and passed with the courseID reference to the TeacherCourses table along with SNTeacher from RegisterTeacher table.How would the syntax look like?

What I have tried:

What I have tried is the following:I have made a separate query to insert data into TeacherCourses:

using (SqlCommand com = new SqlCommand(query, con))
                      {
                  query = "Insert into TeacherCourses (courseID,SNTeacher) select courseID,SNTeacher from RegisterTeacher p inner join AvailableCourses c ON p.courseID=c.courseID  AND p.SNTeacher=c.SNTeacher";
                  SelectedCourses = new ObservableCollection<AvailableCours>();
                  foreach (var item in Courses)
                  {

                      if (item.IsChecked)
                      {
                          SelectedCourses.Add(item);

                      }
                      com.Parameters.AddWithValue("@courseName", SqlDbType.NVarChar).Value = item.courseName;

                      com.ExecuteNonQuery();

推荐答案

尝试这样的事情：

Try something like this:

using (SqlCommand com = new SqlCommand("", con))
{
    var query = new System.Text.StringBuilder();
    query.Append("INSERT INTO TeacherCourses (courseID, SNTeacher) SELECT courseID, @SNTeacher FROM AvailableCourses WHERE courseName IN (");
    
    bool started = false;
    foreach (var item in Courses)
    {
        if (item.IsChecked)
        {
            string name = "@courseName" + com.Parameters.Count;
            com.Parameters.AddWithValue(name, item.courseName);
            
            if (started) query.Append(',');
            query.Append(name);
            started = true;
        }
    }
    
    if (started)
    {
        query.Append(')');
        com.CommandText = query.ToString();
        com.Parameters.AddWithValue("@SNTeacher", SNTeacher);
        com.ExecuteNonQuery();
    }
}

这篇关于如何将两个表中的值插入第三个表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！