Copy columns from one table to another using VB SQL query

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Hi,
I have two database tables tblClasses and tblClassesWeb they are
identical only tblClassesWeb has two less columns. There are no
primary keys in the tables. What I am trying to accomplish is to
insert columns from tblClasses into tblClassesWeb in Visual Basic.NET
using SQL. I need to insert columns only if they don't already exist
in table I am inserting to (don't want to delete anything though). I
connect to db (code is omitted but it works) and execute the following
command:
sqlInsert = "INSERT INTO tblClassesWeb " & _
"(ExtSchoolID, ExtStudID, ExtTeacherID, SubjectTitle," &
_
" ExtClassID, SectionCode, PeriodCode) " & _
"SELECT" & _
" ExtSchoolID, ExtStudID, ExtTeacherID, SubjectTitle," &
_
" ExtClassID, SectionCode, PeriodCode " & _
"FROM tblClasses " & _
"WHERE tblClasses.ExtSchoolID <>
tblClassesWeb.ExtSchoolID " & _
"AND tblClasses.ExtStudID <> tblClassesWeb.ExtStudID
" & _
"AND tblClasses.ExtTeacherID <>
tblClassesWeb.ExtTeacherID " & _
"AND tblClasses.SubjectTitle <>
tblClassesWeb.SubjectTitle " & _
"AND tblClasses.ExtClassID <> tblClassesWeb.ClassID "
& _
"AND tblClasses.SectionCode <>
tblClassesWeb.SectionCode " & _
"AND tblClasses.PeriodCode <>
tblClassesWeb.PeriodCode"

the error I am getting after executing this command is "no value given
for one or more required parameters"

I stripped this string to the simplest case - inserting into 1 column
and using one where clause, I still get the same error.

I was also trying to accomplish this using WHERE NOT EXISTS clause
like this:
sqlInsert = "INSERT INTO tblClassesWeb SELECT ExtSchoolID, ExtStudID,
ExtTeacherID, SubjectTitle, ExtClassID, SectionCode, PeriodCode FROM
tblClasses WHERE Not Exists (SELECT * FROM tblClassesWeb)"
This works fine for the first time but if i add more entries to
tblClasses after the first run they won't get added to tblClassesWeb.

Can somebody please tell me how I can accomplish inserting rows from
one table to another using a conditional statements. Thank you
 
Am not positive exactly what you're after, but I suspect the query parser is
squawking at the use of the
"tblClassesWeb.<col>" terms in the subquery.

It almost looks as if you're trying to do a "Correlated Insert", but there
is no such thing. How could you correlate with an outer row that does not
yet exist?

If I can presume for the moment that your intent is along the lines of the
generic pattern :
"Find all 'things' in Table A that do not (yet) exist in Table B,
then insert those 'things' into Table B", so that Table B contains every
'thing' in Table A" ,

Then you might consider the following:

From a Set-based perspective, you're looking for the things in Set A that
are not in Set B, or "A MINUS B". Once you have those elements, they could
then be added into Set B.

The 'things' in each set/table are comprised of the attributes/columns that
uniquely identify an element/row in each set/table.

Assume for the moment that the uniqueness fields in both tables are called
"col1,col2,col3'
Then,

If your back-end database supports the MINUS operator :

You could try the sql dml:
INSERT INTO TableB (col1,col2,col3)
( SELECT col1,col2,col3 FROM TableA
MINUS
SELECT col1,col2,col3 FROM TableB
)


If, on the other hand, your database vendor has bailed and left it to you to
hack-out the minus operation you really need, then you'll have to do it with
yet another nested-level subquery:
INSERT INTO TableB (col1,col2,col3)
( SELECT col1,col2,col3 FROM TableA
WHERE NOT EXISTS
( SELECT col1,col2,col3 FROM TableB
WHERE ( TABLEA.col1 = TableB.col2 AND TableA.col2 =
TableB.col2 AND TableA.col3 = TableB.col3 )
)
)
In this case, the two nested sub-queries ARE correlated; the selected rows
of the nested subquery9s) can then be propogated back up to the INSERT
operation.

HTH.
 
Back
Top