insert and update

i have a text file which i want to input the data in it to a database, i means the new data be inserted and if there exists old ones, it be updated without the "key violation " error.

how to do it?

thansk

mzlxkats
mzlxkatsAsked:
Who is Participating?
 
Stuart_JohnsonConnect With a Mentor Commented:
if you have a table that looks like this:

MYKEY         FIELD1   FIELD2   FIELD3
1             DAH      0        TRUE
2             DOO      100      TRUE
3             DEE      -5       FALSE

and you want to add a new record if one doesn't already exist, BUT update a record where it does already exist, then try this:

procedure DoIt(var NewKey: Integer; Field1: String; Field2: Integer; Field3: Boolean);
var
  Exists: Boolean;

begin
  Exists := false;
  with TQuery.Create(nil) do
    try
      DatabaseName := 'WHATEVER';
      SQL.Add('select count(DBKEY) from TABLENAME');
      SQL.Add('where DBKEY=:DBKEY');
      Prepare;
      ParamByName('DBKEY').asInteger := NewKey;
      Open;  
      Exists := RecordCount > 0;
      Close;
      SQL.Clear;  
      if Exists then
        begin
          SQL.Add('update TABLENAME');
          SQL.Add('set FIELD1=:FIELD1,');
          SQL.Add('FIELD2=:FIELD2,');
          SQL.Add('FIELD3=:FIELD3');
          SQL.Add('where DBKEY=:DBKEY');
        end
      else
        begin
          SQL.Add('insert into TABLENAME');
          SQL.Add('DBKEY, FIELD1, FIELD2, FIELD3)');
          SQL.Add('values (:DBKEY, :FIELD1, :FIELD2, :FIELD2');
        end;
      Prepare;
      ParamByName('DBKEY').asInteger := NewKey;
      ParamByName('FIELD1').asString := Field1;
      ParamByName('FIELD2').asInteger := Field2;
      ParamByName('FIELD3').asBoolean := Field3;
      try
        ExecSQL;
      except
        on E: Exception do
          MessageDlg(E.Message, mtError, [mbOK], 0);
      end;
    finally
      Free; {TQuery}
    end;
end;

This is coded from memory, so it may need a bit of correcting to run (but not much!).

The stored procedure issue is something different again.  It depends on the database you're using.  I'm only familiar with MS-SQL Server, so if you're using that, I can definately post the SQL for that and the Delphi code.

Hope that helps,

Stu
0
 
Stuart_JohnsonCommented:
If your database is uniquely indexed, then you can do

if newdatakey=dbkey then
  update the database
else
  insert into database

If you're using a Database which has stored procedures, you can do this in one line of code in Delphi :)

Hope this helps,

Stu
0
 
mzlxkatsAuthor Commented:
hello stu

do you have code more in detail?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.