I have a timesheet app and I want to create a SP to insert a list of names in to a field in one table from another table.. but only if that name is not already there ????? can anybody help with the SQL syntax
Table 1 contains generic user info including username. Table 2 contains Username, a week number, and a couple of flag fields .. has the user submitted their timesheet, has it been approved, etc. At present when a user submits their timesheet a record is written to table 2. But if a user hasn't at least submitted their timesheet there is no record. If you want to quickly see who hasn't submitted a time sheet for a particular week you could do a fairly ugly sort of lookup against Table 1 ( give me list of users from table 1 where they dont show up in table 2 for a particular week - but this doesn't take into account new users who shouldn't have a timesheet a previous week, and users who have left and been disabled but their time hasn't been recharged yet. So I want the system to write/update the users record in table 2 but to also generate an initial record ( autogenerate username and week number for all users not already in table 2 for that week) for all active users listed in table 1. This way I can run a report which is simply give me list of users from table 2 where submitted is null for a particular week.
looks like a duff database design to me. data really shouldn't be replicated in two different tables - they should be normalised so they're joined by a unique key. If I were you, I'd look into normalisation and relational concepts a bit more deeply. If it WAS relationally organised this lookup would be relatively simple. as it is it's going to be an ugly hack which is resource intensive.<BR><BR>j
Data isn't being replicated???? The first table ( in another database) contains all sorts of general user specifc info which is utilized in 4 other data systems as well as this one. The second table contains a row entry per week for possibly every user listed in the first table. It is about as normalized as it is going to get ???