Insert into clause

[directmail]

Hello,

I am trying to insert rows in a table by doing a select from another table.
I have a field in the table whose data needs to be static

For example: Tab1 is the table that I am trying to insert into

tab1.field1 -> data comes in from tab2.field10
tab1.field2 -> data comes in from tab2.field11
tab1.field3 -> static string like 'FIRST GROUP'

I was able to successfully run the following query:

insert into tab1 ( field1, field2 )
select field10, field11
from tab2
where field1 = 'some condition'

I want to change this insert statement to :

insert into tab1( field1, field2, field3 )
select field10, field11 from tab2 where field1 = some condition
< and the static value for tab1.field3 >

No matter what combinations I have tried, I get an error on the insert
statement syntax.

How should I do this.

Thanks
Girish

 

[John Vinson]

How should I do this.

Just use a literal as a calculated field:

insert into tab1 ( field1, field2, field3 )
select field10, field11, "SOME VALUE" As Expr1
from tab2
where field1 = 'some condition';


John W. Vinson[MVP]

 

[directmail]

Does not quite seem to work.

Just the select part of it works fine - with the literal column. The minute
I add it to my insert - select combination I get an error "SYNTAX ERROR IN
INSERT INTO STATEMENT"

My statement is :
INSERT INTO foreclosure_absentee_info ( property_address, property_city,
property_state, property_zip, mailing_address, mailing_city, mailing_state,
mailing_zip, apn, first_name, last_name, group )
SELECT [site address] & " " & [site unittype] & " " & [site unit] AS Expr1,
santa_clara.[Site City], santa_clara.[Site State], santa_clara.[Site Zip],
[mail address] & " " & [mail unittype] & " " & [mail unit] AS Expr2,
santa_clara.[Mail City],santa_clara.[Mail State], santa_clara.[Mail Zip],
santa_clara.[Parcel Number], santa_clara.[First Owner First Name],
santa_clara.[First Owner Last Name], 'MYGROUP' as Expr10
FROM santa_clara;

 

[John Vinson]

Does not quite seem to work.

Just the select part of it works fine - with the literal column. The minute
I add it to my insert - select combination I get an error "SYNTAX ERROR IN
INSERT INTO STATEMENT"

Try using " rather than ' to delimit "MYGROUP".

John W. Vinson[MVP]

 

[directmail]

Sorry John .. does not work either. I changed to "MY GROUP" as EXPR10

-- Its the same error : SYNTAX ERROR in INSERT INTO statement


INSERT INTO foreclosure_absentee_info ( property_address, property_city,
property_state, property_zip, mailing_address, mailing_city, mailing_state,
mailing_zip, apn, first_name, last_name , group)
SELECT [site address] & " " & [site unittype] & " " & [site unit] AS Expr1,
santa_clara.[Site City], santa_clara.[Site State], santa_clara.[Site Zip],
[mail address] & " " & [mail unittype] & " " & [mail unit] AS Expr2,
santa_clara.[Mail City], santa_clara.[Mail State], santa_clara.[Mail Zip],
santa_clara.[Parcel Number], santa_clara.[First Owner First Name],
santa_clara.[First Owner Last Name], &quot;MYGROUP" as EXPR10
FROM santa_clara;
Thanks
Dipti

 

[John Vinson]

Sorry John .. does not work either. I changed to "MY GROUP" as EXPR10

Hrm. Not sure what the problem might be!

Try opening the query in design view and change it from an Append
query into a SELECT query (don't worry, it won't lose the Append To
information if you don't save the query, though it will disappear from
view). If you open this as a datasheet does it show the expected
values?

Also, you seem to be using HTML - the SQL you posted has &quot; in
place of one of the " marks. Make sure the actual SQL doesn't.

John W. Vinson[MVP]

 

[John Vinson]

Sorry John .. does not work either. I changed to "MY GROUP" as EXPR10

-- Its the same error : SYNTAX ERROR in INSERT INTO statement


INSERT INTO foreclosure_absentee_info ( property_address, property_city,
property_state, property_zip, mailing_address, mailing_city, mailing_state,
mailing_zip, apn, first_name, last_name , group)
SELECT [site address] & " " & [site unittype] & " " & [site unit] AS Expr1,
santa_clara.[Site City], santa_clara.[Site State], santa_clara.[Site Zip],
[mail address] & " " & [mail unittype] & " " & [mail unit] AS Expr2,
santa_clara.[Mail City], santa_clara.[Mail State], santa_clara.[Mail Zip],
santa_clara.[Parcel Number], santa_clara.[First Owner First Name],
santa_clara.[First Owner Last Name], &quot;MYGROUP" as EXPR10
FROM santa_clara;
Thanks
Dipti

Hmmm... another thought. The word GROUP is probably not appropriate as
a fieldname (it's a valid SQL term, Group By). Try using [group]
instead.

John W. Vinson[MVP]