My understanding of SQL is rather limited.
If I execute
select "TableName","Name","Type", "Length" from information.tablecolumns
it runs happily, but if I run
Create temporary table InfoTbl as select "TableName","Name","Type", "Length" from information.tablecolumns
It wants a “with” clause.  I have no idea how to modify it to make it create the temp table. Any help appreciated.

Colin,

> It wants a “with� clause.

You need to specify if you want to just create the empty with the structure
of the result set or if you want to populate it too.    Add "WITHOUT DATA"
to the end of your statement to create the empty table, or "WITH DATA" to
populate it.  Using your query as an example,

Create temporary table InfoTbl as select "TableName","Name","Type", "Length"
from information.tablecolumns WITH DATA

will get you your temporary table that you can then work with.


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

Fantastic, thanks Terry

Terry, if I want to append the data to an existing table how would I modify the syntax. Obviously "Create temporary" would go, but what do I put in its place?

Create temporary table InfoTbl as select "TableName","Name","Type", "Length"
from information.tablecolumns WITH DATA

Col

Colin,

INSERT INTO <YourTempTable>
  SELECT <Column List> FROM <Your Source Table>



--
Fernando Dias
[Team Elevate]

Colin


>Terry, if I want to append the data to an existing table how would I modify the syntax. Obviously "Create temporary" would go, but what do I put in its place?

There you'd use an INSERT statement. Treat the temporary table as just another table.

eg

create temporary table "albert" as select * from contacts with no data

insert into albert select * from contacts where _surname like 'w%'

select * from albert

NOTE these are separate statements run one at a time

If you have trouble post what it is you're wanting to insert / update and we'll help you with the sql


Roy Lambert [Team Elevate]

Thank you gentlemen, I'll give it a go.