Solved

SQL QUERY Multiple Inserts

Posted on 2012-04-05
10
375 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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 500 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 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