I need to be able to copy  a row from within a particular table,  modify the fields that comprise the primary key before then copying it back   I think that the correcct way is to use a Temporary table as this action is done only on  a strictly per user basis within a C/S setup   ie other users cannot access the temporary table

Am I correct and help with the SQL needed would be appreciated

Thanks

Mike

Michael


>I need to be able to copy a row from within a particular table, modify the fields that comprise the primary key before then copying it back I think that the correcct way is to use a Temporary table as this action is done only on a strictly per user basis within a C/S setup ie other users cannot access the temporary table
>
>Am I correct and help with the SQL needed would be appreciated

Colour me confused. What you seem to be saying is that you want to alter some columns for a specific row, and that its c/s and you're going to use SQL. If that's right there's no need for copying or a temporary table.

The SQL is quite simple

UPDATE table
SET
columnname1=newvalue1,
columnname2=newvalue2,
....
....
columnnameN=newvalueN

WHERE
wherecolumn1=oldvalue1 AND
wherecolumn2=oldvalue2 AND
....
....
wherecolumnN=oldvalueN

If you only want to update one row the where clause must select out that row otherwise all the rows that have been selected out will have the specified columns set to the new values.

If you need to do some messing around first eg you need the old values to work out what the new values are then:

1. use a query component to select the row
2. work out what the new values should be
3. construct an sql statement as a string variable
4. either use a query to execute the statement or use the built in .Execute facilities in the session or database components to do so

Roy Lambert

Just to clarify  :  I want to clone the row apart from  the primary key which obvoiusly has to be unique
What I was thinking of doing was something like the following

CREATE Temporary TABLE NewTable (LIKE Patterns);
Insert into NewTable select * from Patterns where PtPattern='9388';

Modify the primary key here then copy it back

Thanks

Mike

Michael,

<< Modify the primary key here then copy it back  >>

As long as you can empty the existing table first (EMPTY TABLE <TableName> IGNORE CONSTRAINTS), before inserting the rows back into the table, then that will work.

I say "you can", because obviously there are multi-user usage implications to doing so.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>As long as you can empty the existing table first (EMPTY TABLE <TableName> IGNORE CONSTRAINTS), before inserting the rows back into the table, then that will work.

You obviously need more coffee or I'm totally misunderstanding Michael's two posts. I think he just wants to add a new row where all but the primary key is the same as an existing row, not replace the whole table.

IF the new primary key values can be determined without extracting the old row first a simple INSERT statement should work eg

INSERT INTO MyTable

select
NEW_MsgNo,
_MD5,
_fkStaff,
_Private,
_fkContacts,
_Comments,
_Size,
_Subject,
_Sender,
_emSender,
_Colour,
_Reply,
_DisplayFormat,
_Encoding,
_TimeStamp,
_Destination,
_emDestination,
_InOutInd,
_aList,
_Message,
_Graphics,
_Recipients,
_Flags,
_Encoded

FROM MyTable
WHERE
_MsgNo = 6

at least it seems to work here. The only downside is having to list all the fields. I cheat a little bit and use SELECT LIST(Name,#13) FROM Information.TableColumns WHERE TableName = 'MyTable'

Roy Lambert

Roy is correct

I need to create a new table row having a unique primary key but with all the remaining fields being the same  I was hoping to have a method that did not have to be altered every time the structure of the table changed

Mike

Michael

<<I need to create a new table row having a unique primary key but with all the remaining fields being the same  I was hoping to have a method that did not have to be altered every time the structure of the table changed>>


The approach you suggest will work. I think its a bit messy, and I'd want to wrap it all in a transaction on the target table to make sure no one else can do this sort of thing or alter fields whilst its in progress (that would apply to my suggestion as well).

Depending on how often this cloning a row would occur, and how performance critical it is you could generate the complete SQL on the fly each time, or generate a skeleton which gets completed for each run. I'm assuming you're using Delphi and not one of those naff languages from MS or other supplier.

Here's one doing a full clone using tables

function CloneRecord(tblSource, tblClone: TnlhTable; BestTry: boolean = True): boolean;
var
Cntr: integer;
fNam: string;
begin
Result := True;
tblClone.Open;
tblClone.FieldDefs.Update;
tblSource.FieldDefs.Update;
if tblClone.FieldCount = tblSource.FieldCount then begin
 tblClone.Insert;
 for Cntr := 0 to tblClone.FieldCount - 1 do begin
  fNam := tblClone.FieldDefs[Cntr].Name;
  if tblSource.FieldDefs.IndexOf(fNam) > -1 then begin
   tblClone.FieldByName(fnam).Assign(tblSource.FieldByName(fNam));
  end else begin
   if not BestTry then begin
    Result := False;
    tblClone.Cancel;
    Break;
   end;
  end;
 end;
end else Result := False;
end;

Since its interesting to do I'll knock up a function to do what you want using SQL and post when done. It will be in Delphi but there's no reason why you couldn't convert to SQL/PSM


Roy Lambert

Michael


Here you go

function RowClonerSQL(const tbl: string; DB: TEDBDatabase): string;
var
Qry: TEDBQuery;
FldList: TStringList;
IdxList: TStringList;
WhereClause: string;
Cntr: integer;
begin
{Create the temporary query that will be used throughout}
Qry := TEDBQuery.Create(nil);
Qry.SessionName := DB.SessionName;
Qry.DatabaseName := DB.DatabaseName;
Qry.RequestSensitive := True;
{Get a list of all the columns in the table}
Qry.SQL.Text := 'SELECT LIST(Name,#13) FROM Information.TableColumns WHERE TableName = ' + QuotedStr(tbl);
Qry.ExecSQL;
if not Qry.Fields[0].IsNull then begin
 FldList := TStringList.Create;
 FldList.StrictDelimiter := True;
 FldList.CommaText := Qry.Fields[0].AsString;
 Qry.Close;
{Next get a list of the columns making up the primary key}
 Qry.SQL.Text := 'SELECT LIST("ColumnName") FROM Information.ConstraintColumns';
 Qry.SQL.Add('WHERE');
 Qry.SQL.Add('TableName = ' + QuotedStr(tbl));
 Qry.SQL.Add('AND');
 Qry.SQL.Add('ConstraintName = (SELECT Name FROM Information.Constraints WHERE TableName = ' + QuotedStr(tbl) + ' AND Type = ''Primary Key'')');
 Qry.ExecSQL;
 if not Qry.Fields[0].IsNull then begin
  IdxList := TStringList.Create;
  IdxList.StrictDelimiter := True;
  IdxList.CommaText := Qry.Fields[0].AsString;
  Qry.Close;
  for Cntr := 0 to IdxList.Count - 1 do begin
   FldList.Text := StringReplace(FldList.Text, IdxList[Cntr], 'PK' + IntToStr(Cntr + 1), [rfIgnoreCase]);
   IdxList[Cntr] := IdxList[Cntr] + ' = OldPK' + IntToStr(Cntr + 1);
  end;
  WhereClause := 'WHERE ' + #13 + StringReplace(IdxList.Text, ',', ' AND ', [rfReplaceAll]);
 end;
end;
Result := 'INSERT INTO ' + tbl + #13 + ' SELECT ' + #13 + FldList.Text + WhereClause;
Qry.Free;
if Assigned(FldList) then FldList.Free;
if Assigned(IdxList) then IdxList.Free;
end;

There's a lot more code than your method, but you can run this at the start of the application, store the result in a string (say PatternsCloner) then you have a small procedure to substitute actual old & new values (remember to use a new string - I've stuffed myself lots of times by forgetting) and execute the string using database.execute. This will cope for any table structure and, as long as you re-run the RowClonerSQL after any restructuring, it will cope with dynamic table restructuring.

Roy Lambert

Thanks Roy very much apprerciated and FYI I am using Delphi XE7

Mike

Michael

>Thanks Roy very much apprerciated and FYI I am using Delphi XE7

My sympathies

Roy