Insert the data from Old table to new table, ID from new table and update for the corresponding row in old table. Most optimized way?


HOME ยป Databases

I have two table. OLDTABLE and NEWTABLE.
I need to insert some data from oldtable to newtable and the get the ID from new table and update in the oldtable for that corresponding row. the newtable ID has seperate column in oldtable which is null by default.


One way is to get the ID from both table while inserting the data in new table and then later find the data in oldtable and update it. Which is simpler way.


Can anyone suggest some optimized way to do so?


Related to : Insert the data from Old table to new table, ID from new table and update for the corresponding row in old table. Most optimized way?
insert data from one table to another table table name genrated runtime without declaring the table fields
Databases

I have two table say table1 and table2 all the columns in both table are same but table2 has some extra column in which i want to insert a some default value and name of table is generated runtime so table name can be changed so i cant declare the field of table.


Mysql INSERT data in Table 2 for each row in Table 1
Databases

I have two tables.


Table A is a list of questions that needs to be filtered by question type.
e.g. SELECT * from TableA WHERE Qtype = "whatever"


I then need to add four new records to Table B for each record pulled by the query on TableA.


Table A and Table B join on an ID number - TableA.id = TableB.questionid


I am presuming I will need to run it four times, once for each record that I add.


I am getting confused on the FOR EACH command in conjunction with an INNER JOIN


Would appreciate any help!


MySql, Insert data to a table row from another table row (this row is just been created)
Databases

Not sure how to describe my problem, but lets try..


I am working on a small news portal php + mysql project
I have two tables in mysql database.


1: news (
n_id (primary key)
n_title
n_text
)
2: news_image (
ni_id (primary key)
n_id (foreign key from news table)
ni_name
ni_caption
)

(n_ prefix is for new and ni_ is prefix for news_image)


news table will hold the news story and news_image table will store the names and captions for the images of the news. separate table for news images is because on later

Need to get all the Table data items (one set per Table row) in each row of html table into the servlets
Databases

I have a Table in my html page which contains three table data items (one checkbox, one textbox and one select box in each row) in each row.
I need to retrieve the table rows in which the checkbox is checked into the servlets..
I strucked in the middle..Please fix the issue..


Thank you all in advance..


SSIS: How to transfer data from table A to table B and then update table A flag column for each row
Databases

I need to crate a package in SSIS which will selectively transfer data from Table A to table B based on a flag in Table A and after the transfer update the flag in Table A.


Please advice on the approach to this scenario and the different ways in which this can be done effectively.


I would also like to mention that i have never worked on SSIS packages before.


MySQL INSERT into new table, SELECT from old table, UPDATE old table with new key
Databases

Okay, here goes...

Code:

INSERT INTO retailers
(retailername, retailerdesc, retailerwebsite, retailerurl, active)
SELECT
datasource_retailername,
datasource_retailerdesc,
datasource_retailerwebsite,
datasource_retailertrackurl,
1
FROM
datasources_retailers_idx AS i
LEFT OUTER JOIN
retailers AS r
ON
r.retailername NOT REGEXP REPLACE(i.datasource_retailername, ' ','.+')
UPDATE datasources_retailer
can not insert row in a table while data redirecting from parent table to child table in trigger(stored procedure)
Databases

I am using postgres version: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

Description:

I have one parent table and that parent table has many child tables. I want to redirect the data from parent table to child table (while insertion)

(same As this link: Example Link ) (example of ice cream company)

I did following things...

create table tsttbl1 (id integer, name text);

create table tsttbl1_hour0 (LIKE tsttbl1) INHERITS (tsttbl1);

CREATE OR REPLACE FUNCTION insert_tsttbl1_hourbase() RETURNS TRIGGER
AS $$
BEGIN
    RAISE NOTICE 'Hi...%', NEW;
    execute 'INSERT INTO ' || TG_TABLE_SCHEMA|| '.tsttbl1_hour0 values
'|| NEW;
    -- INSERT INTO tsttbl1_hour0 values (NEW.*)';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

create trigger trigger_insert_tsttbl1_hourbase before insert on
tsttbl1 FOR EACH ROW EXECUTE PROCEDURE insert_tsttbl1_hourbase();

Here,

parent table:- tsttbl1

child table:- tsttable1_hour0

Trying to achieve: insert into tsttbl1 (id, name) values (0,'xyz');

should insert data in tsttable1_hour0 instead of tsttbl1;

But When i tried to insert the row, i am getting like this...

ccc=# insert into tsttbl1 (id,name) values (1, 'abc');
LOG:  statement: insert into tsttbl1 (id,name) values (1, 'abc');
NOTICE:  Hi...(1,manan)
ERROR:  column "abc" does not exist
LINE 1: INSERT INTO act1.tsttbl1_hour0 values (1,abc)
                                                 ^
QUERY:  INSERT INTO act1.tsttbl1_hour0 values (1,abc)
CONTEXT:  PL/pgSQL function insert_tsttbl1_hourbase() line 4 at
EXECUTE statement

Additional Information:

-> This data redirection can also be achieved by rule. But for my case, this insertion is frequently, so rule might be costly compare to trigger...

so i want to achieve this by trigger only.

-> if i un comment the line "INSERT INTO tsttbl1_hour0 values (NEW.*)'" .. row is inserted successfully



Privacy Policy - Copyrights Notice - Feedback - Report Violation - RSS 2017 © bighow.org All Rights Reserved .