[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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