I am relativley new to SQL and have a question about identity fields.
I am creating a script to run everynight to insert records into a support table in a database. one of the fields is a identity field that is updated everytime a record is added locally or over the web.
Some records that are added into the database locally by users do not get added into theis support table, but so those new people entered in can use the website a entry must be added to this support table.
I am working on a script to take the records that where added by users and automatically put them in every night using a basic schedueled job.
The identy number is updated everynight in a table that collects all the important identiy numbers. I would like to use this table to alter the seed value and then increment by one every time a new record is added. This is my only sticking point so far.
These are kind of what the tables hold, Table1 on holds basic customer info and Table2 holds the login information for authentication purposes.
When users enter in new customers they don't assign any login information. I want to automatically assign it every night.
The records will be added when no other users are entering data. So i need to look at the last used login_id number and increment it by one, then insert the information. this would continue until all the records from the day beofre have been added to the supplimental table.
The database is a 3rd party system. They let us do almost whatever we want to do as long as it does not interfear with their triggers and procedures. I talked to them first and they said not to add any triggers without their approval, but that i could do this in a batch scenario.
here is basically what the rest would be
Insert into t_cust_login
login_id(this is what my question asked about)
from (criteria from earlier post)
This is basically what i am trying to do. my question was if i left the login_id out of this part of the query, would the inserted records continue the login_id numbers behind the previous 33000 records? Or do i have to set the seed value every time i run this query, and if this is the case how do i do that so it is automaticaly done?
from t_cust_login right outer join t_eaddress
on t_cust_login.customer_no = t_eaddress.customer_no inner join t_customer
on t_customer.customer_no = t_eaddress.customer_no inner join t_address
on t_address.customer_no = t_eaddress.customer_no
where t_cust_login.customer_no is null and
t_eaddress.create_dt >= dateadd(day, -1, getdate())and
t_eaddress.address is not null and
t_eaddress.address != ' ' and
t_eaddress.customer_no != 0
i still need some way to set the seed value in the insert query for the login_id field. The DB does use a table that holds all of the last used ID from all the tables in the system.
I just checked that and it is only a integer field. When i started this I assumed it was a Identity field.
I know this changes the whole dynamic.
Your example is kind of what i thought i might have to do. Can i write it so that it only referances the logidtable once, or would it have to access it, insert a record, adjust it, and then start the process again?
Also, how would i add that to the query i just posted.