Results 1 to 5 of 5

Thread: Sql insert into

  1. #1
    Join Date
    Jun 2008

    Unanswered: Sql insert into


    I have 3 tables: Movimentos, Compras, Vendas

    The table Movimentos stores all the data about buying and selling stocks.
    When i want to sell some stock, my code do the following:
    1- Selects the stocks i want from Movimentos and copy all the records to table Compras and delete them from table Movimentos;
    2 - It make some calculations in table Compras and changes the values;
    3 - Write some records (one or more) in table Vendas;
    4 - It copies all the calculated record in table Compras and Vendas to Table Movimentos;
    5 - Deletes all records in table Compras and Vendas.

    I wrote the code but i have some problems with the table's key fields.

    Private Sub Comando29_Click()
    Dim db As DAO.Database
    Dim rst_mov, rst_compr, rst_vend As Recordset
    Dim strNome As String
    Dim ncompr, nvend, i, j As Integer
    strNome = Me.Nome.Value
    Set db = CurrentDb
    db.Execute "DELETE * FROM Movimentos WHERE Movimentos.Nome= '" & strNome & "' And Movimentos.Operacao = 'Compra' And Movimentos.Quantidade_Rest>0"
    db.Execute "INSERT INTO Movimentos SELECT Nome,Data,Operacao,Quantidade,Quantidade_Rest,Valor_Unitario,Comissoes,Validar,Total,Valias FROM Compras"
    db.Execute "INSERT INTO Movimentos SELECT Nome,Data,Operacao,Quantidade,Quantidade_Rest,Valor_Unitario,Comissoes,Validar,Total,Valias FROM Vendas"
    db.Execute "DELETE * FROM Compras"
    db.Execute "DELETE * FROM Vendas"
    Me.Texto0.Value = ""
    Me.Texto2.Value = ""
    Me.Texto6.Value = ""
    Me.Texto8.Value = ""
    End Sub
    Any suggestions!?

    Thank you for your time...

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Try to specify the names of the field in the destination table:
    INSERT INTO Table1 ( Field1, Field2, Field3, etc... ) SELECT ... etc.
    Also, be careful with the primary key (if any) of the destination table.

    Another solution (perhaps easier in this case as you delete everything from the destination table first), would be to use an Instruction SELECT...INTO.
    Have a nice day!

  3. #3
    Join Date
    Jun 2008
    Thank you Sinndho.
    I'll try your suggestions.
    SELECT INTO works like a cut paste? It that it?

  4. #4
    Join Date
    Jun 2008
    OK... i tried that but it seems that the primary key in table Movimentos is causing the problem.
    How can i assure that when the record is copied to table Movimentos it doesn't delete an existent one?

  5. #5
    Join Date
    Mar 2009
    Provided Answers: 14
    As you transfer data from two tables to a new one (or one that is emptied before the transfer operation) SELECT INTO allows you to do that in a single operation. From the SQL Server Help file:
    The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.

    The structure of the new table is defined by the attributes of the expressions in the select list. The following example creates the table dbo.EmployeeAddresses by selecting seven columns from various employee and address-related tables.

    SELECT c.FirstName, c.LastName, e.Title, a.AddressLine1, a.City, sp.Name AS [State/Province], a.PostalCode
    INTO dbo.EmployeeAddresses
    FROM Person.Contact AS c
    JOIN HumanResources.Employee AS e ON e.ContactID = c.ContactID
    JOIN HumanResources.EmployeeAddress AS ea ON ea.EmployeeID = e.EmployeeID
    JOIN Person.Address AS a on a.AddressID = ea.AddressID
    JOIN Person.StateProvince as sp ON sp.StateProvinceID = a.StateProvinceID;
    It's possible that Access can only use a subset of the full SQL Server syntax, though.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts