Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    19

    Unanswered: Setting Identity Fields in Queries

    Hello,

    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.

    Any help would be greatly appreciated.
    Kenn Brown

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you posty the insert?

    be default, you don't have to "update" the identity...it automatically get's added...

    I know I'm missing something....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    19
    Sorry i know it is a little confusing the way i said it, let me try again. I will scale it down to help explain what i am trying to do.

    Table 1
    cust_no, name, email

    Table 2
    login_id, cust_no, email, password, username

    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.

    Does that clear it up a bit. I hope.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You could use a trigger and have added right away...

    And if you want a batch process that's no prob either

    INSERT INTO Table2(ColList)
    SELECT collist FROM Table1 o
    WHERE NOT EXISTS (SELECT * FROM table2 i WHERE o.UserId = i.UserId)

    Just make the loginid an identity column and don't make it part of your col list.

    And what abouth the passwrod and username...where do you expect to get those?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    19
    a trigger is out of the question. The database is verylarge and this is just a small part of it. The batch process is the only solution right now.

    As for the password and username, the username will be the email address and the user name will be made up of bits and peices of information in table1.

    The login_id is already a identity field. with about 33000 records. I want to add records after those and keep in line with the already given id fields.

    If i leave off the login_id field when i do the insert script will it automatically generate the id number behind the rest?

  6. #6
    Join Date
    Jan 2004
    Posts
    19
    here is the criteria that i will need to use for the insert query:

    from t_cust_login right outer join t_eaddress
    on t_cust_login.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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well I don't know why a trigger is out of the question...

    and that's only a partial insert statement...

    But what does this mean?


    Originally posted by KenDingo
    If i leave off the login_id field when i do the insert script will it automatically generate the id number behind the rest?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Posts
    19
    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,
    cust_no,
    username,
    password
    )

    Select
    login_id(this is what my question asked about)
    cust_no,
    username,
    password

    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?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If the column is defined as an identity column yes...if not no.

    Can you post the tables ddl?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2004
    Posts
    19
    thanks,

    I am not sure how to post the ddl of the table, and I am not sure if my company would want me to.

    I am going to give this a whirl. I must have been overthinking what i was trying to do.

    I will post back and let you know if it worked

    thanks again brett

    Kenn

  11. #11
    Join Date
    Jan 2004
    Posts
    19
    okay back for more. Just doing the insert without telling the table what to put in the login_id field did not work.

    Here is the complete script:

    insert into t_cust_login
    (
    customer_no,
    n1n2_ind,
    login_type,
    login,
    [password],
    eaddress_no,
    primary_ind,
    inactive
    )

    select
    t_eaddress.customer_no,
    '1',
    '2',
    t_eaddress.address,
    left(t_customer.lname,3)+right(t_address.postal_co de,3)+left(t_customer.fname,2),
    t_eaddress.eaddress_no,
    'Y',
    'N'

    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.

    Do you have any more suggestions for me?

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well it sounds like you don't have an IDENTITY Column...

    Can you go in to Enterprise Manager and design the table.

    Let us know the def of that column...

    YOu might need to do

    DECLARE @x int

    SELECT @x=MAX(LoginId)+1 FROM yourLogIdTable
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2004
    Posts
    19
    You are correct sir,

    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.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Gotta split...but you should sit down and visio out a process model...

    Describe all the things that need to happen, and then convert them in to code...

    I'll check in to it in the am


    It's Margaritta time!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •