Results 1 to 3 of 3

Thread: INSERT INTO ... FROM Help.

  1. #1
    Join Date
    Dec 1969

    Default INSERT INTO ... FROM Help.

    I&#039;m trying to construct an INSERT INTO ... FROM statement that would take information that I feed it directly, and the rest from a file. for example:<BR><BR>InvDet (Invoice Detail) will contain a list of all members on "this" invoice. So: INSERT INTO InvDet SELECT * FROM Members WHERE InvoiceNo=&#039;12345&#039;, would seem to be the right format. However, I also need to add the Invoice Number, and a Member Type field in there, which isn&#039;t contained in the Member table from which I&#039;m trying to pull the data. So, How do I construct an INSERT INTO statement that will allow me to specify the Invoice # for the InvoiceNo field, and a constant value for the MemberType field, then to pull the rest from the Member table?<BR><BR>It almost seems like I need something like this:<BR>INSERT INTO IntDet (InvoiceNo, MemberType, MemberID, FirstName, MI, LastName) VALUES (&#039;12345&#039;,&#039;Student&#039;,(SELECT ID,FirstName,MI,LastName FROM Members WHERE InvoiceNo=&#039;12345&#039;))<BR><BR>But, this results in a "Number of query values and destination fields are not the same."<BR><BR>I am using .ASP with MS Access. Any help is appreciated.<BR><BR>Thanks,<BR>Jesse

  2. #2
    Join Date
    Dec 1969

    Default RE: INSERT INTO ... FROM Help.

    INSERT INTO IntDet (SELECT InvoiceNo,&#039;Student&#039; as MemberType, FirstName, MI, LastName FROM Members WHERE InvoiceNo=&#039;12345&#039;)<BR><BR>That might work Mind Access not my strong point

  3. #3
    Join Date
    Dec 1969

    Default Basically.

    But, you need to lose the parenthesis.<BR><BR>And, I prefer to name the fields I&#039;m inserting into:<BR>[code language="T-SQL"]<BR>INSERT INTO IntDet (InvoiceNo, MemberType, FirstName, MI, LastName)<BR>SELECT InvoiceNo, &#039;Student&#039; AS MemberType, FirstName, MI, LastName<BR>FROM Members<BR>WHERE InvoiceNo = &#039;12345&#039;<BR>[/code]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts