To get last record in the table

Thanks and Regards,
Venkatesan Prabu .J

Checking appropriate row in the secondary table in reference to the primary table

I got a peculiar query to fetch the rows from the table + check for the same value in the secondary table.

1. The condition is only one select query to fetch it.

2. Input parameters will be passed.

This can be achieved by co-related query concept. Am thinking for any other alternative on this. May be, I will place it in my future articles.

create table mysampletable (id int,name1 varchar(100))
insert into mysampletable values(1,'Venkat')
insert into mysampletable values(2,'Venkat1')
insert into mysampletable values(3,'Venkat2')
insert into mysampletable values(4,'Venkat3')

create table mysampletable1 (id int,name1 varchar(100))
insert into mysampletable1 values(1,'Venkat')
insert into mysampletable1 values(2,'Venkat1')
insert into mysampletable1 values(3,'Venkat2')
insert into mysampletable1 values(4,'Venkat3')

select * from mysampletable
select * from mysampletable1

select *,(select count(*) from mysampletable1 where mysampletable1.id=mysampletable.id) as 'Rowexistsin Secondtable'
from mysampletable where mysampletable.id =1

Thanks and Regards,

Venkatesan Prabu .J

Joining three tables in SQL Server

Below is the sample to join three tables in sql server.

Happy Learning!!!

Regards,
Venkatesan Prabu .J

File System task - Renaming the file

Let see some interesting facts in SSIS,

File system tasks is a nice task used to do our operations on the files in operating system level. Using file system task, we can transfer the file from one place to another (By specifying the source and destination), directory level manipulations and assigning the property to a file.

Lets see a small demo on file system task, I am trying to rename the file located in my desktop.

My existing file name is venkat.txt and i am trying to rename it to venkat1.txt. Drag file sytem tasks and double click it. In the properties window, provide necessary source and destination path of the file. Specify the operation to be done on the file.

Once the file connections were specified(source and destination), you will get the file connection in your packages.

right click on the task and execute it. Now, you file got renamed.

Happy Learning!!!

Thanks and Regards,

Venkatesan Prabu .J

Get the current designation of an employee in sql server

Scenario: I want to fetch the latest role of employee and the employee id

drop table emp
drop table emprole
create table Emp(Employeeid int, roleid int,Updtdate datetime)
create table EmpRole(Roleid int, RoleName varchar(10))

insert into emp values(1,33,'2008-10-11')
insert into emp values(1,44,'2008-10-13' )
insert into emp values(2,55,'2008-10-14')
insert into emp values(2,66,'2008-10-16')

insert into emprole values(33, 'Mgr')
insert into emprole values(44, 'Dev')
insert into emprole values(55, 'Tester')
insert into emprole values(66, 'Lead')

select * from Emp
select * from EmpRole
select * from
(
select Employeeid, row_number() over (partition by employeeid
order by UpdtDate desc) as row ,r.rolename
from emp e inner join emprole r on e.roleid=r.roleid
)tblw where row=1

drop table Emp
drop table EmpRole

Thanks and Regards,

Venkatesan Prabu .J

Row_number in SQL Server 2005

An article on Rownumber:



Usually we used to find some peculiar scenarios like below,


An employee is frequently shifting to different places. I need to fetch all the employees recent location. Considering an employee is working in texas, a record will be added. Now, the employee got shifted to newyork. I need to list the employees recent location. How can i achieve it

create table venkat1
(id int, name1 varchar(100),name2 varchar(100))
insert into venkat1 values(1,'Venkat','Venkat1')
insert into venkat1 values(2,'Venkat1','Venkat2')
insert into venkat1 values(3,'Venkat1','Venkat3')
insert into venkat1 values(4,'Venkat','Venkat4')
select * from venkat1

select id,name1 from
(select id, row_number() over (partition by name1 order by id desc) as row, name1
from venkat1 )tbl where row=1




Thanks and Regards,
Venkatesan Prabu .J

Like operator in SQL Server

Pattern matching in SQL Server:

Pattern matching is a very good area and its very well needed for searching our related data in our database. Considering, I need to check the person name starts with A. In this case, we can opt like operator in SQL Server to retrieve those related patterns.

Below is an example,

drop table venkat
create table venkat(id int, name varchar(10))
insert into venkat
select 1,'Venkat'
union all
select 2,'arun'
union all
select 3,'Lakshmi'
union all
select 4,'arun'
union all
select 5,'ve'

Let's see some of the pattern mathching conditions

String Starts with "V" and having any number of characters. % indicates any number of character
select * from venkat where name like 'V%' - No ouput
String Starts with "V" and having only one character. _ indicates only one number.
select * from venkat where name like 'V_' - 5 for id and ve for name
String should not start with "V"
select * from venkat where name not like 'V%'

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Server Error

Error Message:
Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.

Example:
create table venkat(id int, name varchar(10))
insert into venkat
select 1,'Venkat'
union all
select 2,'arun'
union all
select 3,'Lakshmifdsaf'
union all
select 2,'arun'
You are trying to insert the data, which is beyond the allowable size. The data "Lakshmifdsaf" will hold more size when compared to the allowable size.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

CASE Operator in SQL Server

CASE Operator:

Scenario:

We used face a regular scenario to update the values based on the data available in the table. For this kind of situation, we can go for CASE operator.

Sytax:

Case

when expresssion or column1 = value1 Then assign this value to column1

when expresssion or column1 = value2 Then assign this value to column1

end

Example:

Am trying to create a table,
drop table venkat
create table venkat(id int, nam varchar(10))
insert into venkat values (1,'a')
insert into venkat values (2,'aa')
insert into venkat values (3,'aaa')
select * from venkat
select * from
(
select idval =
case id
when 1 then '1a'
when 2 then '2a'
when 3 then '3a'
end
from venkat
)tbl

The output resembles,

Thanks and Regards,

Venkatesan Prabu .J

SQL Puzzle - Small things to remember

Today, I have tried some interesting tiny concepts like,

I am assigning a value to a variable and it should be bound to a specific column.
Whether I need.
1. Assign the value and bind it to a column or Whether this one will work
2. Bind to a column and afterwards assign it to a column. Whether this one will work

Lets make a try,
Now, am declaring a variable and binding it to a column and afterwards am trying to set the variable. OOPs, the value is not reflecting.

declare @a varchar(10)
select @a "ColumnName"
set @a='venkat'
print @a

Let's try for an alternative, assign the value and bound it to the column.

declare @a varchar(10)
set @a='venkat'
select @a "ColumnName"
print @a

Yes, its working. As a basic foundation thoughts, the variables should be assigned with a value and afterwards we need to bound it to a columnname.

Happy Learning!!!

Regards,

Venkatesan Prabu .J