Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jonny1409
Posting Yak Master
133 Posts |
 Posted - 2007-10-04 : 10:03:27
|
| Hello,I have a table in my SQL DB called Cycles.I also have a table called Employees.In the employees table, there are about 2000 records.In the Cycles table, there are the employee numbers of about 200 of these 2000 employees.I'd like to add a record into the Cycles table for each of the 1800 employees that aren't already there.How can I do this please ?Thanks in advance |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-04 : 10:06:39
|
| [code]Insert into Cycles(col1, col2,...)select e.col1, e.col2,...from Employees e Join Cycles c on e.key = c.keywhere c.key is null[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-04 : 10:15:47
|
| Thanks harsh_athalye,That looks like it will give me what I want.Can I just ask you one thing though ?The fields in Cycles are EERef, ID, DateFrom and DateTo (with ID being autogenerated)Will this field being autogenerated cause a problem ?Also I want the DateFrom and DateTo to be the same date for all, not taken from the Employee Table - again will this cause a problem ? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-04 : 10:25:10
|
| If ID column is autogenerated, exclude it from column list in Insert. Also which date you want to use for FromDate and ToDate if it is not coming from Employee table? hard-coded one?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-04 : 10:25:42
|
| Yes, the date is hard coded. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-04 : 10:27:47
|
| [code]Insert into Cycles(EERef, DateFrom, DateTo, ... )select e.EERef, '20/01/2006', '20/01/2006', ...from Employees e Join Cycles c on e.key = c.keywhere c.key is null[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-04 : 10:30:29
|
| Ignore |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-10-05 : 04:13:13
|
| Hi harsh_athalye,I've tried this but it didn't work - it shows "0 rows affected" in Query Analyser when I run this code. |
|
|
|
|
|
|
|
|
Update Statement Syntax Please