Results 1 to 7 of 7

Thread: scrip help

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: scrip help

    I wanna to have a script that will read a sertain table in a database. If a csrtain record that is being added exists in the table already I want it not to be added into teh specific tables. If it does not exist, i want it to be added.

    Here is the logic that I wanna have:

    READ OPCSHTO
    GET CUST_CODE + LOC_CODE
    IF EXIST CUST_CODE + LOC_CODE RECORD IN DPTORGANIZATIONSLOCATIONS
    GO TO READ OPCSHTO

    ELSE
    DO ADD TO DPT.ORGANIZATIONLOCATIONS TABLE
    DO ADD TO DPTORGANIZATIONS

  2. #2
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    you can have a stored procedure with CUST_CODE & LOC_CODE as input parameters. It will first check the table for this record by doing a count(*). If this count is zero it will insert the new record.

  3. #3
    Join Date
    Jan 2004
    Posts
    164
    Originally posted by rohitkumar
    you can have a stored procedure with CUST_CODE & LOC_CODE as input parameters. It will first check the table for this record by doing a count(*). If this count is zero it will insert the new record.
    That sounds cool. Can you be a little more specific cause I really don't know how I would do a sp.

    thanks.

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

    ===================================
    CREATE PROCEDURE USP_insert_dtporgloc AS
    BEGIN
    DECLARE @CUST_CODE NCHAR(20)
    DECLARE @LOC_CODE NCHAR(20)

    DECLARE cur_OPCSHTO SCROLL CURSOR FOR
    SELECT
    CUST_CODE , LOC_CODE
    FROM
    OPCSHTO

    OPEN cur_OPCSHTO

    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @CUST_CODE and LOC_CODE = @LOC_CODE) = 0
    BEGIN
    insert into DPTORGANIZATIONSLOCATIONS values (@CUST_CODE, @LOC_CODE, ...etc etc...)
    END


    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE
    END
    CLOSE cur_OPCSHTO
    DEALLOCATE cur_OPCSHTO
    Return
    END
    GO
    ======================================

  5. #5
    Join Date
    Jan 2004
    Posts
    164
    Originally posted by rohitkumar
    I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

    ===================================
    CREATE PROCEDURE USP_insert_dtporgloc AS
    BEGIN
    DECLARE @CUST_CODE NCHAR(20)
    DECLARE @LOC_CODE NCHAR(20)

    DECLARE cur_OPCSHTO SCROLL CURSOR FOR
    SELECT
    CUST_CODE , LOC_CODE
    FROM
    OPCSHTO

    OPEN cur_OPCSHTO

    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @CUST_CODE and LOC_CODE = @LOC_CODE) = 0
    BEGIN
    insert into DPTORGANIZATIONSLOCATIONS values (@CUST_CODE, @LOC_CODE, ...etc etc...)
    END


    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE
    END
    CLOSE cur_OPCSHTO
    DEALLOCATE cur_OPCSHTO
    Return
    END
    GO
    ======================================



    Thanks a bunch...I will see how it turns out. How long have you been doing this for? Thanks for understanding.......pretty new at this.

  6. #6
    Join Date
    Jan 2004
    Posts
    164
    Originally posted by rohitkumar
    I am bad at syntax and I have not tested this one, so you might have to spend some time on it to make it working

    ===================================
    CREATE PROCEDURE USP_insert_dtporgloc AS
    BEGIN
    DECLARE @CUST_CODE NCHAR(20)
    DECLARE @LOC_CODE NCHAR(20)

    DECLARE cur_OPCSHTO SCROLL CURSOR FOR
    SELECT
    CUST_CODE , LOC_CODE
    FROM
    OPCSHTO

    OPEN cur_OPCSHTO

    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE

    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @CUST_CODE and LOC_CODE = @LOC_CODE) = 0
    BEGIN
    insert into DPTORGANIZATIONSLOCATIONS values (@CUST_CODE, @LOC_CODE, ...etc etc...)
    END


    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE
    END
    CLOSE cur_OPCSHTO
    DEALLOCATE cur_OPCSHTO
    Return
    END
    GO
    ======================================


    You think you would be able to right a descripting by each command, thatway I could understand what is going on and I can understand it better?
    thanks for your help.

  7. #7
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    I've tried writing a short description, let me know if something is missing

    =========================================
    CREATE PROCEDURE USP_insert_dtporgloc AS
    BEGIN
    /* variable declaration */
    DECLARE @CUST_CODE NCHAR(20)
    DECLARE @LOC_CODE NCHAR(20)

    /* this will fetch CUST_CODE , LOC_CODE from cur_OPCSHTO and store it in an cursor "cur_OPCSHTO" (sort of an array) */
    DECLARE cur_OPCSHTO SCROLL CURSOR FOR
    SELECT
    CUST_CODE , LOC_CODE
    FROM
    OPCSHTO

    /* open this cursor for fetching the data */
    OPEN cur_OPCSHTO

    /* fetch first of the stored values and put them in these variables */
    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE

    /* repeat the process till last record in the cursor */
    WHILE @@FETCH_STATUS = 0
    BEGIN

    /* count the number of recs in DPTORGANIZATIONSLOCATIONS having CUST_CODE , LOC_CODE. If this count is zero then insert this as a new record in the DPTORGANIZATIONSLOCATIONS table*/
    IF (select count(*) from DPTORGANIZATIONSLOCATIONS where CUST_CODE = @CUST_CODE and LOC_CODE = @LOC_CODE) = 0
    BEGIN
    insert into DPTORGANIZATIONSLOCATIONS values (@CUST_CODE, @LOC_CODE, ...etc etc...)
    END

    /* fetch next of the stored values in the cursor and put them into variables*/
    FETCH NEXT FROM cur_OPCSHTO
    INTO @CUST_CODE, @LOC_CODE
    END /* END corresponding to WHILE, the process between WHILE and END will repeat till there are no more records in the cursor*/

    /* close the cursor and deallocate the resources*/
    CLOSE cur_OPCSHTO
    DEALLOCATE cur_OPCSHTO
    Return
    END
    GO

Posting Permissions

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