Results 1 to 8 of 8

Thread: give me answer

  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: give me answer

    sirs,
    i am new to database field , i have one problem

    i have one table customer--->custid(pk),lname,fname

    and another table address -- > strno(pk),city(pk),state(pk),country(pk),pcode

    one relationship between above table called -- has --custid,strno,city,state,country (all are pk and fk)

    so please any one can send me how to enter the (insert) values in these table so my table relationship and its integrity maintain .

  2. #2
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    Hi

    There could be some ladies here too who won't help you because you yelled "Sirs" .. just kiddin . Please provide the exact ddl here I really can't make out the second table . However If there are only these 2 tables , first insert in Customer table then the 2nd table

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    order of customer and address is irrelevant, but the insert into the third table should be after the first 2 are populated.

  4. #4
    Join Date
    Sep 2003
    Posts
    6

    Re: Hi

    Originally posted by aashu
    There could be some ladies here too who won't help you because you yelled "Sirs" .. just kiddin . Please provide the exact ddl here I really can't make out the second table . However If there are only these 2 tables , first insert in Customer table then the 2nd table

    my mistake
    yes madam

    my table s are
    1. customers (cust_id(p key),lname,fname).
    2. has(cust_id,city,country,street no,state,postal code)
    primary key ->(cust_id,city,street no,state,country)

  5. #5
    Join Date
    Sep 2003
    Posts
    6

    Re: Hi

    Originally posted by aashu
    There could be some ladies here too who won't help you because you yelled "Sirs" .. just kiddin . Please provide the exact ddl here I really can't make out the second table . However If there are only these 2 tables , first insert in Customer table then the 2nd table

    my mistake
    yes madam

    my table s are
    1st table .

    customers (cust_id(p key),lname,fname).


    2nd table.

    has(cust_id,city,country,street no,state,postal code)
    primary key ->(cust_id,city,street no,state,country)
    foreign key (cust_id) references to customer table
    foreign key (city,country,state,street no) reference address table

    3rd table.

    address(city(pkey),country(pkey),state(pkey),stree t no(pkey),postal code)

    ----------------------
    my question is :
    how to insert in this tables without any problem of
    data integrity and concistancy

    please reply soon
    and please reply in sql format because for u i am in still in eggs for database applications

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    assuming cust_id is numeric identity:

    create procedure sp_insert_into_customer_tables (
    @lname varchar(100), @fname varchar(50), @city varchar(100), country varchar(100), @streetno varchar(100), @state varchar(100), @postalcode varchar(25)) as
    declare @cust_id int, @error int
    begin tran
    insert customer (lname, fname) values (@lname, @fname)
    select @error = @@error, @cust_id = scope_identity()
    if @error != 0 begin
    raiserror ('failed to insert', 15, 1)
    rollback tran
    return (1)
    end
    insert address (city, country, state, [street no], [postal code]) values (@city, @country, @state, @streetno, @postalcode)
    if @@error != 0 begin
    raiserror ('failed to insert', 15, 1)
    rollback tran
    return (1)
    end
    insert has (cust_id, city, country, state, [street no], [postal code]) values (@cust_id, @city, @country, @state, @streetno, @postalcode)
    if @@error != 0 begin
    raiserror ('failed to insert', 15, 1)
    rollback tran
    return (1)
    end
    commit tran
    select retval = 'successfully inserted'
    return (0)
    go

  7. #7
    Join Date
    Sep 2003
    Posts
    6

    thank

    thank u sir

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    welcome

Posting Permissions

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