Search This Blog

Comparing values of Two Table and Performing Insert and Update using Merge in SQL

Comparing values of Two Table and Performing Insert and Update using Merge in SQL 


Issue-Matching the values of Two tables if matched then Update a value else Insert in source table

Issue Description-Inserting XML data into a temp table comparing the temp table Records with the main table records based on some conditions
If Records matched then update  main table values with temp table
IF records didn't matches then Insert into Main table

Issue Solution-Will show the Result by creating two tables one main table and second test table

--Main table Script
CREATE TABLE [dbo].[[MyEmploys]](
[EmployeeID] [smallint] NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](40) NULL,
[Title] [nvarchar](50) NULL,
[DeptID] [smallint] NULL,
[ManagerID] [int] NULL,
 CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED 
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (1, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (11, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (22, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (112, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (200, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (201, N'Ken', NULL, N'Chief Executive Officer', NULL, NULL)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
GO
INSERT [dbo].[[MyEmploys]] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)

GO


--Test Table Variable which is storing xml data to test i am inserting some values in test table

declare @employeetable table
( id int,
 name nvarchar(100),
 Title nvarchar(100)
 )
-- the first Row which we are inserting is already present there so it will update the Main table employee id with test table id other two record it will insert
 insert into @employeetable values(250,'Syed','Pacific Sales Manager'),(500,'Cat',' Officer'),(150,'Ken','Chief ')

 --Here is the Final script

 MERGE [MyEmploys] AS stm
USING (SELECT * from @employeetable) AS sd
--checking conditon where name and title matches update main table EmployeeiD
ON stm.firstname=sd.name and stm.title=sd.title
WHEN MATCHED THEN  update set stm.employeeid=sd.id 
 --when name and title are not matching inserting the record
WHEN NOT MATCHED THEN
insert (employeeid,firstname,title) 
values(sd.id,sd.name,sd.title);

--verify your result
Select * from [MyEmploys]

EmployeeID FirstName LastName Title DeptID ManagerID
1 Ken NULL Chief Executive Officer NULL NULL
11 Ken NULL Chief Executive Officer NULL NULL
16 David Bradley Marketing Manager 4 273
22 Ken Sánchez Chief Executive Officer 16 NULL
23 Mary Gibson Marketing Specialist 4 16
112 Ken NULL Chief Executive Officer NULL NULL
150 Ken NULL Chief NULL NULL
200 Ken NULL Chief Executive Officer NULL NULL
201 Ken NULL Chief Executive Officer NULL NULL
250 Syed Abbas Pacific Sales Manager 3 273
273 Brian Welcker Vice President of Sales 3 1
274 Stephen Jiang North American Sales Manager 3 273
275 Michael Blythe Sales Representative 3 274
276 Linda Mitchell Sales Representative 3 274
286 Lynn Tsoflias Sales Representative 3 285
500 Cat NULL Officer NULL NULL


Issue PowerPivot Data Refresh with SharePoint 2013

Issue PowerPivot Data Refresh with SharePoint 2013 

Issue-Not able to Refresh the excel report in Browser and in excel as after migration of Report from one server to another the workbook connection was taking old connection string.
2.Secondaly It was Not allowing to  Edit\Change Excel Workbook Data connection string.




Issue Description- One of my excel 2013 power pivot report was migrated from old server to new server after migration i changed the excel power pivot connection string to connect with new server but the workbook connections was still taking the old connection string of old server and there is no option of changing workbook connnection string .
While opening the powerpivot data connection window it was only allowing to view the workbook connection however i was able to edit the powerpivot connection but it was not updating the workbook connection screenshot below screenshot.



Issue Solution: Searched  a lot in but didn't get any solution so below is the solution what i did to fix.

1.Select the complete sheet Ctrl+A 
2.Copy the Sheet Ctrl+C
3.Open new excel in new window 
4.Paste your Excel Sheet data into new Excel sheet(don't paste the data in same excel file(new sheet )
5.It will automatically copy the sql code in back end and will connect string along with excel data in new excel file.
6.Save the File and replace it with your old existing Excel Report.

Preview the Report in Excel as well as in browser the report will Refresh the Data without any Issue.
Also if you will see the workbook connection it will have same connection as power pivot connection file.

Note:In case you are getting below errror while  refreshing the data in Browser
For this Issue open Report in Excel 
Go to Data tab
Connection Properties
Authentication Setting
Change None to-Autheticated User Account.
Save the Excel Report and Refresh again in browser it will refresh the data.

System Stored Procedures in SQl

System Stored Procedures in SQl

Below are the list of  some of the Important system Stored Procedures which we used most while working with SQl

SP_Tables--Shows list of all Tables in a database

sp_spaceused--if you will run this procedure without any parameter it will show the space used by DB

Output
name rows reserved data index_size unused
TESTING 7                   72 KB 8 KB 8 KB 56 KB

SP_SPaceused 'Test'--Shows the Space used by Table

--Rename db first parameter old db name second parameter new db Name
SP_Renamedb

--Rename Table Name first parameter old Name second old table Name
SP_Rename 'Testing','Testing4'


--Rename column Name first parameter old column Name along with table name second old column Name
 sp_rename 'Departments.id','departmentid' ,'column'

 You can use SP_rename procedure to rename Index,constraints datatype and statics also

 --sp wHO AND SP_WHO2 checks what user is doing what on server
 sp_who
 sp_who2

--Shows all index in table
sp_helpindex 'Departments'


--List of locks held by Database
sp_lock;

 --list of all columns of a table
 SP_columns 'Departments'
  
  --list of all constraints of a table
sp_helpconstraint 'Departments'

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure,function, trigger, computed column, CHECK constraint, view, or system stored procedure.
sp_helptext '[dbo].[Proc_BasicAnnualSalary]'

Note :Above are the list of System SP which we used a lot in order to see all system stored procedure check msdn link:
http://msdn.microsoft.com/en-us/library/ms187961.aspx

Either the User, abc does not have access to the xyz Database, or the database does not exist.

Either the User, abc does not have access to the xyz Database, or the database does not exist.


Issue:Either the User, abc does not have access to the xyz Database, or the database does not exist.\

Issue Description:While Refreshing an Excel report which was connected to analysis service Data cube showing the Below error
Either the User, abc does not have access to the xyz Database, or the database does not exist.



Check -Login to Analysis Service with the user who is facing issue while viewing Report in SP Site
IF the user is not able to see any Database after connecting to analysis server that means the User doesn't have permission to access Database  as in below screenshot.

Solution-Login to analysis services with your Master user or system user in most case which is Sa.
Expand Analysis Service Database
Role
--XXXOlapdataRole
--Rightclick
--Properties
--Membership
--Add the above user who was facing issue 
--ok
Screenshot Below.


Preview the Report by login with the User facing Issue refresh the Report -It will Refresh the Report without any Issue.

NOTE: If after entering the user name you are still not getting the User Go to location and Select all Directory as below screenshot.

Pass Stored Procedure Output to another Stored Procedure in SQL

 Pass Stored Procedure Output to another Stored Procedure  in SQL

Issue-Passing Output of one Procedure as a Input to other Procedure and Displaying Records

Issue Description-One of my colleagues faced the Issue.Below is the Issue Details
He created two Procedures one was calculating the Basic Annual Salary of an employee and the Second Procedure was calculating TotalSalary which includes basicSalary, bonus which was 10% of Basic Salary and allowances.

So below is a simple example Demonstrating -How to Pass One Stored Proc value to another Stored Procedur

--First Procedure Calculating Basic Salary Per Month
CREATE PROCEDURE [dbo].[BasicAnnualSalary]
@Basic Float
AS
DECLARE @Salary Float
SELECT @Salary = @Basic/12
-- Additional Code
RETURN @Salary
GO


-Second Procedure  calculating Total Salary which Includes basic Salary Salary per month,10% of Basic Salary and allowance per mont
Create PROCEDURE [dbo].[TotalSalary]
--First Parameter will Store output of First Procedure that is calculating Basic Salary
@BSalary float,
--Second Parameter is to calculate allowance per month
@Allowance Float
AS
begin
--Tsalary to calculate total salary
DECLARE @TSalary FLOAT
--Bonus to add 10% of BasicSalary
declare @bonus float
--BscSalary to store output of Store Proce BasicAnnual Salary
declare @BscSalary float
exec @BscSalary = [BasicAnnualSalary] @BSalary--Storing first Procedure Output to another Procedure variable
set @bonus =(@BscSalary *10)/100
set @TSalary = (@Allowance/12) +@bonus+@BscSalary 
SELECT @TSalary as MonthlySalary,@BscSalary as MonthlyBasicSalary
end

-- Testing by Executing Second Procedure
exec [TotalSalary] 240000,120000

Output

MonthlySalary MonthlyBasicSalary
32000                  20000

This is how you can store one Procedure Output to another Procedure 

Returning Previous or Next values as a column in SQL

Returning Previous or Next values as a column in SQL

Sometimes the requirement is like we want to show next or previous values of the column.
For Eg In a employee table which have Employee details including salary and we want to know what is the employee current salary what was employee's salary before Hike so this we can achieve through Corelated sub queries.

Below I will demonstrate this using Employee table which contain's employee salary column storing employee current salary as well as previous salary in a single column but while fetching records we have to show previous salary as one column and current salary as new column.


--Employee Table Script 
CREATE TABLE [dbo].[Employee1](
[Empid] [int] NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 5000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'sumit', 6000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'amit', 8000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'amit', 9000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'vijay', 10000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, N'vijay', 5000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, N'suresh', 5000)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, N'suresh', 5800)
GO
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (9, N'Mukesh', 1300)
GO


If you will see above table some Employee like Sumit ,Amit ,vijay and Suresh has two salaries the lesser salary is salary before hike and the greater salary is salary after hike So we have to fetch this in sql query.

So below is the solution using Co-Related sub query.

--Query showing currentsalary and salary before hike
select Empid ,Empname, Empsalary as EmpCurrentSalary,(select Max(empsalary) from Employee1 E1 where e1.EmpSalary <e.empsalary
and e1.EmpName =e.EmpName ) as SalaryBeforeIncrement from Employee1 E

Output

Empid Empname EmpCurrentSalary SalaryBeforeIncrement
1              sumit           5000 NULL
2 sumit        6000                    5000
3 amit 8000 NULL
4 amit             9000 8000
5             vijay 10000 5000
6             vijay              5000                                  NULL
7 suresh 5000 NULL
8 suresh 5000 NULL
9 Mukesh        1300 NULL