duplicate a record in more tables

[filipe26]

Hi i want to duplicate a record in a query but i want to do this without passing thru field by field.I have 300 tables and what i want to do is putting a table by a parameter to select the same code to any table i want.thanks a lot.

 

[lespaul]

I use the following to pass the entire dataset to a function or procedure. Then I can use the dataset in the function just like I would outside of it:

procedure PrintPostponeReminder(const JurorsData : TDataSet);
begin
  While not JurorsData.eof do
  begin
  //process each record of the passed in dataset
  end;
end;

to call it:

procedure ProcessPostPoneReminder(ADate : string);
begin
  With dmJMS.qryProcessPPReminders do
  begin
    SQL.Clear;
    SQL.Add('SELECT * FROM JMPMAIN WHERE TERMDATE = ' + QuotedStr(ADate) + ' AND STATUSCD = ''PP''');
    Active := True;
//check if the query returned any records and if so, print letters
    If not isempty then
        [COLOR=red]PrintPostponeReminder(dmJMS.qryProcessPPReminders);[/color]
  end;
end;

Leslie

 

[filipe26]

Hi,yes that will work if i had 5 or 6 tables,but imagine to do that for 300 tables.JPMMAIN has to be a parameter and has any other way to with a simple function duplicate a entire record?

 

[lespaul]

You said:

i want to duplicate a record in a query ... without passing thru field by field

I have provided you a way to pass the results of a query to a function without passing thru field by field.

If that isn't what you wanted perhaps you could explain your situation in a little more detail because:

I have 300 tables and what i want to do is putting a table by a parameter to select the same code to any table i want.thanks a lot.

doesn't make much sense.

Are you trying to say that you want to create a function or procedure that takes a table name as a parameter and then performs a specific process on the table?





Leslie

 

[filipe26]

yes,sorry my english.the specific process is to duplicate a record or copy an existing one to another.

Thanks a lot for your patience.

 

[lespaul]

It's not your english that's making this difficult, it's that you are not providing any details on what you want to do? Perhaps an example?

Leslie

 

[filipe26]

Are you trying to say that you want to create a function or procedure that takes a table name as a parameter and then performs a specific process on the table?

But i have answered yes.

 

[lespaul]

Here is a procedure that takes a table name as a parameter and then does something with it:

procedure Something (ATableName : string);
begin
  with qrySomething do
  begin
     SQL.Add('INSERT INTO ' + ATableName + ' REST OF QUERY');
     ExecSQL;
  end;
end;

Without more details that's the best I can do.





Leslie

 

[LucieLastic]

Like LesPaul's but with a select?

:
OK := GetData(Tablename);
:

function TMyForm.GetData(Tablename : string): boolean;
begin
try
try
query.sql.clear;
query.add('select * from '+TableName);
query.open;
:
result := true;
except
result := false;
end
finally
query.close;
end;
end;


If you're using SQLServer, Sybase or similar, you can retrieve the names of all your tables by querying one of the system tables.

 

[filipe26]

Hi ,thanks a lot.And what about duplicate the record that matches the query without passing thru all fields?

 

[LucieLastic]

Like this?

insert into <table>
select * from <othertable>
where ...

If this is right, pass the 2 table names in the function header.

 

[filipe26]

no i want to copy a record from the same table and add paste in a new one.

 

[LucieLastic]

Post up some example records of data, before and after adding the 'duplicate' record so we are all clear on exactly what you're wanting.

Is it like this, then?
eg

insert into sametable (a,b,c)
select a,b,c+10 from sametable <--c is changed as dangerous to have duplicate records in same table - not good at all

...and you don't want to list out all the fields?

You can query the system tables to find out all the fields in each table.

If this e.g. is right, why are you wanting to do this this 'globally' across most of the tables?

 

[VintageWine]

Here is a stored procedure I wrote some time ago to do just this. I wrote it for SQL Server 6.5. If you are usign SQL Server 7+ it can be simplified by using one longer variable instead of tt1, tt2 etc.

CREATE PROCEDURE q_CopyRecord
(@TableName varchar(32),
@KeyField varchar(32),
@ExistingValue varchar(32),
@NewValue varchar(32))
 AS

