|
CREATE TABLE nlma.acc_monthly_closing_details
(
str_voucher_code character varying(70) NOT NULL,
int_transaction_no integer NOT NULL,
str_voucher_no character varying(50) NOT NULL,
dt_voucher_date timestamp without time zone NOT NULL,
int_payable_id integer NOT NULL,
num_amount double precision NOT NULL DEFAULT 0,
char_cash_chq_auth character varying(1) NOT NULL,
char_credit_debit character varying(1) NOT NULL,
CONSTRAINT pk_monthly_closing_details_vcode_tranno PRIMARY KEY (str_voucher_code, int_transaction_no)
)
WITH (
OIDS=FALSE
);
CREATE OR REPLACE RULE insert_into_acc_monthly_closing_details_2010_01 AS
ON INSERT TO nlma.acc_monthly_closing_details
WHERE new.dt_voucher_date >= '2010-01-01'::date AND new.dt_voucher_date <= '2010-01-31'::date
DO INSTEAD INSERT INTO nlma.acc_monthly_closing_details_2010_01 (str_voucher_code, int_transaction_no, str_voucher_no, dt_voucher_date, int_payable_id, num_amount, char_cash_chq_auth, str_chq_no, dt_chq_date, char_credit_debit)
VALUES (new.str_voucher_code, new.int_transaction_no, new.str_voucher_no, new.dt_voucher_date, new.int_payable_id, new.num_amount, new.char_cash_chq_auth, new.str_chq_no, new.dt_chq_date, new.char_credit_debit);
CREATE TABLE nlma.acc_monthly_closing_details_2010_01
(
CONSTRAINT acc_monthly_closing_details_2010_01_dt_voucher_date_check CHECK (dt_voucher_date >= '2010-01-01'::date AND dt_voucher_date <= '2010-01-31'::date)
)
INHERITS (nlma.acc_monthly_closing_details)
WITH (
OIDS=FALSE
);
CREATE INDEX acc_monthly_closing_details_2010_01_dt_voucher_date
ON nlma.acc_monthly_closing_details_2010_01
USING btree
(dt_voucher_date);
the same way i have another 11 child tables, when i want to insert data using
INSERT INTO nlma.acc_monthly_closing_details
SELECT
a.str_voucher_code, b.int_transaction_no, a.str_voucher_no,a.dt_voucher_date,
a.int_payable_id,b.num_amount,a.char_cash_chq_auth,a.str_chq_no,
a.dt_chq_date, b.char_credit_debit,
FROM
nlma.acc_voucher_master a, nlma.acc_voucher_details b
WHERE
a.int_voucher_month=int_month and a.int_voucher_year=int_year and a.str_voucher_code=b.str_voucher_code;
It will Insert in Both Master as well as one child table.
IF any mistake in above code.. Please mention...
|
|
|
|
|
Not sure the purpose of the child tables. You also need a rule for when an update occurs on the master table since you are replicating data in two tables.
Why not only have the master table with single inserts and create views on the master table that have the hard coded date ranges built into the views? 
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]
|
|
|
|
|
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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
