| Author |
Topic |
|
JeffS23
Posting Yak Master
212 Posts |
 Posted - 2007-12-28 : 17:09:14
|
| The following script will not run, but I need to do something like this. I have a table in SQL that holds Employee Leave transactions. I need to add lines to that table via a SQL script on the frist of the month. I can schedule a job to do that, but the script won't run. My problem is the EmpLeaveId field. It is a number that is auto incremented by the HR program that puts the remaining data in this table. How can I insert a number into my row that has is the max value thus far plus 1? This is an example script below that I was trying to use to insert one record only into the table. If I can get this to work, I can automate it from there. Thanks!!insert into EmpLeaveTemp (EmpUID, CompanyID, EmpLeaveId, LeaveTypeId, StartDate, EndDate, LeaveQty, LeaveAdType, LstModBy, LstModDate, Notes)Values (20, 6, select MAX(EmpLeaveId)+1 from EmpLeave, 17, 01/01/2008, 01/01/2008, 8.0000, 'A', 'Massey', GetDate(), 'Automated Personal') |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-12-28 : 17:14:19
|
| can you have EmpLeaveId as identity field?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-28 : 17:46:44
|
| Just in case he can't use identity:insert into EmpLeaveTemp (EmpUID, CompanyID, EmpLeaveId, LeaveTypeId, StartDate, EndDate, LeaveQty, LeaveAdType, LstModBy, LstModDate, Notes)select 20, 6, MAX(EmpLeaveId)+1, 17, '01/01/2008', '01/01/2008', 8.0000, 'A', 'Massey', GetDate(), 'Automated Personal'from EmpLeaveTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
INSERT statement