/* This procedure copys records in a table to new records in that table with a new, specified, key.
The parameters are:
@TableName - obvious
@KeyField - obvious
@ExistingValue - The key value of the record to be copied.
@NewValue - The Key value for the new record.
If the Key Values are strings then they must be enclosed with inverted commas.

e.g. to copy a scheme
EXECUTE q_CopyRecord Schemes, SchemeCode, 'VSI200','VS200'
*/


/* Create a list of field names for this table */
DECLARE ColumnList INSENSITIVE CURSOR FOR
  SELECT
   c.name
  FROM
   sysobjects o
   JOIN syscolumns c ON c.id=o.id
  WHERE
   o.name=@TableName



DECLARE
  @COLUMN_NAME varchar(32),
  @tt1 varchar(255),
  @tt2 varchar(255),
  @tt3 varchar(255),
  @tt4 varchar(255),
  @tt5 varchar(255),
  @tt6 varchar(255),
  @tt7 varchar(255),

  @tt8 varchar(255),
  @tt9 varchar(255),
  @tt10 varchar(255),
  @tt11 varchar(255),
  @tt12 varchar(255),
  @ttt varchar(255),
  @nn  int

SELECT
  @tt1="",
  @tt2="",
  @tt3="",
  @tt4="",
  @tt5="",
  @tt6="",
  @tt7="",
  @tt8="",
  @tt9="",
  @tt10="",
  @tt11="",
  @tt12="",
  @nn=1

/* Cycle through the fields creatng a list of them
The list is kept in multiple local variables because
255 chars would not be enough.  The system is very rough.

It can be improvedor dispensed with for SQL Server 7+
because longer variables are allowed */

OPEN ColumnList

WHILE 1=1 BEGIN

    FETCH ColumnList INTO
      @COLUMN_NAME

    IF (@@FETCH_STATUS<>0) BREAK

    IF(@COLUMN_NAME<>@KeyField)	BEGIN
	IF @nn<=5
	    SELECT @tt1=@tt1+','+@COLUMN_NAME
 	ELSE IF @nn<=10
	    SELECT @tt2=@tt2+','+@COLUMN_NAME
 	ELSE IF @nn<=15
	    SELECT @tt3=@tt3+','+@COLUMN_NAME
 	ELSE IF @nn<=20
	    SELECT @tt4=@tt4+','+@COLUMN_NAME

 	ELSE IF @nn<=25
	    SELECT @tt5=@tt5+','+@COLUMN_NAME
 	ELSE IF @nn<=30
	    SELECT @tt6=@tt6+','+@COLUMN_NAME
 	ELSE IF @nn<=35
	    SELECT @tt7=@tt7+','+@COLUMN_NAME
 	ELSE IF @nn<=40
	    SELECT @tt8=@tt8+','+@COLUMN_NAME
 	ELSE IF @nn<=45
	    SELECT @tt9=@tt9+','+@COLUMN_NAME
 	ELSE IF @nn<=50
	    SELECT @tt10=@tt10+','+@COLUMN_NAME
 	ELSE IF @nn<=55
	    SELECT @tt11=@tt11+','+@COLUMN_NAME
 	ELSE SELECT @tt12=@tt12+','+@COLUMN_NAME


SELECT @nn=@nn+1
    END
END

CLOSE ColumnList
DEALLOCATE ColumnList


/* Run the statement that will add our new record(s) */

EXECUTE(
"INSERT INTO "+@TableName+
"("+@KeyField+@tt1+@tt2+@tt3+@tt4+@tt5+@tt6+@tt7+@tt8+@tt9+@tt10+@tt11+@tt12+")"+
"SELECT "+
@NewValue+@tt1+@tt2+@tt3+@tt4+@tt5+@tt6+@tt7+@tt8+@tt9+@tt10+@tt11+@tt12+"FROM "+@TableName+" WHERE "+@KeyField+"="+@ExistingValue
)

You could wrap another similar stored procedure round it to cycle through the tables in your database, as LucieLastic has suggested.

Have fun
Simon

 

[svanels]

1) I want to duplicate a record in a query.

A query is a result of data you take out of a table in most cases.

Question which database are you using?

Could you use SQL doing this?

example "update mytable set mycolumn = newValue where City = New York" ?

If you could do this outside your application, with the SQL-explorer or some other tool, you could create a SQL procedure in delphi to this using the SQL sintaxe

Regards

Steven van Els
SAvanEls@cq-link.sr