|
Thank you for your quick reply Chris.
I would be having around 5 million records per month in my table. That is why I thought of partitioning the table on the basis of months to generate month-wise reports. May be I got it wrong, but my understanding was that the data would be inserted only in the child tables and not the master table.
Due to the amount of data, the query being fired takes 4-5 mins to return any results (basically it is using the WITH RECURSIVE clause). I used indexes and also tried optimizing the query but it hardly made any difference. I might have to write the query in a different manner, but the problem is still with the amount of data in the table.
Kindly suggest on how to resolve this issue.
Thank you in advance.
|
|
|
|
|
hello all,
is it possible to load data from csv file into an oracle table using an ODBC bulk command ? 
|
|
|
|
|
why don't you use Sqlldr.exe for Bulk insert
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
Because I am coding an application in VB6, I have only an ODBC link to the database
|
|
|
|
|
hatemtaleb wrote: Because I am coding an application in VB6
I'm not trying to be rude, but why are you coding in a dead and unsupported language.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
You can Use Sqlldr.exe in VB6..,
Why are you going to break your head again for bulk insert concept., there is already well tested component is giving by the Oralce..,
Use the Shell executions to execute the sqlldr.., No need of ODBC..,
Thanks
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
Hi Guys,
I am writing code for an insert part. I would like to write an store procedure which do both insert and update of my table. Imean when user call SP and pass the parameter if it was regarding insert add data otherwise update them.
is it possible?
here is my code
USE [Hostel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SprocInsertUpdatePerson]
(-- Definition of Storeprocedure's parameter
@id int,
@fname nvarchar(50),
@srname nvarchar(50),
@gender nvarchar(15),
@dob datetime,
@transactionid int
)
AS
Declare @ReturnValue int
if (@id IS NULL) --New Item
Begin
set identity_insert Person_prs off
insert into Person_prs
(
id_prs,
fname_prs,
srname_prs,
gender_prs,
dob_prs,
transactionid_prs
)
values
(
@id,
@fname ,
@srname,
@gender,
@dob ,
@transactionid
)
Select @ReturnValue = Scope_Identity()
End
else
Begin --Update Item
Update Person_prs
Set
--Id = @id,
fname_prs = @fname,
srname_prs = @srname,
gender_prs = @gender,
dob_prs = @dob,
transactionid_prs = @transactionid
where id_prs = @id
Select @ReturnValue = @id
End
IF (@@ERROR != 0)
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN @ReturnValue
END
the problem is how to set the parameter for update or insert. in update I have to pass parameter to do search while in insert don't need it. however I should define my parameter.
does any one has idea,please?
|
|
|
|
|
The answer is in your code only
if (@id IS NULL) --New Item if the @id is null the insert statement will works
else
Begin --Update Item in else part you write for update commands..
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
thnks for ur reply, but i got confused. because I will get error in passing parameter for id in update and insert.
|
|
|
|
|
What you are doing is exactly the method we use.
Assumes ID is an identity primary key.
Test the variable one the way in, note the isnull treatment
If IsNull(@ID,0)=0
begin
future3839 wrote: set identity_insert Person_prs off
This is wrong, leave the identity insert alone but remove the @ID from the value list
After the insert get the new identity value to pass back to the client
Set @ID = Scope_Identity
end
else
begin
Do the update
Return the ID as the result
Select @ID as ID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
this is my code
USE [Hostel]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SprocInsertUpdatePerson]
(-- Definition of Storeprocedure's parameter
@id int,
@fname nvarchar(50),
@srname nvarchar(50),
@gender nvarchar(15),
@dob datetime,
@transactionid int
)
AS
Declare @ReturnValue int
if (@id IS NULL) --New Item
Begin
insert into Person_prs
(
--id_prs,
fname_prs,
srname_prs,
gender_prs,
dob_prs,
transactionid_prs
)
values
(
--@id,
@fname ,
@srname,
@gender,
@dob ,
@transactionid
)
Select @id = Scope_Identity()--Returns the last identity value inserted into an identity column in the same scope.
End
else
Begin --Update Item
Update Person_prs
Set
--Id = @id,
fname_prs = @fname,
srname_prs = @srname,
gender_prs = @gender,
dob_prs = @dob,
transactionid_prs = @transactionid
where id_prs = @id
Select @id as id
End
IF (@@ERROR != 0)
BEGIN
RETURN -1
END
ELSE
BEGIN
RETURN @ReturnValue
END
the id_prs field is autonumber. I run SP by this value
execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200
select * from Person_prs
after select table nothing insert into table.while I have to add data and when pass parameter can update it.
could you please help me??
|
|
|
|
|
future3839 wrote: execute SprocInsertUpdatePerson 11,'XXX','YYY','male','1988/10/10',1200
This is an update, you passed in an ID that is > 0
This will insert a record
execute SprocInsertUpdatePerson 0,'XXX','YYY','male','1988/10/10',1200
I would also return the the @ID value as the return value, it is very useful in the client.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I think you have to pass like this for insert
execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200
First Parameter should be NULL
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
thanks a lot. if I insert your code absolutely I don't have any problem with inserting.
execute SprocInsertUpdatePerson null,'XXX','YYY','male','1988/10/10',1200
the issue is when I am doing update who can I send parameter.
for instance, I inserted 20 record by above code. now, I wanna update one of those record who have user entered id.
Update Person_prs
Set
--Id = @id,
fname_prs = @fname,
srname_prs = @srname,
gender_prs = @gender,
dob_prs = @dob,
transactionid_prs = @transactionid
where id_prs = ????????????????????????????????????????????????????????
|
|
|
|
|
I got it.its ok
thanks a lot for your help
|
|
|
|
|
Hi,
I tried this code to get the record Identity with C# Windows Form application bu tit's not working.. can any one help please...
CREATE OR REPLACE FUNCTION fn_create_resume(resume_name character)
RETURNS int AS
$$
DECLARE
record_identity int;
BEGIN
insert into resume_details (resume_id, resume_name) values (default, resume_name);
return resume_id;
END;
$$ LANGUAGE plpgsql;
|
|
|
|
|
Try the following
BEGIN
insert into resume_details (resume_id, resume_name) values (default, resume_name);
select resume_id into record_identity from resume_details where resume_name = resume_name;
return record_identity;
END;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
|
I have the following data returned buy a simple SQL query
| Site | X | Y | Z |
--------------------
A 1 2 3
B 4 5 6
C 7 8 9
I need to get it to the following format
| A | B | C |
--------------
1 4 7
2 5 8
3 6 9
Any ideas / code snippits most welcome
Mark
|
|
|
|
|
|
I have done that!
Can't work it out tho, as all the example sum values and I dont want to do that.
TSQL is not my strong point
|
|
|
|
|
Well Here[^] is an example on Pivot two or more columns. Read through it and try out.
|
|
|
|
|
Do I understand that both your sites (A,B,C) and your attributes (X,Y,Z) are variable in number ? And you are counting some sort of occurrence ?
My pivot tables show the months across the top, the salesman down the left and the number in the grid represents the number of orders closed in that month. Sound familiar ?
Your Pivot might look something like this:
select Salesman, [1] as Jan,[2] Feb,[3] Mar,[4] Apr,[5] May,[6] Jun,<br />
[7] Jul, [8] Aug, [9] Sep, [10] Oct, [11] Nov, [12] Dec<br />
from (<br />
select salesman, OrderID, datepart(month,order_date) as month<br />
from orders<br />
where datepart(year,order_date) = 2010)<br />
AS SourceTable<br />
PIVOT (COUNT(OrderID) FOR month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
|
|
|
|
|
Thanks for your reply, I will have a look at your code now.
My sites are a variable, but X,Y,Z are fixed (they are different types of accidents, and the data stored represents the number of occurances)
Just incase anybody want to have a go here is a create script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Example](
[Site] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[X] [int] NOT NULL,
[Y] [int] NOT NULL,
[Z] [int] NOT NULL
) ON [PRIMARY]
insert into Example(Site, X,Y,Z) Values ('A',1,2,3)
insert into Example(Site, X,Y,Z) Values ('B',4,5,6)
insert into Example(Site, X,Y,Z) Values ('C',7,8,9)
select * from Example
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
