ZURINET
asked on
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","Temp 4","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
I have Table A.
Table B.
Table C.
Table D.
Table X.
Each of them have columns "ID","Temp2","Temp3","Temp
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
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...
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...
ASKER
Sorry Guys..
I am looking for Microsoft SQL 2000 solution
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
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
ASKER
Instead of SELECT ID, TEmp2, Temp3, Temp4, Temp5
can one write SELECT *
?
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
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...
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...
ASKER
Will try out "imitchie's code
looks like it will run more faster and economical:-)
looks like it will run more faster and economical:-)
ASKER
Hallo Imitchies
Your code have two ends.. but no beginning :-)
Is it possible?
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 ... "
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
while charindex(',', @dest) > 0 begin
....
end
if len(@dest) > 1 begin
....
end
ASKER
Thanks :-)
ASKER
Hi imitchie
Error with the solution
Disallowed implicit conversion from data type datetime to data type int, table
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?
And what is your EXEC statement exactly, please?
ASKER
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."
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
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
ASKER
Hoi imitchie
Thanks for your help
I will be taking solution from angelIII
it simple and less complicated..
Thanks.
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..
Open in new window