Multiple records in the same table is a well-known technique. But in this post, we’ll look at inserting into multiple tables using a single expression. This isn’t a new concept, although it isn’t commonly known.
This is a simple method for separating data from a table into numerous tables.
Consider the below datasets for the practice.
Customers-Agents-Suppliers Data:
ID, Name, City, EmailID, Phone, Work
100001, Steve, Baltimore, steve@gmail.com, 12220010, Customer
100002, Frank Muller, Philadelphia, frankmuller@gmail.com, 12220011, Customer
100003, Melanie Pike,Pasadena, pike@gmail.com, 12220012, Customer
100004, Tony Curtis, Houston, tony@curtis.com, 12220013, Customer
200001, John Smith,Pasadena, john@gmail.com, 2200021, Agent
200002, Jordan Katharine,Philadelphia, jordank@hotmail.com, 2200022, Agent
200003, James William, Houston, james@james.com, 2200023, Agent
200004, Bella, Baltimore, bella2022@gmail.com, 2200024, Agent
300001, Scott, Pasadena, scott@gmail.com, 330001, Supplier
300002, Amanda Lee, Baltimore, leeaman@hotmail.com, 330002, Supplier
300003, Sara Dyer,Houston, dyersara@hotmail.com, 330003, Supplier
300004, Connie Angland, Philadelphia, angloconnie@gmail.com, 330004, Supplier
Hive> FROM PersonsData
INSERT INTO tbCustomers
SELECT ID, Name, City, EmailID, Phone, Work WHERE work=’Customer’
INSERT INTO tbAgents
SELECT ID, Name, City, EmailID, Phone, Work WHERE work=’Agent’
INSERT INTO tbSuppliers
SELECT ID, Name, City, EmailID, Phone, Work WHERE work=’Supplier’;
The query above will assist in the separation of data from a composite data source into its separate tables. Since it was provided at the start, no “From” clause is necessary in each SELECT statement.
Hope you find this article helpful.
Please subscribe for more interesting updates.
Big Data and SQL 
One comment