?
Solved

SQL QUERY Multiple Inserts

Posted on 2012-04-05
10
Medium Priority
?
378 Views
Last Modified: 2012-04-06
I am need of a query to perform the following additions with one sql query.

My original record (lets call it TABLE1):

item     class_type          class name
X418    CC-SUP               ADW
X338    CC-SUP               ADW

What I am hoping to achieve is:

item     class_type       class name
X418    CC-SUP             ADW
X418    CC-CATT           TGG
X338    CC-SUP             ADW
X338    CC-CATT           TGG

I have another table, lets say TABLE2.

I would like the INSERT to occur where TABLE1.item = TABLE2.item in which case it will duplicate the item value in TABLE1 and then add the CC-CATT and TGG in the new row.

I would like to do this across all matches with one sql query.
0
Comment
Question by:vasboz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37811871
After making a copy of your table!!!!

INSERT INTO yourTable (Item, Class_Type, [Class name])
SELECT Item, "CC-CATT", "TGG"
FROM yourTable
WHERE [Class_Type] = "CC-SUP" AND [Class name] = "ADW"

This will copy only those records where the class_type and [class name] are the same as in your example.
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37812366
Hello,

Fyed's query is exactly what you need to simply update TABLE1.  To have the records setup the way you want in TABLE 2, you'll need either a 2 part query (a SELECT INTO or INSERT statement for the records already in TABLE 1 and the INSERT statement from feyd's post for adding the additional records) or a single query that makes use of a UNION ALL.

Here's the 2 parter:
INSERT INTO TABLE2 ([item], [class_type], [class name])
SELECT [item], [class_type], [class name]
FROM TABLE1

then run

INSERT ... {Fyed's code above}

The single step approach is as follows:
INSERT INTO TABLE2 ([item], [class_type], [class name])
SELECT tbl1.[item], tbl1.[class_type], tbl1.[class name]
FROM (SELECT t1.[item], t1.[class_type], t1.[class name] FROM TABLE1 t1
UNION ALL
SELECT t2.[item], 'CC-CATT' AS t2.[class_type], 'TGG' AS t2.[class name] FROM TABLE1 t2) tbl1

Basically, UNION ALL a table to itself to return duplicates.

Thanks,
Chris
0
 
LVL 4

Expert Comment

by:agusacil
ID: 37812413
If you do have two tables, I propose this :

insert into table2(ITEM,class_type,class_name)
select a.item, 'CC-CATT', 'TGG' from table1 a join table2 b on a.item = b.item
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 3

Expert Comment

by:Chris__W
ID: 37812415
Hello,

I just reread your statement, and if you are trying to add just the rows that have an ID in TABLE 2, run the query I listed with this change:

INSERT INTO TABLE2 ([item], [class_type], [class name])
SELECT tbl1.[item], tbl1.[class_type], tbl1.[class name]
FROM (SELECT t1.[item], t1.[class_type], t1.[class name] FROM TABLE1 t1
UNION ALL
SELECT t2.[item], 'CC-CATT' AS t2.[class_type], 'TGG' AS t2.[class name] FROM TABLE1 t2) tbl1 INNER JOIN TABLE2 tbl2 ON tbl1.[item] = tbl2.[item]

If this is being run repeatedly, include a WHERE clause at the bottom to exclude previous updates:
WHERE tbl2.[class_type] NOT IN ('CC-CATT', 'CC-SUP' {, etc.})
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37812446
@agusacil - That will not add the CC-SUP record, only the CC-CATT.  If I understand the request correctly, they are looking to add both records in one query.
0
 
LVL 4

Expert Comment

by:agusacil
ID: 37812474
After I reread the question, this may be the correct one :

insert into table1(ITEM,class_type,class_name)
select a.item, 'CC-CATT', 'TGG' from table1 a join table2 b on a.item = b.item

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 37812475
Sorry about that, missed the Table2 thing entirely

Chris, looks like you've got this one.  I think I will bow out and go to lunch.

Dale
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37812589
Final note here - if the alias on the Union table gives you problems (tbl1), just leave it out (and take it off of the select fields).

Please update if this isn't working.
0
 
LVL 7

Accepted Solution

by:
Anoo S Pillai earned 2000 total points
ID: 37814838
Hi,

Believe that you are going to insert data into table1. A simple join caluse will help you to identify the qualifying rows.  A single insert can INSERT the required rows to the target table.  If you accidentally rerun the query, in order to avoid the duplication of already inserted 'CC-CATT' rows you could use EXCEPT clause.

Altogether the INSERT statement will be something like the following:-

INSERT INTO TABLE1 ( Item , class_type , class_name )
SELECT      T1.Item , 'CC-CATT' , 'TGG'
FROM      TABLE1 T1  INNER JOIN TABLE2 T2
ON            T1.Item = T2.Item
EXCEPT
SELECT      T1.Item , class_type , class_name
FROM      TABLE1 T1


My test table and Test data:-
--DROP TABLE TABLE1;
--DROP TABLE TABLE2;
CREATE TABLE TABLE1 ( Item VARCHAR(10) , class_type varchar(10) , class_name VARCHAR(10))
INSERT INTO TABLE1 VALUES ( 'X418' , 'CC-SUP' , 'ADW')
INSERT INTO TABLE1 VALUES ( 'X338' , 'CC-SUP' , 'ADW')
INSERT INTO TABLE1 VALUES ( 'Y100' , 'CC-SUP' , 'ADW')

CREATE TABLE TABLE2 ( Item VARCHAR(10) , class_type varchar(10) , class_name VARCHAR(10))
INSERT INTO TABLE2 VALUES ( 'X418' , 'CC-SUP' , 'ADW')
INSERT INTO TABLE2 VALUES ( 'X338' , 'CC-SUP' , 'ADW')
INSERT INTO TABLE2 VALUES ( 'Z100' , 'CC-SUP' , 'ADW')
                   
SELECT * FROM TABLE1 ORDER BY Item
SELECT * FROM TABLE2 ORDER BY Item


INSERT INTO TABLE1 ( Item , class_type , class_name )
SELECT      T1.Item , 'CC-CATT' , 'TGG'
FROM      TABLE1 T1  INNER JOIN TABLE2 T2
ON            T1.Item = T2.Item
EXCEPT
SELECT      T1.Item , class_type , class_name
FROM      TABLE1 T1

SELECT * FROM TABLE1 ORDER BY Item
0
 

Author Closing Comment

by:vasboz
ID: 37815497
Thanks guys. The solution from anoospillai works perfectly!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question