Help with 'Insert into' statement syntax

[Seamus Conlon]

In a VB program I want to run an INSERT INTO query that
will insert a fixed string into the first field of the output table and
use a SELECT statement to fil the rest of the fields from another
table. Something like

Insert into table1 "textstring", select * from table2 where ....

I have tried a variety of formats and keep getting a syntax error.
Any ideas of how best to do this.

Many thanks,
Seamus

 

[Seamus Conlon]

Thanks Jamie. There are about 30 cols in the input
table and 31 in the output with col1 to col30 of the
the input the same as col2 to col 31 of the output
and only col1 different.

Do I need to list each col individually or can I use
an * in the statement?

Seamus

 

[John Spencer]

I don't know if this would work, but I would try

INSERT into Table1
SELECT "TextString", Table2.* FROM Table2 WHERE ...

Personnally, I normally use the following syntax since I want to be sure of
my data alignment.
INSERT Into Table1 (<Field List>)
SELECT <Field List>
FROM Table2 ...

 

[Seamus Conlon]

Nope, that didn't work. Got an error ' No destination field
in INSERT INTO statement. Mind you, since I am using
the execute method of my database object I have set a
string variable to the query text and used single quotes
as the text string delimiter -

strsql = "insert into table1 select 'text', table2.* from table2 ...

I don't know if this would make a difference.

Seamus

 

[Seamus Conlon]

Maybe I should explain in more detail what I am trying to do.
Table1 holds firewall log entries and has 30 cols (I know their
names). I want to build a new table2 by running various queries
on table1 to extract rows that match certain selection criteria.
So table2 will have the same cols as table1 but with an extra
col that indicates the query criteria.

Perhaps there is some other way of doing this but the INSERT
INTO seemed the best option to me.

Seamus

 

[Seamus Conlon]

Thanks, that worked fine. While it isn't production code it will be
used internally and I would prefer that it worked all the time. Is
the issue the same as you mentioned previously with regard to
SQL DDL (schema changes) breaking SQL DML (queries etc)?

Seamus

 

[Seamus Conlon]

Jamie, thanks for the update - it sounds sensible.

One other point that is currently puzzling me. Some of the numeric
database fields that I will be printing may be null. So I thought of
using something like

iif(isnull(rs.fields(n),"--",str(rs.fields(n))

but this gives an invalid use of null error. What is the correct
way of handling this?

Seamus

 

[Seamus Conlon]

I had expanded it out to the if ... then ... else statement but as I have
to do it for all the numeric fields I thought there should be a shorter
way.

I didn't realise that for iif each of the clauses get evaluated - a bit
silly
that.

Thanks again for all the help.

Seamus