none
insert into table from dataset

    Question

  • hi
    i have a dataset and fill it with from the first table  ,how to insert the data to another table in database

    the code i use to fill dataset lik this

    System.Data.

    DataSet ds1;
    const string ConnStringfrom = "Provider=SQLOLEDB.1;Data Source=.;Integrated Security=SSPI;Initial Catalog=Northwind";
    const string ConnStringto = "Provider=SQLOLEDB.1;Data Source=.;Integrated Security=SSPI;Initial Catalog=testtime";
    const string sqlSelect="SELECT top 10 ProductId, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, GETDATE() as Extracted from Products order by UnitPrice";

    const
    string sqlInsert = "INSERT INTO Products ( ProductId,ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Extracted) VALUES (@ProductId, @ProductName, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @Extracted)";

    private void FromSql()

    {

    var ConnSql = new System.Data.OleDb.OleDbConnection(ConnStringfrom);
    var da1 = new System.Data.OleDb.OleDbDataAdapter();
    da1.SelectCommand=
    new System.Data.OleDb.OleDbCommand(sqlSelect);
    da1.SelectCommand.Connection= ConnSql;
    ds1=
    new System.Data.DataSet();
    da1.Fill(ds1,
    "Extracto");

    }

    private void tosql()

    {

     ؟؟؟؟

    }



    thanks 

    Monday, April 20, 2009 7:07 PM

Answers

  • I think you shoud create another dataAdapter and dataset for the second database, Fill the second dataSet
     
    adapter2.Fill(ds2,"Products");
    ds2 = ds1;

    adapter2.Update(ds2,"Products");


    I hope that will help you
    • Marked as answer by Bahgat_Mashaly Wednesday, April 22, 2009 12:29 AM
    Tuesday, April 21, 2009 7:13 AM
  • Maybe I am missunderstanding things.
    But if all you want to do is to move data from one table to another table. Why go via DataSets etc.

    You can do it directly in T-SQL, either create the destination table on the fly, or add to an existing one:

    -- Table to move data FROM
    create table FromTable (id int primary key, fname nvarchar(20), lname nvarchar(20))
    -- Some example rows.
    insert into FromTable values (1, 'aaa', 'bbb')
    insert into FromTable values (2, 'ccc', 'ddd')
    insert into FromTable values (3, 'eee', 'fff')
    
    -- 1. This will create the ToTable
    select top (2) id, fname, lname
    into ToTable
    from FromTable
    
    -- 2. This will add rows to the table if it already exists.
    create table ToTable (id int primary key, fname nvarchar(20), lname nvarchar(20))
    
    insert into ToTable
    select top (2) id, fname, lname 
    from FromTable
    HTH
    //Michael
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Bahgat_Mashaly Wednesday, April 22, 2009 12:29 AM
    Tuesday, April 21, 2009 9:04 AM

All replies

  • I think you shoud create another dataAdapter and dataset for the second database, Fill the second dataSet
     
    adapter2.Fill(ds2,"Products");
    ds2 = ds1;

    adapter2.Update(ds2,"Products");


    I hope that will help you
    • Marked as answer by Bahgat_Mashaly Wednesday, April 22, 2009 12:29 AM
    Tuesday, April 21, 2009 7:13 AM
  • Maybe I am missunderstanding things.
    But if all you want to do is to move data from one table to another table. Why go via DataSets etc.

    You can do it directly in T-SQL, either create the destination table on the fly, or add to an existing one:

    -- Table to move data FROM
    create table FromTable (id int primary key, fname nvarchar(20), lname nvarchar(20))
    -- Some example rows.
    insert into FromTable values (1, 'aaa', 'bbb')
    insert into FromTable values (2, 'ccc', 'ddd')
    insert into FromTable values (3, 'eee', 'fff')
    
    -- 1. This will create the ToTable
    select top (2) id, fname, lname
    into ToTable
    from FromTable
    
    -- 2. This will add rows to the table if it already exists.
    create table ToTable (id int primary key, fname nvarchar(20), lname nvarchar(20))
    
    insert into ToTable
    select top (2) id, fname, lname 
    from FromTable
    HTH
    //Michael
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Bahgat_Mashaly Wednesday, April 22, 2009 12:29 AM
    Tuesday, April 21, 2009 9:04 AM
  • thanks for help
    Wednesday, April 22, 2009 12:30 AM