• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

StoreProcedure select and Insert

Hallo all..

I have Table A.
           Table B.
            Table C.
            Table D.
            Table X.

Each of them have columns "ID","Temp2","Temp3","Temp4","Temp5","Temp6"

I need a store procedure that will extract all the rows from Table x and insert them in
Table A, B, C and D ,, taking these tables as variables
Source table x destination table A..D

Thanks in Advance
0
ZURINET
Asked:
ZURINET
  • 9
  • 9
  • 5
  • +1
1 Solution
 
sonicefuCommented:

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;

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
ZURINETAuthor Commented:
Sorry Guys..
I am looking for Microsoft SQL 2000 solution
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
sonicefuCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0
 
ZURINETAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go
CREATE PROCEDURE copy_x
  (@source_table sysname
  , @dest_table sysname
  )
AS
 DECLARE @sql nvarchar(1000)
 
  SET @SQL = 'INSERT INTO [' + @dest_table + ']  ( ID, Temp2, Temp3, Temp4, Temp5)
    SELECT ID, TEmp2, Temp3, Temp4, Temp5 
      FROM [' + @source_table + '] '
  
  EXEC (@SQL)

Open in new window

0
 
imitchieCommented:
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
0
 
imitchieCommented:
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
0
 
ZURINETAuthor Commented:
Instead of  SELECT ID, TEmp2, Temp3, Temp4, Temp5

can one write  SELECT *

?
0
 
imitchieCommented:
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
0
 
imitchieCommented:
zurinet: have a look at http:#20394230
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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...
0
 
ZURINETAuthor Commented:
Will try out "imitchie's code

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

0
 
ZURINETAuthor Commented:
Hallo Imitchies

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

Is it possible?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Your code have two ends.. but no beginning :-)
I see 2 begin ...   see at the end of the lines " if ... "
0
 
imitchieCommented:
angel got quick eyes

while charindex(',', @dest) > 0 begin
  ....
end
if len(@dest) > 1 begin
  ....
end
0
 
ZURINETAuthor Commented:
Thanks :-)

0
 
ZURINETAuthor Commented:
Hi imitchie
Error with the solution

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

0
 
imitchieCommented:
Are the table structures EXACTLY the same?
And what is your EXEC statement exactly, please?
0
 
ZURINETAuthor Commented:
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."
0
 
imitchieCommented:
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
0
 
ZURINETAuthor Commented:
Hoi imitchie

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

Thanks.

0
 
imitchieCommented:
how does angel's solution get rid of your IDENTITY_INSERT problem???
0
 
imitchieCommented:
What a waste of time..
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 9
  • 9
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now