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 DPTORGANIZATIONSyou 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.|||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.|||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
======================================|||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.|||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.|||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