SQL Syntax Help for INSERT INTO WHERE...

G

Gladimir

Well-known member
Joined
Mar 29, 2003
Messages
60
Location
San Diego, CA
Here is my current SQL statement that produces a syntax error:

C#: 
"INSERT INTO " + strTableName + " (DescID) " +
   "SELECT DescID FROM " + strTableName + " WHERE (Description=" + strDescription + ") " +
   "VALUES(" + intDescID.ToString() + ")";

I came across one of those SQL Tutorial sites that said something like this would be legal, but I had my doubts from the beginning.

What I am trying to do is INSERT INTO myTable (DescID) VALUES(intDescID) WHERE (Description=strDescription)". I just dont know the proper syntax or even if it is possible to do with a single SQL statement.

Background

I select distinct descriptions and come up with about 4000 rows. For each of those distinct descriptions, I am trying to use my INSERT statement to update records in another table. Sometimes Description equals strDescription in only one row, and other times that criteria will return just under 10,000 rows or matches.
 
You can do the following in SQL just fine. But you have to have the corrct number of columns that the table has.
Code: 
INSERT INTO table
SELECT col1, col2, col3, col4
FROM other_table
WHERE col1 = condition
 
Youre combining the two basic forms of the INSERT. You either want the SELECT or the VALUES, but not both. For instance:
Code: 
INSERT INTO Table1 (DescID)
SELECT DescID FROM Table2 WHERE MyColumn = MyValue

OR

INSERT INTO Table1 (DescID)
VALUES (123)

When using VALUES you must provide an actual value, not a select. If you have a combination of known hard-coded values and values from a table, you use the SELECT as in:
Code: 
INSERT INTO Table1 (DescID, Active, UserName)
SELECT DescID, 1, djones FROM Table2 WHERE MyColumn = MyValue
In the above, the "1" and "djones" are hard-coded - they have nothing to do with Table2.

-Nerseus
 
Patience for a slow one...

Ok, so these responses would indicate to me that I need two queries; one to select the records matching the description and a second to insert that descriptions id into the DescID column. Please correct me if I am wrong.

Here is a sample of information from table REF_CSDSoftware:
Code: 
recID	Description	            DescID
339480	expinst		                0
339481	Internet Explorer ReadMe	0
Its the zeros in DescID that the INSERT will correct.

Here is a sample from table REF_MasterDescription:
Code: 
DescID	Description	       CategoryCode
1	Adobe Acrobat Writer	1
2	Adobe PageMaker 5.0	1

In a separate function, I select the distinct descriptions from REF_CSDSoftware, assign each one a unique DescID, and insert that into the REF_MasterDescription table.

I then take that same unique DescID and the corresponding description and pass it to a function that queries all records from REF_CSDSoftware matching the description. I would like to simply insert that DescID into the results of that query, which is the one Im trying to build.

Here is some code:
C#: 
int intDescID = 0;
string strDescription = null;
// drc is distinct descriptions from table REF_CSDSoftware
foreach (DataRow row in drc)
{
   strDescription = row["Description"].ToString();
   // does description already exist in REF_MasterDescription
   intDescID = LookupDescID(strDescription);
   if (intDescID < 1)	// id does not exist in master list
   {
      // insert new Description and DescID in the master list
      InsertNewDescription(strDescription, eCode);
      intDescID = LookupDescID(strDescription);
   }
   // Get all records matching description from REF_CSDSoftware
   // and assign the new DescID to all rows with that description
   InsertMasterDescriptionID(intDescID, strDescription, eCode);
   Application.DoEvents();
}

So, am I correct in assuming InsertMasterDescriptionID will first have to SELECT all rows matching strDescription, the follow that with a separate query to INSERT the corresponding intDescID?
 
Back
Top