none
how can i build SQL insert query it bring the value from Select query RRS feed

  • Question

  • hi all

    i am try to build SQL Insert Command takes the values From Select query  ,,

    the general  Sentence be like this :

    INSERT INTO table1
    (column-1, column-2)
    SELECT 
    (column-3, column-4)
    From table2

    pleas Guiding me build that in Linq to SQL .

    i am work on VB.net platform 

    thanks 




    • Edited by ebrahimZ Sunday, April 8, 2012 5:00 AM
    Sunday, April 8, 2012 4:56 AM

Answers

  • I thought that should work, but it keep telling : Cannot add an entity that already exists.

    Dim toinsert = From c In db.customers Where c.CustomerID = "ALFKI" Select   c 
    
    toinsert.First.CustomerID = "eeee"
    
    'db.customers.InsertAllOnSubmit(Of customer)(toinsert)
    
    db.customers.InsertOnSubmit(toinsert)
    
    db.SubmitChanges
    

    Can any one fix it or tell why?
    Tuesday, April 10, 2012 9:40 AM

All replies

  • In Linq to SQL, there's no such a insert command, you need to use call insertOnSubmit method to insert the record into context, then call SubmitChanges method to persist the record into database.

    Refer to this link. http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx


    Fighting like Allen Iverson. Neve give up!


    Sunday, April 8, 2012 9:14 AM
  • I thought that should work, but it keep telling : Cannot add an entity that already exists.

    Dim toinsert = From c In db.customers Where c.CustomerID = "ALFKI" Select   c 
    
    toinsert.First.CustomerID = "eeee"
    
    'db.customers.InsertAllOnSubmit(Of customer)(toinsert)
    
    db.customers.InsertOnSubmit(toinsert)
    
    db.SubmitChanges
    

    Can any one fix it or tell why?
    Tuesday, April 10, 2012 9:40 AM
  • I did more research & tests about this error, and as I found that when you query from a table and store the result in an object, that result hold that row in the same datacontext and whatever changes you made you are changing the row itself and cannot insert it because as a whole row, it exist. To get around this you have to SELECT and INSERT in different datacontext.

    For example let say we want to get a row from table customer which match a criteria, then change some fields in it then insert it as new row, here is my working code

    Dim db1 As New northwindDataContext
    Dim db2 As New northwindDataContext
    Dim qry_cus = From c In db1.Customers Where c.CustomerID = "ALFKI" Select c
                      
    qry_cus.First.CustomerID = "1234"
    qry_cus.First.CompanyName = "new_company"
    db2.Customers.InsertOnSubmit(qry_cus.First)
    db2.SubmitChanges()


    The above code works if the SELECT & INSERT are from the same table, but what if I had to SELECT from TABLE1 and INSERT into TABLE2?

    Here is an example using Employee & Customer in Northwind DB :

    INSERT INTO Customer (CustomerID, CompanyName)
    SELECT (EmployeeID, LastName) From Employees

    Dim db1 As New NorthwindDataContext
        
    Dim qry_emp = From emp In db1.Employees Select New With {emp.EmployeeID,emp.LastName}
    
    Dim qry_cus = From c In qry_emp Select New Customer With {.CustomerID = qry_emp.First.EmployeeID,.CompanyName = qry_emp.First.LastName}
        
    db1.Customers.InsertOnSubmit(qry_cus.First)
    db1.SubmitChanges

    You had to use Select New in order to make it work.

    Now I have one limitation in this, I can SELECT many rows but I can INSERT 1 row. umm I will do more research and see..



    Saturday, April 14, 2012 7:06 AM

  • Now I have one limitation in this, I can SELECT many rows but I can INSERT 1 row. umm I will do more research and see..



     mr.samir thank you to your answer and rely i need to insert multiple rows ,, so i hop to find way to do it . 
    Saturday, April 28, 2012 8:56 AM