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
LVL 10
ranhellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
If you are selecting all the fields to be inserted you can just do this:

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.
0
ranhellAuthor Commented:
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.
0
Patrick MatthewsCommented:
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 ()
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

ranhellAuthor Commented:
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...??
0
Patrick MatthewsCommented:
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.
0
ranhellAuthor Commented:
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?
0
ranhellAuthor Commented:
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"
0
Patrick MatthewsCommented:
I object.  The user needed two different SQL statements to do what s/he wanted to do, as I pointed out.

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ranhellAuthor Commented:
Good
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.