how to move data from one table to another

[zo0oda]

hello,

I'm creating e-menu system for a restuarent... It has 3 users... and in staff user i want , when the staff see orders of the
custmer from StoreOrders1 table which will be displayed in DataGrideView ... i want a botton to move the records of StoreOrders1 to ReceiveOrder table..

and this the code that I tryed but I have error in it..

Imports System.Data.SqlClient
Imports System.IO
Imports System.Data
Imports System.Data.OleDb

Public Class Orders
Dim con As New SqlClient.SqlConnection()
Dim cmd As New SqlClient.SqlCommand()
Dim dataap As New SqlClient.SqlDataAdapter()
Dim ds As New DataSet()
Dim i, c As Integer
Dim dr As DataRow
Private strSQL As String = ""

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

    MsgBox("Order has been Recived")

    Dim strSQL As String = ""
    con.Open()


    strSQL = "select TableNo,FoodName, Quantity , Price INTO TableNo ,FoodName, Quantity , Price FROM StoreOrders4 WHERE TableNo = 4"



    cmd = New SqlClient.SqlCommand(strSQL, con)

    cmd.ExecuteNonQuery()
    con.Close()


End Sub
End Class

[debasisdas]

Why you need to use INTO clause in the SELECT statement ?
Elements after INTO clause needs to be variables and those needs to be declared in the current scope.

[Mitja Bonca]

You cannot simply MOVE data from table to table.
You can do it by regular steps:
1. SELECT value1, value2,... FROM tableName (if you want all: SELECT * FROM...)
2. INSERT INTO tableName VALUES (value1, value2,...)

This is how you can do it.

[zo0oda]

I tryed also to declare it , but same errore occur...
any way.. I will try to do again as Mr.Mitja said..

Thank you

[adam_k]

according to your description you want

strSQL = "insert into ReceiveOrder (TableNo, FoodName,Quantity,Price) select TableNo,FoodName, Quantity , Price  FROM StoreOrders4 WHERE TableNo = 4"

and then you need a second command that will remove the records from StoreOrders4 like this

 strSQL = "delete  FROM StoreOrders4 WHERE TableNo = 4"

Suggest you start a transaction and rollback if you get an error.

Can you please explain what are StoreOrders1 - 4 tables for ? Why can't you use 1 table and control the status of the order by a status field (possibly a user too)?