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">
</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">
</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
