Professional Web Applications Themes

SELECT and INSERT - ASP Database

Is there a way to do the SELECT and INSERT in a single SQL statement in MS-Access?...

Sponsored Links
  1. #1

    Default SELECT and INSERT

    Is there a way to do the SELECT and INSERT in a single SQL statement in
    MS-Access?


    Sponsored Links
    McKirahan Guest

  2. #2

    Default Re: SELECT and INSERT

    McKirahan wrote:
    > Is there a way to do the SELECT and INSERT in a single SQL statement
    > in MS-Access?
    Didn't I answer this yesterday?

    The answer is Yes.

    Insert into tablename (<column list>)
    Select <column list>
    FROM tablename

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Barrows [MVP] Guest

  3. #3

    Default Re: SELECT and INSERT

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:#mLQwS#dEHA.3612TK2MSFTNGP09.phx.gbl...
    > McKirahan wrote:
    > > Is there a way to do the SELECT and INSERT in a single SQL statement
    > > in MS-Access?
    >
    > Didn't I answer this yesterday?
    >
    > The answer is Yes.
    >
    > Insert into tablename (<column list>)
    > Select <column list>
    > FROM tablename
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Actually, you didn't. You saw it yesterday in the post called "Database
    Efficiency" but you only answered the first of three questions. Thank you
    for both of your answers.


    McKirahan Guest

  4. #4

    Default Re: SELECT and INSERT

    > Actually, you didn't. You saw it yesterday in the post called "Database
    > Efficiency" but you only answered the first of three questions.
    ??? You must not have bothered to read far enough down his post. I see
    this (scroll down, please!):







    > Is there a way to do the SELECT and INSERT in a single SQL statement?
    Yes, there are two ways:

    1. You can create a linked table in your Access database and do a simple:
    strSQ2 = "INSERT INTO AR1_CustomerMaster"
    strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
    strSQ2 = strSQ2 & " SELECT CustomerNumber, CustomerName"
    strSQ2 = strSQ2 & " FROM AR1_CustomerMaster_lnk"



    2. You can use an IN clause in your SELECT statement:

    Dim strSQ2
    strSQ2 = "INSERT INTO AR1_CustomerMaster"
    strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
    strSQ2 = strSQ2 & " SELECT CustomerNumber, CustomerName"
    strSQ2 = strSQ2 & " FROM AR1_CustomerMaster "
    strSQ2 = strSQ2 & " IN '' [ODBC; DSN=ODBC_System_DSN;]"

    >
    >
    > Also, what is the value of "adExecuteNoRecords"?
    When you execute a statement that does not return records, you need to tell
    the Command object (yes, a Command object is being used by ADO here) to skip
    the creation of a recordset object. If you do not use "adExecuteNoRecords",
    it will create a recordset object even though your query does not return any
    records.


    Aaron [SQL Server MVP] Guest

  5. #5

    Default Re: SELECT and INSERT

    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:eX5dMUKeEHA.1764TK2MSFTNGP10.phx.gbl...
    > > Actually, you didn't. You saw it yesterday in the post called "Database
    > > Efficiency" but you only answered the first of three questions.
    >
    > ??? You must not have bothered to read far enough down his post. I see
    > this (scroll down, please!):
    >
    >
    >
    >
    >
    >
    >
    >
    > > Is there a way to do the SELECT and INSERT in a single SQL statement?
    >
    > Yes, there are two ways:
    >
    > 1. You can create a linked table in your Access database and do a simple:
    > strSQ2 = "INSERT INTO AR1_CustomerMaster"
    > strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
    > strSQ2 = strSQ2 & " SELECT CustomerNumber, CustomerName"
    > strSQ2 = strSQ2 & " FROM AR1_CustomerMaster_lnk"
    >
    >
    >
    > 2. You can use an IN clause in your SELECT statement:
    >
    > Dim strSQ2
    > strSQ2 = "INSERT INTO AR1_CustomerMaster"
    > strSQ2 = strSQ2 & " (CustomerNumber, CustomerNumber)"
    > strSQ2 = strSQ2 & " SELECT CustomerNumber, CustomerName"
    > strSQ2 = strSQ2 & " FROM AR1_CustomerMaster "
    > strSQ2 = strSQ2 & " IN '' [ODBC; DSN=ODBC_System_DSN;]"
    >
    >
    > >
    > >
    > > Also, what is the value of "adExecuteNoRecords"?
    >
    > When you execute a statement that does not return records, you need to
    tell
    > the Command object (yes, a Command object is being used by ADO here) to
    skip
    > the creation of a recordset object. If you do not use
    "adExecuteNoRecords",
    > it will create a recordset object even though your query does not return
    any
    > records.
    >
    >
    My humble apology -- you're right.

    I guess I saw all that "strSQ2 = " stuff and thought it was my original
    post.

    Thanks again for your responses which are always of the highest quality.


    McKirahan Guest

Similar Threads

  1. insert into...select using the same table
    By Don Vaillancourt in forum MySQL
    Replies: 2
    Last Post: March 22nd, 10:30 PM
  2. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. Insert from Select
    By RPhillips@ce-a.com in forum Informix
    Replies: 6
    Last Post: July 30th, 10:16 PM
  4. How to select and then loop while insert
    By Mansoor Aleem in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 8th, 08:20 PM
  5. cursor with insert/select
    By Andre in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 07:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139