SELECT and INSERT

[McKirahan]

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

[Bob Barrows [MVP]]

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"

[McKirahan]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:#mLQwS#dEHA.3612@TK2MSFTNGP09.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.

[Aaron [SQL Server MVP]]

> 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.

[McKirahan]

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eX5dMUKeEHA.1764@TK2MSFTNGP10.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.