Re: Cross database queries - a survey of sorts

runstein wrote:
> I'm creating a database system in SQL Server 2000 but don't want to be stuck
> if my employer want to use another RDBMS in the future.

Informix answers follow ...

>
> In SQL Server, it is possible to run a query against a database from another
> database. For example:
>
> use DataBaseA
> go
> select * from DataBaseA.dbo.tableA
> go
>
> Is this common in other relational databases? What kind of syntax is
> involved?

Most 'serious' databases can do this. Syntax can vary, however.

Informix syntax is quite nice - just do:

select * from other_database_at_other_server:tableA;

You could also create a synonym: e.g.

CREATE SYNONYM remoteA on other_database_at_other_server:tableA;

>
> Also, SQL Server has what I assume is a proprietary method for inserting a
> record in a table with an auto-incrementing (identity) field, and then
> returning the newly created number. How do other database systems handle
> this?
>

Creating an auto-incrementing column should be pretty standard:

e.g.
create table A (id serial, x integer, y intger); insert into A (x,y) values (10,20);
insert into A (x,y) values (11,22);
select * from A;

id x y
1 10 20
1 11 22

Retrieving the serial value after an insert can be done with DBINFO('sqlca.sqlerrd1') on Informix.

Regards,
Kieran Elby Received on Wed Sep 25 2002 - 18:28:18 CEST