How to copy data from one table to another table in SQL database using C#.Net

[maria_mj]

I have problem to copy data from one table to another table in SQL database. I am develop the system using C#.Net. I have searching about it from internet, but it not works.
I have two table:
1) Project_Pipeline (company_name, project_name, project_revenue, gross_profit, category_pipeline, status_pipeline, bde_name, deadline, remark, pipeline_id)

2)Proposal_listing (proposal_no, project_name, company_name, start_date, end_date, duration, revenue, status_pipeline, proposal_id)

I want to copy data from Project_Pipeline table to Proposal_listing table where status_pipeline is equal to "Proposal Phase".

I have try the following code, but it didn't works...

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace BDAS
{
    public partial class ViewPipeline : System.Web.UI.Page
    {
        DBConn myDB = new DBConn();
        SqlConnection SQLConn = new SqlConnection();
        
        protected void Page_Load(object sender, EventArgs e)
        {
            
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            String company_name;
            String project_name;
            String project_revenue;
            String gross_profit;
            String category_pipeline;
            String status_pipeline;
            String bde_name;
            String deadline;
            String remark;

            String copyData = INSERT INTO Proposal_listing(company_name
                , project_name
                , status_pipeline) SELECT (company_name
                , project_name
                , status_pipeline) FROM Project_Pipeline.BDAS WHERE status_pipeline = "Proposal Phase";
        }

    }
}

My SQL database name is BDAS.dbo

Hope that someone can help me...

[Sals]

Hey this is easy
Create a stored procedure which will copy the data
it would be like "Select * into dest_table from src_table where condition"
call this stored procedure in your code and pass the requirde parameters.

Let me know if you need more help

[maria_mj]

I have try to do as your suggestion. But there is an error.
Here is my code:

protected void btnSubmit_Click(object sender, EventArgs e)
        {
 SQLConn = myDB.OpenDB();
            String copyData = "SELECT* INTO Proposal_listing ( FROM Project_Pipeline WHERE status_pipeline = 'Proposal Phase';";
            SqlCommand cmd = new SqlCommand(copyData, SQLConn);
            int returnValue = cmd.ExecuteNonQuery();
            myDB.CloseDB();
        }

The error that occur are:
SqlException was unhandled by user code
There is already an object named 'Proposal_listing' in the database.

Actually, i only want copy certain data in that table.
For example:
In Project_Pipeline table, i want to copy data in column project_name, company_name, status_pipeline
and i want to copy the data to the Proposal_listing table in certain column (source-->destination):

(project_name-->project_name), (company_name-->company_name), (status_pipeline-->status_proposal).

[dnanetwork]

select * into yourbakuptable
from yourTable

[rohand]

No you can't use "select * into yourbackuptable from yourtable " , because it's only used when you don't have table created in your database and it will create the same table as "yourtable" with same table schema. If the table is exist then it gives the error...

As you want to insert data in few columns so you must have to go with "insert into yourbackuptable (column name) select columnname from yourtable where columnname= condition".
See the below modified code :
String copyData = "INSERT INTO Proposal_listing(company_name, project_name, status_pipeline) SELECT (company_name, project_name
, status_pipeline) FROM Project_Pipeline WHERE status_pipeline = 'Proposal Phase' ";

then execute the above query with the help SqlCommand.

I hope it will work for you.

[dnanetwork]

good option but it depends on requirement !

[maria_mj]

i don't know what is the problem... i have try everything that all of you have suggested. But, it still didn't work. Please help me to solve this problem... now, my project was stuck..

Here is my latest code..as suggested by Rohand..

protected void btnSubmit_Click(object sender, EventArgs e)
        {
SQLConn = myDB.OpenDB();

            String copyData = "INSERT INTO Proposal_listing(company_name, project_name, status_proposal) SELECT (company_name, project_name, status_pipeline) FROM Project_Pipeline WHERE status_pipeline = 'Proposal Phase' ";
            SqlCommand cmd = new SqlCommand(copyData, SQLConn);
            cmd.ExecuteNonQuery();

myDB.CloseDB();
 }

There is an error that occur at cmd.ExecuteNonQuery(); :
SqlException was unhandled by user code
Incorrect syntax near ','.

I don't know where to fix it. I have check the code for several time. But, I don't know where to fix it.

[rohand]

i don't know what is the problem... i have try everything that all of you have suggested. But, it still didn't work. Please help me to solve this problem... now, my project was stuck..

Here is my latest code..as suggested by Rohand..

protected void btnSubmit_Click(object sender, EventArgs e)
        {
SQLConn = myDB.OpenDB();

            String copyData = "INSERT INTO Proposal_listing(company_name, project_name, status_proposal) SELECT (company_name, project_name, status_pipeline) FROM Project_Pipeline WHERE status_pipeline = 'Proposal Phase' ";
            SqlCommand cmd = new SqlCommand(copyData, SQLConn);
            cmd.ExecuteNonQuery();

myDB.CloseDB();
 }

There is an error that occur at cmd.ExecuteNonQuery(); :
SqlException was unhandled by user code
Incorrect syntax near ','.

I don't know where to fix it. I have check the code for several time. But, I don't know where to fix it.

hey sorry one thing is the column name is wrong in insert statement, i have written "status_proposal" instead of "status_pipeline"...
I have modified the query a bit and have tested it on my dot net IDE and it's working fine. use the below modified query..

String copyData = "INSERT INTO Proposal_listing (company_name, project_name, status_pipeline) SELECT company_name, project_name, status_pipeline FROM Project_Pipeline WHERE status_pipeline = 'Proposal Phase' ";

regarding error Incorrect syntax near ','. . only for knowledge purpose, it was coming up because we have use parentheses "select ( column name )" in the Select clause. The solution is you can't use "( )" in Select clause at the time of inserting data from one table to another.

I hope it will help you and you will not stuck anymore.
mark it as solved if it fix your problem.

[maria_mj]

It works as i need. Thank you..

[Sangita13]

protected void linkbutton1_Click(object sender, EventArgs e)
{
c connect();
c = new connect();
c.insrt("insert into attendance(rollno,smartcardid,stuname,admittedcourse,corsesem) VALUES (select rollno,smartcardid,stuname,admittedcourse,corsesem from student)");

}

There is an error that occur at cmd.ExecuteNonQuery(); :
error near select.
Incorrect syntax near ')'.

plz solve the problem as soon as possible.

Can give me another option to solve this problem.
copy data from one table to another table using c# coding and database is in SQL SERVER 2000.

[dnanetwork]

this thread is Resolved. so start a new thread...!

[wria12]

CommandText property has not been initialized