Insert table from same name scheme and table but different path

Question

I want to merge two same name scheme and table but different path. I expected it works but not.

It return error 'System.Data.SqlClient.SqlException: 'Incorrect syntax near 'C:'. Incorrect syntax near 'C:'. The label 'C' has already been declared. Label names must be unique within a query batch or stored procedure.''

private void Merge_Click(object sender, RoutedEventArgs e) {
        string x = // same name scheme but different path 1
                   // c:\dbdata\b.mdf
                   Files.Items[0].ToString(); //from listbox 1
        string y = // same name scheme but different path 2
                   // c:\dbdata\a\b.mdf
                   Files2.Items[Files2.SelectedIndex].ToString(); //from listbox 2

        string connect = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + x + ";Integrated Security=True;Connect Timeout=30";
        string connect2 = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + y + ";Integrated Security=True;Connect Timeout=30;Pooling=False;";

        string insert = "INSERT INTO " + x + ".dbo.item" + 
            " (id, name)" +
        // same table name 1
        " SELECT id, name" +
        "FROM " + y + ".dbo.item"; //same table name 2
        string q = @"select * from dbo.item ";

        using (SqlConnection connection = new SqlConnection(connect))
        {
            connection.Open();
            using (SqlConnection connection2 = new SqlConnection(connect2))
            {
                connection2.Open();

                SqlCommand commandinsert = new SqlCommand(insert, connection);
                var temp = commandinsert.ExecuteReader();                    

            }
        }

when i replace " SELECT id, name" + "FROM " + y + ".dbo.item" to "values (1, 'a')" and "INSERT INTO " + x + ".dbo.item" to "INSERT INTO dbo.item" it worked

Try using this example to create a single connection with one file, then attach the other to it: https://stackoverflow.com/questions/35700066/attach-database-programmatically then you can just refer to them by db name in your query. — Ben, Jan 08 '19 at 04:09
@mjwills "INSERT INTO C:\\dbdata\\b.mdf.dbo.Crwitem (Item_market,Category,Item_id,Item_nm,Item_price,Item_url,Manufactuer, Writeday) SELECT Item_market,Category,Item_id,Item_nm,Item_price,Item_url,Manufactuer, Writeday FROM C:\\dbdata\\a\\b.mdf.dbo.Crwitem" — JY Lee, Jan 08 '19 at 04:33
`C:\\dbdata\\b.mdf.dbo.Crwitem` is not a valid table name. I suspect you meant to use `dbo.Crwitem`. — mjwills, Jan 08 '19 at 04:34
@mjwills I knew '\\' syntax will not return error so i tried to replace '\\' to '\' with string replace method and regex etc. but it was not worked — JY Lee, Jan 08 '19 at 04:41
https://stackoverflow.com/questions/19386585/copy-data-between-two-sql-server-databases-in-c-sharp may be interest. — mjwills, Jan 08 '19 at 04:51

score 1 · Accepted Answer · answered Jan 08 '19 at 04:51

1

The problem is you are not using connection2 anywhere in your insert statement, you are using only connection to first database.

You can use SQL Bulk Copy class here. You can find a nice tutorial here.

answered Jan 08 '19 at 04:51

Gaurav

782
5
12

Insert table from same name scheme and table but different path

1 Answers1