StoreProcedure select and Insert

create or replace procedure transfer_data (src varchar2)
is
begin
  execute immediate 'insert into X select * from '||src;
  commit;
 exception
  when others then
        null;
end;

Select allOpen in new window

Hi,

  you posted in MS SQL Server, Oracle AND MySQL zone(s), please specify which database you are actually using.
sonicefu posted some Oracle code, altough I don't think that is what you are looking for...

Sorry Guys..
I am looking for Microsoft SQL 2000 solution

oh sorry !
angelIII ! you are right, i did'nt check the zone from this question is ?
but i am sure it'll work for oralce if ZURINET wants to check it in oracle

please check out the below code snippet.

now, I am not 100% sure what you mean with this:
>in Table A, B, C and D ,, taking these tables as variables

can you clarify, please

CREATE PROCEDURE copy_x
AS
  INSERT INTO TABLE_A ( ID, Temp2, Temp3, Temp4, Temp5)
    SELECT ID, TEmp2, Temp3, Temp4, Temp5 
      FROM TABLE_X
 
  INSERT INTO TABLE_B ( ID, Temp2, Temp3, Temp4, Temp5)
    SELECT ID, TEmp2, Temp3, Temp4, Temp5 
      FROM TABLE_X
 
  INSERT INTO TABLE_C ( ID, Temp2, Temp3, Temp4, Temp5)
    SELECT ID, TEmp2, Temp3, Temp4, Temp5 
      FROM TABLE_X
 
  INSERT INTO TABLE_D ( ID, Temp2, Temp3, Temp4, Temp5)
    SELECT ID, TEmp2, Temp3, Temp4, Temp5 
      FROM TABLE_X

Select allOpen in new window

Hallo Angel..

I need something like @Table                  sysname,

I need to submit the table name during run time and not hard coded..
this concerns the source and destination code.

Thanks for your help

create proc copytablesimple ( @src sysname, @dest varchar(1000) )
as
declare @smt varchar(4000)
while charindex(',', @dest) > 0 begin
  set @smt = 'insert into ' + left(@dest, charindex(',', @dest)-1) + ' select * from ' + @src
  exec (@smt)
  set @dest = substring(@dest, charindex(',', @dest)+1, 4000)
end

usage:
exec copytablesimple 'X', 'A,B,C,D'
1. dest tables separated by comma
2. if you need to, you can customize the insert and select column

Instead of  SELECT ID, TEmp2, Temp3, Temp4, Temp5

can one write  SELECT *

?

fix:

Create proc copytablesimple ( @src sysname, @dest varchar(1000) )
as
declare @smt varchar(4000)
while charindex(',', @dest) > 0 begin
  set @smt = 'insert into ' + left(@dest, charindex(',', @dest)-1) + ' select * from ' + @src
  exec (@smt)
  set @dest = substring(@dest, charindex(',', @dest)+1, 4000)
end
if len(@dest) > 1 begin
  set @smt = 'insert into ' + @dest + ' select * from ' + @src
  exec (@smt)
end

zurinet: have a look at http:#20394230

>can one write  SELECT *
if the tables have the same format you described, you should stick away from the select *.
it will give you problems, one day, when the tables are modified without "knowing" of how this procedure works...

Will try out "imitchie's code

looks like it will run more faster and economical:-)

Hallo Imitchies

Your code have two ends.. but no beginning :-)

Is it possible?

>Your code have two ends.. but no beginning :-)
I see 2 begin ...   see at the end of the lines " if ... "

angel got quick eyes

while charindex(',', @dest) > 0 begin
  ....
end
if len(@dest) > 1 begin
  ....
end

Thanks :-)

Hi imitchie
Error with the solution

Disallowed implicit conversion from data type datetime to data type int, table

Are the table structures EXACTLY the same?
And what is your EXEC statement exactly, please?

Sorry
Just made a copy of two tables..

I am getting this error now

"An explicit value for the identity column in table 'temA' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Well, that involves testing for whether the destination table has an identity column, and setting IDENTITY INSERT ON which is a lot more complicated.  Not only do you need IDENTITY_INSERT, you also need to know the exact names of the fields that exist in the table.  That script will take at least 2 more hours to build and test and 1 to debug.

What is the purpose of copying into tables that themselves have IDENTITY columns?  If it's not a simple copy/dump operation, then what about INSERT failures?

I'm sorry but I'm stopping at simple copy 1->multiple, no identity columns, no error handling

Hoi imitchie

Thanks for your help
I will be taking solution from angelIII
it simple and less complicated..

Thanks.

how does angel's solution get rid of your IDENTITY_INSERT problem???

What a waste of time..