Insert into values and select statement
I'm using MS Access as the DB, Visual Studio VB.net as the interface and I'm trying to run the following SQL Statement
sSql = "Insert Into TEMPIN(IDSTAT,FECHACON)"
sSql += " Values(2,'" & dtfecha & "');"
sSql += " Select * FROM MASTER"
sSql += " WHERE CB = '" & strLine & "' and IDSTAT = 1 "
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
cmd.ExecuteNonQuery()
What I want to achive is Insert all field from "MASTER" table into "TEMPIN" table where both tables are identical, plus a 2 value fields from different variables
Is this posible to mix the VALUE and SELECT in a INSERT INTO SQL Statement
sSql = "Insert Into TEMPIN(IDSTAT,FECHACON)"
sSql += " Values(2,'" & dtfecha & "');"
sSql += " Select * FROM MASTER"
sSql += " WHERE CB = '" & strLine & "' and IDSTAT = 1 "
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
cmd.ExecuteNonQuery()
What I want to achive is Insert all field from "MASTER" table into "TEMPIN" table where both tables are identical, plus a 2 value fields from different variables
Is this posible to mix the VALUE and SELECT in a INSERT INTO SQL Statement
ASKER
I know how to do that, but that doesn't solve my problem,
Let me clarify.
I have Tables MASTER and TEMPIN
And I want to Insert all fields from Table MASTER to TEMPIN, plus I need to Insert to values from 2 different variables to table TEMPIN using the SQL clause Values.
Let me clarify.
I have Tables MASTER and TEMPIN
And I want to Insert all fields from Table MASTER to TEMPIN, plus I need to Insert to values from 2 different variables to table TEMPIN using the SQL clause Values.
ranhell said:
>>I have Tables MASTER and TEMPIN
>>And I want to Insert all fields from Table MASTER to TEMPIN, plus I need to Insert to values from 2 different variables to table TEMPIN using the SQL clause Values.
The you need two different inserts, one using syntax:
INSERT INTO Table ()
SELECT ...
INSERT INTO Table () VALUES ()
>>I have Tables MASTER and TEMPIN
>>And I want to Insert all fields from Table MASTER to TEMPIN, plus I need to Insert to values from 2 different variables to table TEMPIN using the SQL clause Values.
The you need two different inserts, one using syntax:
INSERT INTO Table ()
SELECT ...
INSERT INTO Table () VALUES ()
ASKER
Again I know How to do that see queries below.
sSql = "Insert Into TEMPIN"
sSql += " Select * FROM MASTER"
sSql += " WHERE CB = '" & strLine & "' and IDSTAT = 1 "
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
cmd.ExecuteNonQuery()
sSql = "UPDATE TEMPIN Set IDSTAT = 2, FECHACON = '" & dtfecha & "'"
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
cmd.ExecuteNonQuery()
What I was hoping is to do only 1 SQL statement what would combine both.
is these posible...??
sSql = "Insert Into TEMPIN"
sSql += " Select * FROM MASTER"
sSql += " WHERE CB = '" & strLine & "' and IDSTAT = 1 "
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
cmd.ExecuteNonQuery()
sSql = "UPDATE TEMPIN Set IDSTAT = 2, FECHACON = '" & dtfecha & "'"
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
cmd.ExecuteNonQuery()
What I was hoping is to do only 1 SQL statement what would combine both.
is these posible...??
ranhell said:
>>What I was hoping is to do only 1 SQL statement what would combine both.
>>is these posible...??
No, you need separate statements.
>>What I was hoping is to do only 1 SQL statement what would combine both.
>>is these posible...??
No, you need separate statements.
ASKER
OK, then I'm trying to do the following SQL statement
sSql = "UPDATE MASTER SET IDSTAT = TEMPIN.IDSTAT, FECHACON = TEMPIN.FECHACON"
sSql += "FROM MASTER inner join TEMPIN WHERE CB = TEMPIN.CB"
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
but it doesn't do what is supposedly to do, it does not update MASTER table and it doesn't give me an error either.
can you help me?
sSql = "UPDATE MASTER SET IDSTAT = TEMPIN.IDSTAT, FECHACON = TEMPIN.FECHACON"
sSql += "FROM MASTER inner join TEMPIN WHERE CB = TEMPIN.CB"
If Con.State = ConnectionState.Closed Then Con.Open()
cmd = New OleDb.OleDbCommand(sSql, Con)
but it doesn't do what is supposedly to do, it does not update MASTER table and it doesn't give me an error either.
can you help me?
ASKER
never mind I figure it out
Sql = "UPDATE"
sSql += " MASTER INNER JOIN TEMPIN ON MASTER.CB = TEMPIN.CB"
sSql += " SET MASTER.IDSTAT = TEMPIN.IDSTAT, MASTER.FECHACON = TEMPIN.FECHACON"
Sql = "UPDATE"
sSql += " MASTER INNER JOIN TEMPIN ON MASTER.CB = TEMPIN.CB"
sSql += " SET MASTER.IDSTAT = TEMPIN.IDSTAT, MASTER.FECHACON = TEMPIN.FECHACON"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good
Insert Into TEMPIN
Select statement here
If you are inserting all the fields into the table you just need to select them all (in the order they appear in the table) and that will insert them for you without having to identify each field individually.
Then the select statement can be any statement that produces the required results you are looking for.