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]