• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

SQL QUERY Multiple Inserts

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
vasboz
Asked:
vasboz
  • 4
  • 2
  • 2
  • +2
1 Solution
 
Dale FyeCommented:
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
 
Chris__WCommented:
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
 
agusacilCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Chris__WCommented:
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
 
Chris__WCommented:
@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
 
agusacilCommented:
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
 
Dale FyeCommented:
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
 
Chris__WCommented:
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
 
Anoo S PillaiCommented:
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
 
vasbozAuthor Commented:
Thanks guys. The solution from anoospillai works perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now