INSERT INTO Statement Help

[DDTiff]

Hello Everyone,

Can you please let me know what is wrong with the query?

INSERT INTO Junction (Unique_ID, ID, WBS, Name, Duration_In_Days, Start_Date, Finish_Date, Recurring, Milestone, Resource_Unique_ID, Resource_ID, Resource_Name, Resource_Initials )
SELECT Task.Unique_ID, Task.ID, Task.WBS, Task.Name, Task.Duration, Task.Start_Date, Task.Finish_Date, Task.Recurring, Task.Milestone, Assignment.Resource_Unique_ID, Assignment.Resource_ID, Assignment.Resource_Name, Assignment.Resource_Initials
FROM Task, Assignment;

The Junction table is the table I want to paste new records from other table into. My goal is to transfer the records from the Task table which has nine attributes, and these nine attributes are the same as the ones in the Junction table, and the rest of the attributes in the Junction Table come from the Assignment table.

Thus, when I excecute the above query to Append the Junction table, it pastes the new records right below the last record in the Junction table instead of replace the existed records of the first nine attributes in the Junction table.

For example, I previously have 48 records in the Junction take, and 100 records in the Task table. I want the extra 52 records in the Task table added to the Junction table to make it 100 records instead of just 48 records, where all the attribute names in the Task table matches with the names in the Junction table.

Thank you very much for your help.


DTiff

 

[Gila47ac]

>> Can you please let me know what is wrong with the query?

First, your FROM clause is probably incomplete, which will give your SELECT statement far too many rows. Because it has no qualifiers, the SELECT will perform what is called a cross join, combining every row in Task with every row in Assignment. (So if you have 100 rows in the Task table and 100 rows in the Assignment table, the SELECT statement will generate 100*100=10,000 rows.) You need to specify how those two tables are combined; e.g., &quot;...FROM Task, Assignment WHERE Task.<some_column_in_this_table> = Assignment.<the_matching_column_in_this_table>&quot;

Second, you apparently believe that the INSERT will replace rows in the Junction table when it finds a match on the key column(s). Nope, no such luck; INSERT does not behave that way. When the INSERT tries to insert the first SELECT row that you would consider a match (it would consider a duplicate) in the Junction table, the processing will stop with an error (details depend upon which DBMS you are using).

 

[johnwm]

Go to SQL BOL and look up UPDATE:

http://www.microsoft.com/sql/techinfo/productdoc/70/books.asp

Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'