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.
vasbozAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 FyeOwner, Developing Solutions LLCCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vasbozAuthor Commented:
Thanks guys. The solution from anoospillai works perfectly!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.