Insert Into query

I want to insert into an existing table a row if does not already exists.

For instance; If Exists(Select * from table1
Where table1.recordid = table1.recordid And not exists
actioncode = 0)
Insert into table1 values (0,.....) --Here I want to replicate everything from an already existed record except for one column - actioncode. Does anyone have any suggestions?

Can't you insert and then update the one column?

Yeah That ^

in your If statememnt that checks if a row exists you could execute the Insert statement if it doesn't and an UPDATE statement if it does.

HTH

This is what I have but I'm problems debugging my logic.

Declare
@actioncode int,
@actiondate datetime,
@assigneeid char(6),
@assigneeldid int,
@assigneesubservid char(6),
@assigneesubservldid int,
@delivered int,
@documentcustodianid int,
@documentcustodianldid int,
@interumfunderid char(6),
@interumfunderldid int,
@lenderbaseid int,
@loanrecordid char(8),
@minnumber char(25),
@originatorid char(6),
@originatorldid int,
@origsubservid int,
@origsubservldid int,
@sequencenumber int,
@MERSRegistrantLDID int,
@MERSRegistrantID int,
@MOM int,
@GestationLDID int,
@GestationID int
Declare cursor_actioncode Cursor
For

--Select actioncode from loan_mers

/* open cursor */
Open cursor_actioncode
/* get row */
Fetch next from cursor_actioncode

While @@fetch_status = 0
Begin

IF NOT EXISTS(SELECT actioncode FROM loan_mers lm
WHERE actioncode = 0 and lm.loanrecordid = lm.loanrecordid)
INSERT INTO loan_mers
(0, @actiondate, @assigneeid, 1, 0, 0, 0, 0, 0, @interumfunderid, 1, 1, @loanrecordid, @minnumber,
@originatorid, 1, 0, 0, 1, 0, 0, 1, 0, 0)
Select * from loan_mers
Fetch next from cursor_actioncode
End

Deallocate cursor_actioncode

I don't claim to fully understand what your doing, but, aren't you missing the key word VALUES out of your insert statement, and a BEGIN from your IF statement...

What are the error messages your getting?

Yes but I am trying to pull values from an already existed row for the loan number. I'm not getting any errors just not inserting the row for a particular record.

What am I doing, is the following, inserting an additional row for a record. For instance,

For loan number 7002345, if action code = 0 does not exist then insert action code = 0, other field values from the row record loan number 7002345 that exists for other action codes.

As a illustration:

Action Code ColumnName ColumnName Loan record Columname,......
102 0 1 7002345 0

I want to script to poll the records in this table to check for loan record is equal and an action code = 0 exists or not. If it doesn't then add row

Action Code ColumnName ColumnName Loan record ColumnName,.......
0 0 1 7002345 0

I hope that makes more sense.

First question: Can there be multiple records in loan_mers that have an action_code that is not 0
If so, how do you select the record you want to copy, with top 1 or with a column?

I'm going from the assumption there is only one record in the loan_mers in the following code:

INSERT INTO loan_mers (actioncode,actiondate,asigneeid,......) --list the actual filednames
SELECT
0
, a.actiondate
, a.assigneeid
, 1
, 0
, 0
, 0
, 0
, 0
, a.interumfunderid
, 1
, 1
, a.loanrecordid
, a.minnumber
, a.originatorid
, 1
, 0
, 0
, 1
, 0
, 0
, 1
, 0
, 0
FROM loan_mers a
WHERE a.loanrecordid NOT IN (SELECT loanrecordid FROM loan_mers WHERE actioncode = 0)

NOTE if you can have multiple and want the latest you have to add something like
AND col1 in (select max(col1) from loan_mers b where a.loanrecordid = b.loanrecordid)

hope this helps