How to Generate SQL Query in excel sheet two column

Today I have faced one problem in excel sheet. I have a situation need to update user email id details from excel to my client database. The users email list is more than 1000 users.

For this update to excel we can do line by line update to the database or import into database and write the join query for update. But the simple way is we can generate INSERT/UPDATE SQL query and execute in to the database. For my situation I have already user data in the database. So I want to update the emailID based on the employeeID contition.

For that the below Excel comment will help to generate the SQL UPDATE query with simple steps and I can update with in 5mintus.

=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Excel Sheet:

Emp ID	Name	EMail id	Project
1110	Karthik	Karthik@gmail.com	Sprots Domain
1111	Kumar	Kumar@gmail.com	Sprots Domain
1112	Vijay	Vijay@gmail.com	Sprots Domain
1113	Yuvan	Yuvan@gmail.com	Education Domain

Excel Sheet Query Generator:
=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Output SQL Query :

SQL Query

UPDATE tb_employee set EMPL_EMAILID= ('Karthik@gmail.com') WHERE EMPL_ID= (1110);

UPDATE tb_employee set EMPL_EMAILID= ('Kumar@gmail.com') WHERE EMPL_ID= (1111);

UPDATE tb_employee set EMPL_EMAILID= ('Vijay@gmail.com') WHERE EMPL_ID= (1112);

UPDATE tb_employee set EMPL_EMAILID= ('Yuvan@gmail.com') WHERE EMPL_ID= (1113);

Finlay copy all the sql query from excel sheet and execute into the database. Its take 5 mints to complete your task.

Try this and let me know your feedback.

Display number of days/weeks in the year using SQL Query

Each time if i develop any report i need to write SQL Query for list out number of days or week in the year. So here i am giving you the simplified way to get the number of days name,date and week number of each date. One of my requirement is to display each week the sales report for the product. So the below query will help you to list out number of weeks in the year with week name.


SQL Query:

DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)

;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS DateList ,
DATENAME(dw, @FirstDateOfYear) AS DayName

UNION ALL

SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.DateList),
DATENAME(dw, DATEADD(d, 1 ,cte.DateList)) AS DayName
FROM cte
WHERE DATEADD(d,1,cte.DateList) < @LastDateOfYear

)
SELECT *  ,'Week '+Convert(nvarchar,DATEPART(wk,DateList)) as WeekName
FROM CTE

OPTION (MaxRecursion 370)


The Result :

DayID	DateList	DayName	WeekName
1	1-Jan-16	Friday	Week 1
2	2-Jan-16	Saturday	Week 1
3	3-Jan-16	Sunday	Week 2
4	4-Jan-16	Monday	Week 2
5	5-Jan-16	Tuesday	Week 2
6	6-Jan-16	Wednesday	Week 2
7	7-Jan-16	Thursday	Week 2
""	""	""	""
""	""	""	""
366	31-Dec-16	Saturday	Week 53

The above SQL query we will get the day count start from 1 to end with 365 or 366 and display the date and the Date name. Finlay added week number in the list so when you want only week of the day or number of weeks in the year you can use only the WeekName column.You can use DISTINCT option to display only week number on the display result. We can make this query to our convenience way to get proper result set.

8+ Interview questions in C#.Net and ASP.NET

Here with i am planning to share the interview questions asked for different company. I hope this will help you for job seekers.


Interview questions
27-02-2016 iBox
1. What is visual studio online
2. What is use of code analyzer
3.NUnit test how to write and every time it will go for database for value check or some other method to use for that.
4.Static class use and where can use
5.Difference between abstract and interface
6.Difference between web service and WCF services
7.What is Cross page/code scripting
8.What is MVC and advantages
9.VS 2015 features
10.what is JSON and what is the use.
11. Difference between JQuery and java script.
12. Method overload and override.
13. Difference between Dataset and DataReader.
14. What is Cross page postback
15. what is Nugat and have you used?
16. What is the use of multi inheritance in c#


31-03-2016 :RRD
Timing : 1 Hr

1.    Tell me about yourself.
2.    Tell me your strength and weakness in Technically
3.    Explain .Net Framework and tell me about CTS,CLS, JIS and MSIL and the flow
4.    Explain your project architecture and data flow.
5.    What is MangoDB and advantage of that. Can I store N number of records?
6.    What is SQLLite and who developed that or who released?
7.    What is the difference between SVN and Perforce source controls.
8.    What is FFMPEG and how fast its convert the video files.
9.    What is the new feature in .NetFramework 3.5 (VS2008)
10.     Have you done any code review?

SQL:
1.    How to do performance tuning  in SQL
2.    Tell me Type of join and difference between INNER JOIN and LEFT OUTER JOIN
3.    What is sql constrain and explain
4.    What are the index are available in SQL and explain
5.    Difference between Inner join and Union
6.    Difference between Temp table and Global temp table and life time of temp table
7.    I have two table in my View and DBA deleted one of the table column in the table, Then what will happen while running select query and how you will prevent to delete column from in table
8.    What is Profiler
9.    Different type of index in SQL and explain.
10.    Difference between temp table and act table. Which one is good?
11.    What is primary key and foreign key
12.    Difference between stored procedure and function
13.    Hoe to capture error in SQL and how to return error log ID
14.    What is DEAD lock and how to prevent that.

