How to select data and insert those data using single sql?

Question

I want to select some data using simple sql and insert those data into another table. Both table are same. Data types and column names all are same. Simply those are temporary table of masters table. Using single sql I want to insert those data into another table and in the where condition I check E_ID=? checking part. My another problem is sometime there may be any matching rows in the table. In that time is it may be out sql exception? Another problem is it may be multiple matching rows. That means one E_ID may have multiple rows. As a example in my attachment_master and attachments_temp table has multiple rows for one single ID. How do I solve those problems? I have another problem. My master table data can insert temp table using following code. But I want to change only one column and others are same data. Because I want to change temp table status column.

    insert into dates_temp_table SELECT * FROM master_dates_table where e_id=?;

In here all data insert into my dates_temp_table. But I want to add all column data and change only dates_temp_table status column as "Modified". How should I change this code?

If matching rows exist in target table, what would you like to do? Ignore or update or delete? — Jacob, Sep 12 '13 at 05:22
I want to add requested id values into temp table and change temp_table column status as "Modified". — user2632766, Sep 12 '13 at 06:28

score 2 · Answer 1 · edited May 23 '17 at 11:46

You could try this:

insert into table1 ( col1, col2, col3,.... ) 
SELECT  col1, col2, col3, ....
FROM   table2 where (you can check any condition here on table1 or table2 or mixed)

For more info have a look here and this similar question Hope it may help you.

EDit : If I understand your requirement properly then this may be a helpful solution for you:

insert into table1 ( col-1, col-2, col-3,...., col-n, <Your modification col name here> ) 
    SELECT  col-1, col-2, col-3,...., col-n, 'modified'
    FROM table2 where table1.e_id=<your id value here>

As per your comment in above other answer:

"I send my E_ID. I don't want to matching and get. I send my E_ID and if that ID available I insert those data into my temp table and change temp table status as 'Modified' and otherwise don't do anything."

As according to your above statements, If given e_id is there it will copy all the columns values to your table1 and will place a value 'modified' in the 'status' column of your table1

For more info look here

Thanks thats work fine. But I can't understand how automatically handle when there any matching values for where condition. — user2632766, Sep 12 '13 at 04:17
I want to add requested id values into temp table and change temp_table column status as "Modified". — user2632766, Sep 12 '13 at 06:42
I edited my answer and added something. I may not be correct to understand your requirement. If so let me know with some more detail. — Shailesh Saxena, Sep 12 '13 at 13:32

score 0 · Answer 2 · answered Sep 12 '13 at 03:58

insert into tablename( column1, column2, column3,column4 ) SELECT  column1,
column2, column3,column4 from anothertablename where tablename.ID=anothertablename.ID

IF multiple values are there then it will return the last result..If not you have narrow your search..

Jacob · Accepted Answer · 2013-09-12T07:15:33.057

0

You can use merge statement if I understand your requirement correctly.

Documentation

As I do not have your table structure below is based on assumption, see whether this cater your requirement. I am assuming that e_id is primary key or change as per your table design.

MERGE INTO   dates_temp_table trgt
      USING  (SELECT * FROM master_dates_table WHERE e_id=100) src
          ON     (trgt.prm_key = src.prm_key)  
WHEN NOT MATCHED
THEN
    INSERT       (trgt.col, trgt.col2, trgt.status)
         VALUES  (src.col, src.col2, 'Modified');

More information and examples here

edited Sep 12 '13 at 07:15

answered Sep 12 '13 at 06:43

Jacob

14,463
65
207
320

I send my E_ID. I don't want to matching and get. I send my E_ID and if that ID available I insert those data into my temp table and change temp table status as 'Modified' and otherwise don't do anything.How should I do this? – user2632766 Sep 12 '13 at 06:59
@user2632766 But you have mentioned in your question that you have matching rows! If you have matching rows, then use your primary key to match and use `E_ID' in where condition. I have edited my answer. – Jacob Sep 12 '13 at 07:06
I have removed the update part so it will not do any update if rows are matching. And if rows are not matching, it will insert rows and `status` column will be marked as `Modified` – Jacob Sep 12 '13 at 07:16
I think some misunderstanding. I have two table date master and date temp.Date temp table haven't any data.Then system user pass id and I want if that id available in date master table whole data copy into temp table(one row) except STATUS column.Because I want to change status as 'Modified'. – user2632766 Sep 12 '13 at 07:51
@user2632766 If it didn't solve your problem, you need post your table structure and sample data. – Jacob Sep 12 '13 at 07:55

How to select data and insert those data using single sql?

3 Answers3