Thursday, January 30

Select Into

SELECT INTO statement copies data and its structure from one table and inserts it into a new table.The new table will be created with the column-names and data types as defined in the SELECT statement. New column names can be given using the AS clause in select statement.

Syntax:
SELECT *
INTO table_new 
FROM table_exists;
Or 
SELECT column_name(s)         //copy only the columns specified  
INTO table_new                  
by us to the new table
FROM table_exists;


This can come in handy when you want to duplicate a table,taking backup for a table, As temp table for manipulating data before doing on actual table, copying only the structure of a table, and also copying a table from database to another database.


Use DestinationDb;

SELECT
INTO Table_new 
FROM [database].[Schema_name].OldTable;

Examples:

Here I'm using ' Mytest_db' database which has a table called 'currency'. Using below select into query we will create a backup or duplicate of currency table as currency_backup.
use Mytest_db;

SELECT *
INTO currency_backup
FROM currency;
Output:
Select * from currency_backup;

Now lets copy a table called 'Person.Address' from 'AdventureWorks2008' database to 'Mytest_db' database, Using below query.

use Mytest_db;

SELECT *
INTO Person_address
FROM AdventureWorks2008.Person.Address;

Output:
Select * from Person_address;

Now lets see this example to Copy only a few columns from more than one table into the new table where Currencykey is (6,98,16). Here we use 'AdventureWorksDW2008R2' database and tables such as DimCurrency and FactCurrencyRate; 

Select Distinct a.Currencykey,a.CurrencyName,b.AverageRate 
Into Currency2014 From DimCurrency a 
Inner Join FactCurrencyRate b 
On a.Currencykey=b.Currencykey
Where a.Currencykey in(6,98,16);

Output:
select * from Currency2014;

SELECT INTO statement can also be used to create a new, empty table(copy only structure of a table) using the schema of another; by adding a WHERE clause that causes the condition to evaluate to false. See the below query.

Select
Into Currency2014_A
From Currency2014 
Where 1=2;           //where 1=2 condition evaluate to false, so                                     no data is copied only the structure is taken 

Output:
select * from Currency2014_A;






Friday, January 24

Miscellaneous Tasks 3


CREATE TABLE Schedules
(
        Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        EmpName NVARCHAR(255) NOT NULL,
        StartDate DATETIME NOT NULL,
        EndDate DATETIME NOT NULL,
        Roster  NVARCHAR(50) NOT NULL
);
GO

INSERT INTO Schedules
values('Jiss','20-Oct-13','26-Oct-13','_*___**'),
      ('Jom','20-Oct-13','26-Oct-13','*_*__*_'); 
-------------------------------------------------------------------------------
The Input table Table looks Like this

Id
EmpName
StartDate
EndDate
Roster
1
Jiss
20-Oct-13
26-Oct-13
_*___**
2
Jom
20-Oct-13
26-Oct-13
*_*__*_

The Roster Column shows the day worked i.e that person is present on that day between StartDate and EndDate
The symbol  * shows that person is present for that day and
The symbol  _  shows that person is absent  for that day
For eg:  Jiss is absent for 20,22,23,24 and Jom is absent for 21,23,24,26

The output table should like this
EmpName
Date
HasWorked
Jiss
20/10/2013
0
Jom
20/10/2013
1
Jiss
21/10/2013
1
Jom
21/10/2013
0
Jiss
22/10/2013
0
Jom
22/10/2013
1
Jiss
23/10/2013
0
Jom
23/10/2013
0
Jiss
24/10/2013
0
Jom
24/10/2013
0
Jiss
25/10/2013
1
Jom
25/10/2013
1
Jiss
26/10/2013
1
Jom
26/10/2013
0

Note:From Roster Column HasWorked column has to be determined,
     For Present day by 1 and Absent day by 0 for each date

Solution:


WITH DateList
AS
(

SELECT startdate [Date] ,ID,empname,
         substring(Roster, 1, 1) as Chars,
         stuff(Roster, 1, 1, '') as Roster,
         1 as RowID from Schedules
UNION all
SELECT [Date] +1,ID,empname,
         substring(Roster, 1, 1) as Chars,
         stuff(Roster, 1, 1, '') as Roster,
         RowID + 1 as RowID FROM DateList WHERE [Date] <(select  enddate from Schedules where ID=1)
)
SELECT EmpName,CONVERT(VARCHAR(10), [Date],120) [Date],
case when Chars = '_' then 0
else 1
end HasWorked FROM DateList order by [Date]


Thursday, December 12

Miscellaneous Tasks 2

CREATE TABLE Items
(
Id               INT,
Name         VARCHAR(50),
Cost          MONEY
);


INSERT INTO Items VALUES 
(1, 'Pen', 20),(1, 'Pencil', 30),
(2, 'Pencil', 30),(3, 'Pen', 30),
(3, 'Pencil', 40),(4, 'Pen', 30),
(5, 'Paper', 40),(5, 'Pencil', 30),
(6, 'Pencil', 40);


Select * from  Items;

Id     
Name
Cost        
1
Pen
20.00
1
Pencil
30.00
2
Pencil
30.00
3
Pen
30.00
3
Pencil
40.00
4
Pen
30.00
5
Paper
40.00
5
Pencil
30.00
6
Pencil
40.00

The Output should be,

Product
Number_of_Product
Total_Cost        
Pencil
2
70.00
Pen
1
30.00
Pencil/Paper
1
70.00
Pencil/Pen
2
120.00



Solution:

select distinct(Product),COUNT( distinct(Id)) as   
[Number_of_Product],sum(cost) as Total_Cost from   
(                                                  
select                                             
STUFF((Select '/'+ Name                            
from Items t1                                      
where t1.Id=t2.Id                                  
FOR XML PATH('')),1,1,'')                          
as Product,Cost,Id from Items  t2)gsf              
group by Product                                   

INSERT INTO SELECT - SQL

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.

Syntax:

INSERT INTO Table_A         --here it will insert records into Table_A from Table_B, if
SELECT * FROM TABLE_B;      data types are same.

If you want to insert from a specified column of a table to specified column of another table then we can write query as:

INSERT INTO Table_A (column_names) 
SELECT (column_names) FROM TABLE_B;

Example: Consider Table employee_A having same structure as Table employee. 

INSERT INTO employee_A
SELECT * FROM  employee
;

Output:





























Now Consider inserting from a specified column of a table to specified column of another table.

INSERT INTO employee_A (E_ID,E_Name)
SELECT ID,Name FROM employee;

Output:

























Here you could see that only two columns inserted from employee table.


Now consider using where condition along with select statement.

INSERT INTO employee_A (E_ID,E_Name)
SELECT ID,Name FROM employee 
Where Salary is null and Gender is null;

Output:





















You could see that only two such records exists in employee table which is inserted into employee_A table.


Wednesday, December 11

INSERT INTO - SQL

The INSERT INTO statement is used to insert new records in a table. It is a DML statement.

Syntax:


Here only values are specified for all corresponding columns in order.


INSERT INTO table_name                                         
VALUES (value1,value2,value3,...);                                                                         

or,

Here records are inserted in specified columns only.

INSERT INTO table_name (column1,column2,column3,...              
VALUES (value1,value2,value3,...);                                                                                                           
Example: We will insert values into Employee table, that we have created in Mytest_db                           database during previous sessions.

INSERT INTO employee
VALUES (1231,'Smith',15000,'M');
INSERT INTO employee
VALUES (1232,'Jones',15000,'M');

Note: From SQL Server 2008 onwards, the code can be simplified to the following.

INSERT INTO employee                                      VALUES (1233,'Alice',15000,'F'),(1234,'Biona',15000,'F');


Output:

Select * from dbo.employee;






















Now lets see to Insert data in specified columns;

INSERT INTO employee(ID,Name)
VALUES (1235,'Rijo'),(1236,'Ashni');
Output:
Select * from dbo.employee;
























The columns without values will display Null.