C#
1.    What is oops and explain
2.    What is class
3.    What is Polymorphism and explain the Run time and compile time polymorphism , Where can we use this
4.    What is encapsulation
5.    What is the default access modifier of class and what is the default access modifier of Interface.
6.    Difference between Abstract and interface. Where can I use this both?
7.    What is sealed class?
8.    How to achieve multiple inheritance in C#.
9.    What are the design patterns are available. Explain singleton patterns, Iterate pattern (if you using foreach in your patters means you are using Iterate pattern)
10.     Explain SOLID
11.    What is Static class in C#?
12.    What is struct class?
13.    What is Assembly and Type of Assembly? What is private and public assembly?
14.    What is static assembly?
15.    What is DLL and tell me the Difference between EXE and DLL?
16.    How to register the assembly?   

ASP.NET
1.    Tell me Page life cycle.
2.    When I give www.google.com what will happen and explain the flow.
3.    Tell me Client side state management and server side state management.
4.    What is Application Object and what are the types are available. Where the application object available.
5.    Type of session management in Server.
6.    What is authentication and authorization, what are the authorizations are available and how to achieve on that.
7.     What is passport authorization and how to implement?
8.    What is Tracing and how to enable that?
9.    How to Host aps.net application in IIS.
10.    What is application pool, worker process?
11.    What is httpHandler and httpmodule.
12.    Explain the response and request in IIS and how IIS handling this.
13.    What is web service and how to use it?
14.    How to secure the webservice.
15.    What is WSDL?
16.    What is SOAP?
17.    What is Serialization and De-Serialization.
18.    What is JOSON Serialization?
19.    Have you worked any payment gate way, Have you used any Encryption method in your project.
20.    What is query string and how to secure that?
21.    How to get text box value in Javascript.
22.    What are the client side validations are available.
23.    What is JSON?
24.    What is latest IIS version?
25.    Have you worked any cloud server and hosted any website in cloud.

ADO.NET
1.    Difference between Data Adapter and Data Reader
2.    Which one is connected architecture and disconnected architecture
3.    What is Dataset
4.    What is Entity

How to get SQL Server IP Address in SQL Query?

Get IP Address of SQL Server using SQL Query:

I have a situation to get the SQL Server IP Address. My SQL Server was in different IP Address and my web application was in different address. Some time we need to get both SQL Client and SQL Server Local IP Address. The below Example will help me to get the both client and sql server IP’s.
@@SPID – This is the session ID of particular connection. Each connection have different session ID.

SELECT   client_net_address,local_net_address, @@SPID as SPID

FROM sys.dm_exec_connections

WHERE Session_id = @@SPID;

I hope the above SQL Query will help you to get the SQL Server IP Address.

If you want to learn more ablut @@SPID Please check the below link

http://sqlserverplanet.com/dba/spid-what-is-it

How to Select/Display/List tables from MS SQL Server 2008 R2 ?

Some time we need to select list of user table in the Database to identify the table list. For that we have different SQL querys to get display the Table list. Here I am give some small code to display the user table in different SQL query language.

List out the Data Base Tables detail:

SELECT * FROM sys.Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

SELECT sc.name +'.'+ ta.name TableName ,SUM(pa.rows) RowCnt

FROM sys.tables ta

INNER JOIN sys.partitions pa  ON pa.OBJECT_ID = ta.OBJECT_ID

INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id

WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)

GROUP BY sc.name,ta.name ORDER BY SUM(pa.rows) DESC

How to Select / Display / List Views in the MS SQL Server 2008 R2:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'view'

How to Select / Display / List Table Columns in the MS SQL Server 2008 R2:

SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='MyTable'

How to check if stored procedure exists or Not in SQL server 2008R2 ?

Drop and Create Stored Procedure in SQL before Creating any SP:


In my project I need to give some Stored Procedure to client system to run some bug fixing. The client environment already have the same SP name. So we can give directly ALTER SP query to execute in the SQL server. But that not a good practices. As a good developer need to think future also. What happen if new client dos not have the Stored Procedure in that system? It will give error message. So avoiding the Error Message we need to add little bit code before the SP.

Before creating SP or deleting any Stored Procedure we need to check if that SP already exist or not in the Database. If Exist DROP the procedure and recreate again. If not exist that SP means don’t Drop the SP just create new Stored Procedure. This is the Good way to Creating Script in the SQL server. Below example first I have checked that SP name already Exist in the DB or Not. If exist I will DROP the Procedure and after that I will create New SP. If Not exist that SP name means it will not run the Drop function.

Drop Stored Procedure if exist in SQL Server and Create New one:

--My_SP_Name - Stored Procedure Name

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[My_SP_Name]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[My_SP_Name]

GO

CREATE PROCEDURE [dbo].[My_SP_Name]

@STARTDATE DATETIME,

@ENDDATE DATETIME

AS

GO

I hope the above code will help you to make good SQL script wit out error free script running. This is not only for SPs, You can use same method for Create Table also.