Solved

SQL QUERY Multiple Inserts

Posted on 2012-04-05
10
370 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now