Insert into select problems c#

Question

I have a C# program from which I want to execute

Insert into table 
   Select * from table2

Here's the code I have

using (SqlConnection conn = new SqlConnection(ConfigurationManager
                            .ConnectionStrings["SQLConnectionString2"].ConnectionString))
{
    conn.Open();

    string selectStmt = "INSERT INTO [TestDB].[dbo].[Test] (ID, Name, CreatedDate) " +
                        "SELECT TOP 10 [ID], [Name], [CreatedDate] FROM [TestDB].[dbo].[TempOrig]";

    using (SqlCommand cmd2 = new SqlCommand(selectStmt, conn))
    {
        int resultValue = cmd2.ExecuteNonQuery();
        Console.WriteLine(resultValue.ToString());
    }

    conn.Close();
}

I do not see any rows being insert to [TestDB].[dbo].[Test] and neither there is an error.

I am not sure what is happening.

Thanks MR

Another victim of |DataDirectory| shortcut in the connectionstring? What is the value of _resultValue_? — Steve, Jul 24 '16 at 11:20
Debug the program. Is any exception being thrown? If not there is a problem with the SQL code or the Database itself. — Pau C, Jul 24 '16 at 11:20
At least put a ";" between the 2 sql command in selectStmt var so you respect sql syntax — user2543740, Jul 24 '16 at 11:22
@user2543740 No need. It is a _one_ command actually. http://stackoverflow.com/a/25971/447156 — Soner Gönül, Jul 24 '16 at 11:23
Does 'TempOrig' table contains something? if yes, then try getting query from 'selectStmt ' variable and execute it directly in SSMS. You might find something — Mohammed Dawood Ansari, Jul 24 '16 at 11:28
@user2543740, it's a `insert into .. select from` construct and no need of `;` therefore. — Rahul, Jul 24 '16 at 11:40
When I execute "insert into select" in the sql server management studio, 10 rows get inserted into [TestDB].[dbo].[Test]. And also resultValue=0. — user2726975, Jul 25 '16 at 12:55

score 1 · Answer 1 · answered Jul 24 '16 at 12:44

You problem is that you are only inserting rows, Your sql query isn't returning any rows, it is a simple insert.

But if you used OUTPUT clause with your insert you will be able to see the rows that were inserted.

Another thing to watch out is that a table will be returned by the OUTPUT clause in your SQL query, so you cannot use the int resultValue variable to capture the results, you will need a DataTable object.

Things to do:

Modify the SQL Query to add OUTPUT clause.
Create a DataTable object.
Fill the DataTable object with the result set returned by the insert.
Finally read from DataTable object and display on the console.

string conString = "Server=ServerName;Database=DBName;User Id=UserName;Password =********; ";
string sqlcmd = " INSERT INTO [TestDB].[dbo].[Test] (ID, Name, CreatedDate) "
              + " OUTPUT inserted.ID, inserted.Name, inserted.CreatedDate "
              + " SELECT TOP 10 [ID], [Name], [CreatedDate] FROM [TestDB].[dbo].[TempOrig] ";

using (SqlConnection con = new SqlConnection(conString))
{
    using (SqlCommand cmd = new SqlCommand(sqlcmd, con))
    {
        con.Open();

        // fill in the DataTable object
        DataTable dt = new DataTable();
        using (var insertedOutput = cmd.ExecuteReader())
        {
            dt.Load(insertedOutput);
        }


        // Read from DataTable object and print on the console
        DataRow[] currentRows = dt.Select(null, null);

        if (currentRows.Length < 1)
            Console.WriteLine("No Current Rows Found");
        else
        {
            foreach (DataColumn column in dt.Columns)
                Console.Write("\t{0}", column.ColumnName);

            Console.WriteLine("\t");

            foreach (DataRow row in currentRows)
            {
                foreach (DataColumn column in dt.Columns)
                    Console.Write("\t{0}", row[column]);

               Console.WriteLine("\t");
            }
        }
        Console.ReadLine();
    }
}

I think you've misunderstood the question – Steve Jul 24 '16 at 13:38 — Steve, Jul 24 '16 at 13:38
You have misunderstood the question – user2726975 Jul 25 '16 at 12:56 — user2726975, Jul 25 '16 at 12:56

Insert into select problems c#

1 Answers1