Copying data from multiple tables into another table using C# and VB.Net in ASP.Net

Refer the below sample code for your reference. Here created one stored procedure which retrieve the table 1 data using cursor and then check each record of table1 with table 2 data to get table 2 column values then it check if record exist in table 3 or not if exist it update records in table 3 else insert in table 3 as per Id and rest column values.

SQL

CREATE Table Table1(ID INT,Name VARCHAR(30),Age INT)
INSERT INTO Table1
SELECT 01,'luke',27
UNION ALL
SELECT 02,'Rita',15
UNION ALL
SELECT 03,'Rick',14

CREATE Table Table2(ID INT,Name VARCHAR(20),Sex VARCHAR(10))
INSERT INTO Table2
SELECT 01,'luke','Male'
UNION ALL
SELECT 02,'Rita','Female'
UNION ALL
SELECT 03,'Rick','Male'

CREATE TABLE Table3(ID INT,Name VARCHAR(30),Age INT,Sex VARCHAR(10))


CREATE PROCEDURE CopyTable1AndTable2Data
AS
BEGIN
      SET NOCOUNT ON;
             
      --DECLARE THE VARIABLES FOR HOLDING DATA.
      DECLARE @Id INT
      DECLARE @Name VARCHAR(30)
      DECLARE @Age INT

      --DECLARE AND SET COUNTER.
      DECLARE @Counter INT
      SET @Counter = 1
 
      --DECLARE THE CURSOR FOR A QUERY.
      DECLARE CopyData CURSOR READ_ONLY
      FOR
      SELECT Id, Name, Age
      FROM Table1
 
      --OPEN CURSOR.
      OPEN CopyData
 
      --FETCH THE RECORD INTO THE VARIABLES.
      FETCH NEXT FROM CopyData INTO
      @Id, @Name, @Age
 
      --LOOP UNTIL RECORDS ARE AVAILABLE.
      WHILE @@FETCH_STATUS = 0
      BEGIN             
             DECLARE @Sex VARCHAR(10) 
             --PRINT CURRENT RECORD
             SET @Sex = (SELECT Sex FROM Table2 WHERE Id = @Id)

             -- Check If User details already exist by Id
             IF EXISTS(SELECT Id FROM Table3 WHERE Id = @Id)
                 BEGIN
                      -- if user Details exist then Updated it By Id
                      UPDATE Table3 
                      SET ID = @Id 
                          ,Name = @Name
                          ,Age = @Age
                          ,Sex = @Sex
                      WHERE Id = @Id
                 END
             ELSE
                 BEGIN
                    -- If new record then Insert It
                    INSERT INTO Table3(Id,Name,Age,Sex) 
                    VALUES (@Id,@Name,@Age,@Sex)
                 END           
    
             --INCREMENT COUNTER.
             SET @Counter = @Counter + 1
 
             --FETCH THE NEXT RECORD INTO THE VARIABLES.
             FETCH NEXT FROM CopyData INTO
             @Id, @Name, @Age
      END
 
      --CLOSE THE CURSOR.
      CLOSE CopyData
      DEALLOCATE CopyData
END
GO

HTML

<div>
    <table>
        <tr>
            <td>
                Table 1
            </td>
            <td>
                Table 2
            </td>
        </tr>
        <tr>
            <td>
                <asp:GridView ID="gvTable1" runat="server">
                </asp:GridView>
            </td>
            <td>
                <asp:GridView ID="gvTable2" runat="server">
                </asp:GridView>
            </td>
        </tr>
        <tr>
            <td colspan="2">
                &nbsp;
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:Button ID="btnCopy" runat="server" Text="Copy Table 1 and Table 2 Data in Table 3"
                    OnClick="Copy" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                &nbsp;
            </td>
        </tr>
        <tr>
            <td colspan="2">
                Table 3
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:GridView ID="gvTable3" runat="server">
                </asp:GridView>
            </td>
        </tr>
    </table>
    <br />
    <br />
</div>

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        PopulateGrid();
    }
}

protected void Copy(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["ConStr2"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("CopyTable1AndTable2Data", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;               
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    this.PopulateGrid();
}
 

private void PopulateGrid()
{
    this.gvTable1.DataSource = GetData("Select * From Table1");
    this.gvTable1.DataBind();

    this.gvTable2.DataSource = GetData("Select * From Table2");
    this.gvTable2.DataBind();

    this.gvTable3.DataSource = GetData("Select * From Table3");
    this.gvTable3.DataBind();
}

private DataTable GetData(string query)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["ConStr2"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {

        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                sda.Fill(dt);
            }
        }
        return dt;
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        PopulateGrid()
    End If
End Sub

Protected Sub Copy(ByVal sender As Object, ByVal e As EventArgs) Handles btnCopy.Click
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConStr2").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("CopyTable1AndTable2Data", con)
            cmd.CommandType = CommandType.StoredProcedure
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Me.PopulateGrid()
End Sub

Private Sub PopulateGrid()
    Me.gvTable1.DataSource = GetData("Select * From Table1")
    Me.gvTable1.DataBind()
    Me.gvTable2.DataSource = GetData("Select * From Table2")
    Me.gvTable2.DataBind()
    Me.gvTable3.DataSource = GetData("Select * From Table3")
    Me.gvTable3.DataBind()
End Sub

Private Function GetData(ByVal query As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConStr2").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                sda.SelectCommand = cmd
                sda.Fill(dt)
            End Using
        End Using

        Return dt
    End Using
End Function

Screenshot