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

 

[Jamie Collins]

Something like

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

Try something more like

INSERT INTO Table1 (col1, col2, col3)
SELECT 'textstring' AS col1,
lname AS col2, fname AS col3
FROM Table2
WHERE ...

Jamie.

--

 

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

 

[Jamie Collins]

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

I would recommend you do not use SELECT * in this way in production
code, primarily because there is too much potential for SQL DDL (schema
changes) to break SQL DML (queries etc).

Also consider that if you don't know the column names at design time
you may have a design flaw in the system.

And saving yourself having to type 30+ column names is no excuse <g>.

I say 'in this way' because there are exceptions. For counting rows,
COUNT(*) is optimized i.e. will run faster than COUNT(column_name). It
is convention to use SELECT * in a subquery with an EXISTS e.g.

SELECT COUNT(*)
FROM Table1
WHERE EXISTS (
SELECT *
FROM Table2
WHERE Table1.key_col = Table2.key_col);

Jamie.

--

 

[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

 

[Jamie Collins]

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.

If table2 doesn't exist yet (and this isn't production code <g>) then

SELECT 'textstring' AS new_col, *
INTO Table2
FROM Table1;

Jamie.

--

 

[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

 

[Jamie Collins]

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)?

Well, if you immediately re-run the SQL you will get an error because
Table2 already exists. And I'd argue that the SELECT..INTO..FROM syntax
is SQL DDL rather than SQL DML; I can see a place from in it your usual
OLTP application (sorry for all the jargon <g>).

Jamie.

--

 

[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

 

[Jamie Collins]

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?

The correct way is to not use 'inline if' <g>.

One of the drawbacks of IIF is that both true and false clauses get
evaluated, so you would have to code in such a way that both clauses
could never error.

Because this is VBA code you can use the syntax

If <expression> Then
<true clause>
Else
<false clause>
End If

The latter syntax has other benefits e.g. you can put break point on
just of the clauses.

Jamie.

--

 

[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

 

[Jamie Collins]

I can see a place from in it your usual
OLTP application (sorry for all the jargon <g>).

Typos!

I meant to say, I see no place for the syntax (SELECT..INTO..FROM) in a
regular OLTP application.

Jamie.

--