SELECT values from one table and insert them to another table?

[savedlema]

Hi all! I think I need some help moving data from one table to another. Here is the scenario:
I have two tables, tblDetails (studentID,Name, Class.....) and tblPhysics (StudentID.....). Now, I want to SELECT StudentID from tblDetails WHERE Class = 1 (SELECT tblDetails.StudentID FROM tblDetails WHERE tblDetails.Class = '1') So this one I can do. But then, I want to INSERT the results of the above query into a table tblPhysics which has a column StudentID. How do I do this? Select values from one table and then insert them to another existing table. Please note that I want to insert the query results to an existing table (I tried to use SELECT INTO, but that will create a new table).

I will appreciate any help.
Thanks.

[AmrMohammed]

INSERT INTO tblPhysics (studentID)
SELECT studentID FROM tblDetails WHERE Class = 1

And to avoid getting error if the studentID may be NULL in your case add this part before the insert IF(SELECT studentID FROM tblDetails WHERE Class = 1) IS NOT NULL. so the final resulted statement will be

IF(SELECT studentID FROM tblDetails WHERE Class = 1) IS NOT NULL
INSERT INTO tblPhysics (studentID)
SELECT studentID FROM tblDetails WHERE Class = 1

Try this out and hope this is what you asked for.

[savedlema]

Thanks Amr,
I tried to do this with your code:

    databaseSettings()
    con.Open()
    sqlQuery = "IF(SELECT studentID FROM tblDetails WHERE Class = 1) IS NOT NULL " _
                + "INSERT INTO tblPhysics (studentID) " _
                + " SELECT studentID FROM tblDetails WHERE Class = '1' "

    cmd = New OleDbCommand(sqlQuery, con)
    cmd.ExecuteNonQuery()
    MsgBox(" succesfully", vbInformation)
    con.Close()

But, ended up with the following error: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Any ideas?

[AmrMohammed]

I forgot, my bad sorry remove the part
IF(SELECT studentID FROM tblDetails WHERE class = 1) IS NOT NULL

because this condition statement is used if you will check for only one value but in your case for example there may be number of students associated with a specific class number so the query within the braces will return a number of student identifiers that causes the problem. So remove that line and everything will become good.

Use only

INSERT INTO tblPhysics (studentID)
SELECT ISNULL(studentID, NULL) FROM tblDetails WHERE Class = 1

If you found another problem send me.

Regards,

Amr Mohammed

[Icone]

I suggest to read the data using DATAREADER and while reading, it is actually inserting the data which has been read already. Hope it helps.

[savedlema]

Thanks again Amr!
I tried this :

        sqlQuery = "INSERT INTO tblPhysics (studentID)" _
                + "SELECT ISNULL(studentID, NULL) FROM tblDetails WHERE Class = 1"

        cmd = New OleDbCommand(sqlQuery, con)
        cmd.ExecuteNonQuery()

And got "Wrong number of arguments used with function in query expression 'ISNULL(studentID, NULL)'." Error message. Since I talked about the arguments..I removed ('NULL) and tried:

sqlQuery = "INSERT INTO tblPhysics1 (studentID)" _
                + "SELECT ISNULL(studentID) FROM tblDetails WHERE Class = '1'" //(note the '' in 1)

and got "You cannot add or change a record because a related record is required in table 'tblDetails'." Error message. Now I think the insert is okay, but there is something wrong with the StudentID (probably to do with a relationship btn the tables?), or may be its because I removed the 'NULL' and thus allowing it to insert a blank value for studentID (which is a primary key and wont allow blanks?)

Any advice?

Thank you so far.

[savedlema]

Hi Amr, this one works:

        sqlQuery = "INSERT INTO tblPhysics1 (studentID)" _
                + "SELECT (studentID) FROM tblDetails WHERE Class = '1'"

But it does not regard the primary key violation. That's, if I execute the command the second time, it will still attempt to enter the data. I'm gonna try to catch this. Thanks, and pls let me know if you get the fix for 'NULL' and 'ISNULL'

[Reverend Jim]

What database are you using? MS SQL uses the COALESCE(field,value if NULL) function while MS Access uses NZ(field,value if NULL) function.

[savedlema]

I use Access. What difference will it make to the code I reported half-working?
Thanks.