I have very little Database Experience, and I need a little help. I am using sqlplus to manipulate an Oracle 10g database.
I have two tables I want to use to create a new table.
Table_1 has a column that references data in Table_2. I want my new table to contain all of the information from Table_1 + one column of Table_2. Table_2 includes data from a bunch of other tables I do not want. Table_2 also has a DATE column that is relevant. So I only want the information from Table_2 where it matches Table_1 AND where the DATE is after 11/15/09.
IMG_SYSID is the column in both tables. IMAGE is the column from table_2 that I want to add to my new table along with all the information from table_1.
"CREATE TABLE new_table_name AS SELECT * FROM Table_1"
Will give me a copy of Table_1, right?
Now how do I add the information from Table_2 where Table_2.IMG_SYSID matches Table_1.IMG_SYSID AND DATE > 11/15/09 (between 11/15/09 and today). Is it a JOIN WHERE clause of some sort? Do I need to do it all in one query?
Posts
columns
FROM Table_1
LEFT JOIN Table_2
ON Table_1.IMG_SYSID =Table_2.IMG_SYSID
WHERE
Table_1.DATE >= '11/15/09'
Can I do
CREATE TABLE new_table_name AS SELECT * FROM Table_1
LEFT JOIN Table_2
ON Table_1.IMG_SYSID =Table_2.IMG_SYSID
WHERE
Table_1.DATE >= '11/15/09'
and I will now have a new table that includes the information I want? Sorry I have to ask, but I understand that the SELECT there will just give me a view that won't be a real table. I need it to be an actual table so I can export the table to another database.
I really would have to know exactly what you're trying to do before I know if what you're doing is the right solution though. If you need your results to be dynamic and not be a copy of the current state of table A and B, you should probably be using a VIEW.
I rarely use SELECT INTO (in your case CREATE TABLE AS SELECT) unless I'm exporting data for a one-time custom report some executive handed down a request for. Otherwise, I would create the table definition beforehand with primary and foreign keys, constraints, and indexes. Then you can insert into the table using
Yea, I want the new table to be a copy of Table_1 and Table_2. I am going to take this new table, and export/copy it to an Access database.
Gives me
CREATE TABLE new_table_name AS SELECT * FROM Table_1
*
ERROR at line 1:
ORA-00957: duplicate column name
Cause I really only want that column, gives me
LEFT JOING Table_2.Image
*
ERROR at line 2:
ORA-00905: missing keyword
:?
unless there's a column in Table_1 called IMAGE.
in which case, you'll need to rename the IMAGE column:
Can I not do this because I am getting the error
"ORA-00997: illegal use of LONG datatype"
Googling this suggests that LONG RAW is tricky to work with. (I mostly use SQL Server myself, so not familiar with Oracle specific stuff).
Would this be possible: on the computer with Access installed, create an ODBC data source to your Oracle database, use the Link Tables option in Access to view those Oracle tables, then do the import to the new table directly in Access?
Otherwise, the workaround I see suggested for copying LONG RAW fields is to export the table (Table_2), rename it (to new_table_name), import Table2 back again, then modify new_table_name as needed. (In your case, dropping the unneeded columns, adding the Table_1 columns, then running an update query to copy the data across.)
As you can see, that's... pretty labour intensive. Why do you need this new table anyway? Is it really necessary to duplicate these specific images from Table2 and get the associated data from Table1 into a single table? Storing the same data in two or more places in a single database is usually considered a no-no.
S/he wants to import this particular set of data into Access.
Buddies, if you don't want to go the ODBC route, can you just export both tables, import them both into Access, and do your data manipulation there? If that would be too slow, I really would recommend trying ODBC... I haven't ever connected to Oracle that way but I've used this method to import data into Access from SQL Server, MySQL, and Paradox.
http://www.dbaxchange.com/3_4_longdatatypes.htm
So try the following:
copy from <user>/<pass>@<SID> CREATE table_3 USING select table_1.*,table_2.IMAGE from table_1,table_2 where table_1.img_sysid=table_2.img_sysid and table_1.datecol > to_date('11/15/2009','MM/DD/YYYY');
Just tried it with a 10G database set up like your tables and it seemed to work fine.
Thanks man, I did the ODBC thing and linked through Access and was able to get what I wanted.
Thanks for all your suggestions everyone else.