I know how to select fields from two different tables in SQL, but can you copy data from one field to another using SQL??

C

If you are trying to update a row you want to do it with subqueries :
You can also add a subquery to the where statement as well.

If you are inserting a new row, do something like this:

where the select statement is the rows you want to copy from the other table.

If you need further clarification let me know.

Ok but if I need to select only records from one table to the other table where the two table are joined do I need a where statement to do this???

If you choose to select ALL rows, then no you do not need a where statement. If you only wish to copy rows from one table based on a certain criteria, then yes.

Example:

Table employee
emp_id
ename
sal
job

Table developers
emp_id
ename
sal
job


ok - Employee holds all your employee data for every job. Table developers only holds data for your developers.

This is a bad example, but off the top of my head its good enough to get an idea.

If you want to copy all of the employees with developer jobs from EMPLOYEE to DEVELOPERS you would do something like this:

This would effectively select all DEVELOPER records from EMPLOYEE and insert them into DEVELOPER. This is what I think you want to do correct?

Also should add a note to make sure the datatypes are consistent - dont try copying a date column to a varchar column... it makes the database angry

If you want to copy all of the employees with developer jobs from EMPLOYEE to DEVELOPERS you would do something like this:


code:--------------------------------------------------------------------------------
insert into developers (emp_id, ename, sal, job)
select emp_id, ename, sal, job from
employee where job = 'DEVELOPER'
--------------------------------------------------------------------------------

What I am trying to do is insert into CurrentMonth(Faxes that are Long distance)

select Long distance fax from

Long Distance Faxtbl where account='Current Month'


Is this correct....I do realize the table names are not exact, but I am trying to make sure my thinking is correct.


The join is the Account number. If the account number doesn't match I do not want to insert anything so in looking in the table under that column a zero would appear.

C

I have to make some assumptions here...because I cant see the data.


If you were to do this:

Insert into current_month (long_fax)
select long_fax from
Long_Distance_Faxtbl where account='Current Month'


You would select all of the Long faxes from the Long_distance_faxtbl where the account = 'CURRENT MONTH'. It would then insert these long faxes figures into the current_month table in the long_fax category.

You can join tables too...


Insert into current_month (long_fax, col2, col3, col4)
Select l.long_fax, b.test_col, b.test_col2, b.test_col3
from long_distance l, fake_table b
where l.fax_id = b.fax_id
and l.account = 'CURRENT MONTH'

Ok some how on all of this I am lost. I have tried to in access take two tables, and based on basically the account is what joins the two tables, so I thought that maybe in the SQL window I could write something that access was not allowing me to do, but I'm not sure I understand what has been explained to me to help me do what I am trying to do.


In SQL I can query the two tables and from the current Month table I pull account and the Long distance record returns all blanks and in the same query I get from the Long distance table all the records with accounts and in the Long distance column I get 3.1.

So an example of the data is as such

Acct CMonth.Long Dist Fax L_D_F_Tbl.Acct L_D_F_Tbl.Long dist Fax
3001 3001 3.1


So all I want to do is transfer the data from L_D_F_Tbl.Long dist Fax to CMonth. Long Dist Fax.

Can you help me?