Insert records from a table to other table

Note:   While inserting records from a table to other table  values clause must not be used

Example:
     
           insert into highAchiever (name, age)  (select name, age from salesperson where salary > 100000);

SQL JOINS

              You have following tables 

Salesperson	Customer
ID Name Age Salary 1 Abe 61 140000 2 Bob 34 44000 5 Chris 34 40000 7 Dan 41 52000 8 Ken 57 115000 11 Joe 38 38000	ID Name City Industry Type 4 Samsonic pleasant J 6 Panasung oaktown J 7 Samony jackson B 9 Orange Jackson B

Orders

Number order_date cust_id salesperson_id Amount 10 8/2/96 4 2 540 20 1/30/99 4 8 1800 30 7/14/95 9 1 460 40 1/29/98 7 2 2400 50 2/3/98 6 7 600 60 3/2/98 6 7 720 70 5/6/98 9 7 150

Given the tables above, find the following:

a. The names of all salespeople that have an order with Samsonic. 

solution:

SELECT distinct name FROM `salesperson` WHERE id in(select salesperson_id from customer,orders where id=cust_id AND name='Samsonic');

if you write  above query like:

SELECT distinct name FROM `salesperson` WHERE id=(select salesperson_id from customer,orders where id=cust_id AND name='Samsonic');
It is invalid query because subquery returning multiple records so it can't be compared. 

Insert multiple rows in mysql

MySql  :)


insert into customer values(4,'Samsonic','pleasant','J'),
(6,'Panasung','oaktown','J'),
(7,'Samony','jackson','B'),
(9,'Orange','Jackson','B');

Insert into Multiple rows in single query: SQL

Method 1 - Traditional InsertINSERT INTO #SQLAuthority (ID, Value)VALUES (1, 'First');INSERT INTO #SQLAuthority (ID, Value)VALUES (2, 'Second');INSERT INTO #SQLAuthority (ID, Value)VALUES (3, 'Third');


TRUNCATE TABLE #SQLAuthority;

Method 2: INSERT…SELECT

INSERT INTO #SQLAuthority (ID, Value)

SELECT 1, 'First'UNION ALLSELECT 2, 'Second'UNION ALLSELECT 3, 'Third';

TRUNCATE TABLE #SQLAuthority;

Method 3: SQL Server 2008+ Row Construction

-- Method 3 - SQL Server 2008+ Row ConstructionINSERT INTO #SQLAuthority (ID, Value)VALUES (1, 'First'), (2, 'Second'), (3, 'Third');