Insert, Update and Delete Using Merge Statement in SQL With Update to Third Table.

This Example will Guide you to update a bulk data of a table.
In Below example Master ID (@JournalVoucherID) is used to recognize the data set and also to Compare the Source and Target Data.

Working Loogic:

1. Common Dataset gets UPDATED as in case of WHen Matched.
2. New DataRow from Source is INSERTED in Target as in case of WHEN NOT MATCHED By             Target.
3. Extra data in Target with same master ID is DELETEDE as in case of WHEN NOT MATCHED By Source and Target.JournalVoucherID=@JournalVoucherid.
4. And at the end DELETED Data needs to update some more Table hence we use
    output deleted.ChequeNo into @Cheque.Where @Cheque is another table variable to maintain a      third table.

GO
alter proc [dbo].[prInsert_JournalDetailsBulk]
@JournalDetails JournalDetails ReadOnly
,@JournalVoucherid int
AS
BEGIN
Declare @Cheque Table(id int )

MERGE INTO 

             tbl_journaldetails  as Target 

Using   @JournalDetails as Source
on (
              Target.JournalVoucherID =@JournalVoucherid
     )


WHen Matched  and Target.[JournadetailID]=Source.[JournadetailID]
THEN 
UPDATE SET 
Target.[LedgerMasterID]=Source.[LedgerMasterID],
Target.[ProjectMasterID]=Source.[ProjectMasterID],
Target.[SNo]=Source.[SNo],
Target.[SubLedgerID]=Source.[SubLedgerID],
Target.[Narration]=Source.[Narration],
Target.[DrAmount]=Source.[DrAmount],
Target.[CrAmount]=Source.[CrAmount],
Target.[TDSPan]=Source.[TDSPan],
Target.[TDSName]=Source.[TDSName],
Target.[TDSAmount]=Source.[TDSAmount],
Target.[TDSTDS]=Source.[TDSTDS],
Target.[TDSDate]=Source.[TDSDate],
Target.[TDSType]=Source.[TDSType],
Target.[Remarks]=Source.[Remarks],
Target.[SourceID]=Source.[SourceID],
Target.[ProgramID]=Source.[ProgramID],
Target.[BudgetHeadMasterID]=Source.[BudgetHeadMasterID],
Target.[BudgetSubHeadMasterID]=Source.[BudgetSubHeadMasterID],
Target.[ChequeNo]=Source.[ChequeNo],
Target.[ChequeDate]=Source.[ChequeDate],
Target.[ChequeAmount]=Source.[ChequeAmount]
WHEN NOT MATCHED By Target 
          THEN 

 INSERT ([LedgerMasterID]
           ,[ProjectMasterID]
           ,[JournalVoucherID]
           ,[SNo]
           ,[SubLedgerID]
           ,[Narration]
           ,[DrAmount]
           ,[CrAmount]
           ,[TDSPan]
           ,[TDSName]
           ,[TDSAmount]
           ,[TDSTDS]
           ,[TDSDate]
           ,[TDSType]
           ,[Remarks]
  ,[SourceID]
  ,[ProgramID]
  ,[BudgetHeadMasterID] 
  ,[BudgetSubHeadMasterID] 
  ,[ChequeNo] 
  ,[ChequeDate] 
  ,[ChequeAmount]  
  )
     VALUES
          ( 
   Source.[LedgerMasterID]
           ,Source.[ProjectMasterID]
           ,@JournalVoucherid
           ,Source.[SNo]
           ,Source.[SubLedgerID]
           ,Source.[Narration]
           ,Source.[DrAmount]
           ,Source.[CrAmount]
           ,Source.[TDSPan]
           ,Source.[TDSName]
           ,Source.[TDSAmount]
           ,Source.[TDSTDS]
           ,Source.[TDSDate]
           ,Source.[TDSType]
           ,Source.[Remarks]
  ,Source.[SourceID]
  ,Source.[ProgramID]
  ,Source.[BudgetHeadMasterID] 
  ,Source.[BudgetSubHeadMasterID] 
  ,Source.[ChequeNo] 
  ,Source.[ChequeDate] 
  ,Source.[ChequeAmount]  
  )



WHEN NOT MATCHED By Source and Target.JournalVoucherID=@JournalVoucherid

then delete

output deleted.ChequeNo into @Cheque

;



 update [tbl_ChequeBookEntry]set [Status]='false' where [ID] in (select ID from  @Cheque)

end

career as asp .net developer and challenges.

Every career has its challenges and upportunities, same is with asp .net developers. Their is always new task to accomplish and their challanges are uncertain.
Not every ideas gets accomplished as there is always client delivery pressure and deadlines to meet.
Projects are bigger and complex. Knowledge required are many to sustain in the field. Few most important are jquery, css, html, xml, json, sql, mvc, c#, webservices, javascript. Mastering all is other challenging part of the career nut yet we need to go through all of this. So that any of these can be used when needed and is needed most of the time.
Major tasks as developer in asp .net includes mis development for many domains.
Majority of industry are dependent on information and mis is the most for all to compete. These infomation system are now vastly migrating to web technologies and asp is one of the top choises of the development because of its vast library and support.
Real time systems is also possible now due to signalr with the release of .net frame work 4.5 and hence have increasing the scope of the technology.
This trend of enhancement in .net technology in every now and then also have made the market of asp .net development more preferable.
But any break in development career may that be of few months can make you get going from the industry as practice and knowledge required to be continuous in this industry to sustain the competition. Be that be in full time job or in freelancing. Although freelancing has not as much upportunities as it provides for php developers.
Problem solving skill and on site solution finding is another challenge for developera as there may be bug even after delivery and also after all the testings. On site solution provided may be temporary for the system to run instantly but you need to find a permanent solution for such problems or bugs and deliver them to customer as their working and business depends on this.

Loop in T-sql using Temporary Table and while loop equivalent to Foreach loop.

This post will explain you the process of performing Foreach equivalent loop using Temporary Table and while loop iteration through the temporary table.
Usage:
1. To achieve loop without use of cursor.
2. To achieve Foreach like functionality i.e. can get current row in the loop.
3. Insert data in some table in bulk for no of fixed range like in case of fixed customer billing, Student bill assign.
First You need to create a temporary table with auto increment integer as primary key and second column for the data you are going to check loop on.
 ex.

CREATE TABLE #temp(
uniqueid smallint Primary Key IDENTITY(1,1)
 ,col1       INT
);

Now we can insert data in the temporary table trough which we are going to traverse.

insert into #temp (col1  )
select customerid from skhpcustomer;

After having data in temporary table now we are all set to go for a loop for which we can set an integer flag to get current row.

declare @i int=1;
while(@i<=(select max(uniqueid) from #temp))
begin
--Here Other task on data can be performed by getting col1 for uniqueid in #temp
print @i;

set @i=@i+1;
end

Drop table #temp -- Donot forget to clear un wanted temp data.

This completes the loop which is equivalent to foreach loop required for different condition in SQL Development.

Getting First and Last Date of the Current Week in sql.

This Post will Explain how we can get the First Date and Last Date of the Current Week or for a given random date if Supplied in sql server.
Here in the Example Below getdate() function is used to get the  todays date. You can make it input field in stored procedure to generalize this procedure for any date and not just current date.

DateADD(interval,increment int,date) Function adds the increment int no of days to the date times the interval, here interval is d which days. Hence 1-datepart(dw,getdate()) no of days are added to the current date.

DatePart(interval,date) gets the date part in interval provided. In our example interval is week day dw hence, datepart gets the what date (1-7) in week is for the given date.


in our example we add the number of day of the week for the date subtracted from 1 for first day which will result in subtracting 2 (1-3) days if today is 3rd day of the week (Tuesday) resulting in sunday's date.
for last day of the week 7-3 will be 4 a positive no which will be added to todays date resulting in saturdays date.

Example:

DECLARE @Startdt Date
DECLARE @Enddt Date
DECLARE @temptbl Table(StartDate Date, EndDate Date)
SET @Startdt= dateadd(d,1-datepart(dw,getdate()),getdate())
SET @Enddt =dateadd(d,7-datepart(dw,getdate()),getdate())

INSERT INTO @temptbl VALUES(@Startdt,@Enddt)
SELECT * FROM @temptbl

Inserting Data in SQL from XML file using Stored Proceduer.

This Post will explain how we can upload data in sql server after reading an XML file.
uses:
1. Importing of data to sql server for application like Excel can be done with help of XML conversion of Excel Data and then Inserting it using this process to upload in the sql database.

2.Other XML Data Generated by application can be inserted in to the data base at once hence reducing network traffic for slow networks.

example:
-- below is the syntax to Create a Stored Procedure for Insertion of data in to the table with XML data
Create Procedure prUpload_XML
@P_XML XML=null
AS
BEGIN
insert into table_Name
(
Column1,
Column2,
Column3
)
SELECT
T.Item.value('(@Column1)[1]','nvarchar(50)'),
T.Item.value('(@Column2)[1]','nvarchar(10)'),
T.Item.value('(@Column3)[1]','nvarchar(50)')
 FROM    @P_XML.nodes('/items/item') AS T ( Item )

--Here items is the name of XML file uploaded and item is the tag for each row in the XML.

Conditional Insert Update of Row Using "IF Exists" in sql.

Same Stored Procedure Can be used to Insert and Update the Row by checking if the data already exists in the table. "If Exists" Key word checks the row if already exists in the Table.
 syntax:
if exists(select *  from tablename where column1=@column1 and column2=@column2 )
begin
--UPdate statement should be written here.
end
else
begin
--Insert statement should be written here.
end

here, "where" condition in the exists method should should uniquely identify the row data. Also in case of Update Statement the where condition should uniquely identify the row as it will update the data of the row satisfying the condition.

Single Stored procedure for insert and update is very important in case of same GUI for insertion  and updation of data. This Type of Interface are very User Friendly and efficient when managed perfectly.


 ex:
CREATE PROCEDURE [dbo].[prInsert_BudgetMaster]
            @BudgetSubHeadID as int =null
           ,@Amount as float=null
           ,@DateFrom as datetime =null          
           ,@DateTo as datetime =null
           ,@CreatedByID as int =null
           ,@CreatedDate as datetime =null
           ,@ModifiedByID as int =null
           ,@ModifiedDate as datetime =null
           ,@ChequeAmount as float=null
           ,@CashierByID as int =null
           ,@SubmittedByID as int =null
           ,@SubmittedPositionID as int =null
           ,@SubmittedDate as datetime =null
           ,@ApprovedByID as int =null
           ,@ApprovedPositionID as int =null
           ,@ApprovedDate as datetime =null
           ,@Remarks as nvarchar(1024) =null
           ,@Status  as nvarchar(50) =null
            ,@BudgetHeadMasterID as int    =null
            ,@ProjectMasterID as int=null
            ,@SourceMasterID as int=null
            ,@ProgramMasterID as int=null
            ,@ReleasedDate date=null
            ,@gov_BudgetSubHeadID as int=null
            ,@fiscalYearID as int =null
            ,@LedgerMasterID as int =null
            ,@SubledgerID as int =null
            ,@AllocationType as nvarchar(250)=null,
            @gov_FundTypeForForeignGrants as int=null
            ,@officeFacultyid int=null

AS
BEGIN
Declare @fiscalYearIDdb int=(select FiscalyearID from tblfiscalyearmaster where active=1 and running=1)
Declare @DateFromdb date=(select StartDate from tblfiscalyearmaster where active=1 and running=1)
Declare @DateTodb date=(select EndDate from tblfiscalyearmaster where active=1 and running=1)

if exists ( select tbl_BudgetHeadMaster .BudgetHeadMasterID  as ID,Code,BudgetHeadMasterName ,Amount,DateFrom,DateTo,FiscalYearID,AllocationType,gov_budgetSubheadId from tbl_BudgetHeadMaster 
left outer join tbl_budgetmaster on tbl_BudgetHeadMaster .BudgetHeadMasterID=tbl_budgetmaster.BudgetHeadMasterID
where (isnull(@gov_budgetsubheadid,'')='' or gov_budgetSubheadId=@gov_budgetsubheadid)
and fiscalyearid=@fiscalYearIDdb and AllocationType=@AllocationType and
(isnull(@SourceMasterID,'')='' or SourceMasterID=@SourceMasterID)
and gov_FundTypeForForeignGrants=@gov_FundTypeForForeignGrants
and tbl_BudgetHeadMaster.BudgetHeadMasterID =@BudgetHeadMasterID
and ( ISNULL(@officeFacultyid,'')='' or tbl_BudgetMaster .officefacultyid=@officeFacultyid )
)
begin
update tbl_budgetmaster
set Amount=@Amount,DateFrom=@DateFrom,DateTo=@DateTo,
ModifiedDate=current_timestamp,ModifiedByID=@ModifiedByID,
 ReleasedDate=@ReleasedDate

where (isnull(@gov_budgetsubheadid,'')='' or gov_budgetSubheadId=@gov_budgetsubheadid)
and fiscalyearid=@fiscalYearIDdb and AllocationType=@AllocationType and
(isnull(@SourceMasterID,'')='' or SourceMasterID=@SourceMasterID)
and gov_FundTypeForForeignGrants=@gov_FundTypeForForeignGrants
and tbl_budgetmaster.BudgetHeadMasterID =@BudgetHeadMasterID
and ( ISNULL(@officeFacultyid,'')='' or tbl_BudgetMaster .officefacultyid=@officeFacultyid )
end
else
begin

INSERT INTO tbl_BudgetMaster
                        (BudgetSubHeadID, Amount, DateFrom, DateTo, CreatedByID, CreatedDate,  ChequeAmount, CashierByID,
                        SubmittedByID, SubmittedPositionID, SubmittedDate, ApprovedByID, ApprovedPositionID, ApprovedDate, Remarks, Status, BudgetHeadMasterID,
                        ProgramMasterID, SourceMasterID, ProjectMasterID, ReleasedDate,gov_BudgetSubHeadID,FiscalYearID ,AllocationType,gov_FundTypeForForeignGrants,officefacultyid )
VALUES      (@BudgetSubHeadID,@Amount,@DateFrom,@DateTo,@CreatedByID,current_timestamp,@ChequeAmount,@CashierByID,
                    @SubmittedByID,@SubmittedPositionID,@SubmittedDate,@ApprovedByID,@ApprovedPositionID,@ApprovedDate,@Remarks,@Status,@BudgetHeadMasterID,
                    @ProgramMasterID,@SourceMasterID,@ProjectMasterID,@ReleasedDate,@gov_BudgetSubHeadID,@fiscalYearIDdb,@AllocationType,@gov_FundTypeForForeignGrants,@officeFacultyid )
end
END

Troubleshooting Database Issues:

The DPM database stores all DPM settings, configuration, and reporting information for DPM.

A database failure can cause DPM jobs to fail, interfere with the use of DPM Administrator Console, prevent DPM reporting, and stop the DPM service. A backlog of requests caused by low memory or high CPU usage also can cause client timeouts (when DPM processes take a long time to establish a connection to SQL Server or run a SQL query).

Possible causes of DPM database failures include the following:

• An unusually high number of protection jobs being processed on the DPM server at one time, and SQL Server is unable to handle the volume of requests in a timely manner. This is the most common cause of client timeouts and general network errors.
• A database or transaction log that DPM uses has run out of disk space.
• The DPM database enters an inconsistent state or becomes corrupted.
• The SQL Server service is not running or is not configured correctly.

Some database failures in DPM are caused by transitory problems that may resolve themselves. Examples include insufficient resources, client timeouts, insufficient disk space for transaction logs, deadlocks in SQL Server, recoverable database errors, and timing issues.

Before you run diagnostics for this type of database failure, perform the following steps on the DPM server to ensure that the problem is not transitory.

To resolve issues that might cause a transitory database failure

1.       Verify that the SQL Server service is running. In Administrative Tools, open Services, and verify that the status of the SQL Server (MSDPM2010) service isStarted. If it is not, right-click the service, and then click Start or Resume, as appropriate.

2.       If this does not fix the problem, close and reopen DPM Administrator Console.

3.       If this does not fix the problem, stop and restart both the DPM service and the SQL Server (MSDPM2010) service. (In most cases, restarting the DPM service will resolve the issue.) For instructions, see Verifying Status of the DPM Service.

If you experience database failures in DPM that are not transitory in nature, the issues can be complex. You will need to work with your SQL Server administrator to diagnose and resolve the related issues. The following Database Errors table provides a starting point for resolving specific database errors. The Database Exceptions table later in this topic provides information about how to troubleshoot database exceptions.

Database Errors

 

Error	To troubleshoot this error
Error 940: Unable to connect to the database because of a fatal database error. It is unlikely that the database itself has been damaged.	Ensure that the DPM instance of SQL Server (MSDPM2010) service is running. Then use Windows Event Viewer to find information about related events.
Error 941: Unable to connect to the DPM database.	To resolve issues related to database connection failures 1. Ensure that the DPM service is running. 2. Resolve transient issues that might be temporary database connection failures.  3. Make sure that SQL Server is configured correctly. For information about configuring SQL Server for use with DPM, see Manually Installing Prerequisite Software.
Error 942: Database integrity is in question because of a hardware or software problem.	If the integrity of the DPM database is in question, contact your SQL Server administrator. You might need to repair or restore the database in SQL Server from backup media. Inform the administrator every time this error is encountered. Look at Windows Event Log for SQL Server/media failures. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. In some cases, it may be necessary to restore the database. To resolve issues related to a database that may be corrupt 1. Use Event Viewer on the DPM server to check for I/O problems at the time of the alert. I/O failures in the event log might explain why the database was corrupted. If that is the case, you might want to repair or replace the hard disk before restoring the database. 2. To determine the extent of the database problem, perform a database check on the database for which integrity is in question. In SQL Query Analyzer, enter the following command: dbcc checkdbdatabasename 3. Depending on the nature of the problem, you might need to repair or restore the DPM database. Some problems, such as indexing and caching problems, can be resolved by repairing the database or restarting SQL Server. Work with your SQL Server administrator to determine the best course of action.
Error 943: Unable to connect to the DPM database because the database is in an inconsistent state.	If DPM finds that the database is in an inconsistent state, the problem is unlikely to resolve itself.
Error 944: Database operation failed.	·    If you are experiencing client timeouts and general network errors that are possibly related to SQL Server being unable to handle the volume of requests, modify protection schedules for protection groups to stagger protection jobs. ·    A high incidence of client timeouts may be a sign that the volume that stores your database files and transaction logs is highly fragmented. If needed, use Disk Defragmenter to defragment the volume. ·    A shortage of disk space for a SQL Server database file or transaction log generates a warning or error in SQL Server, depending on how critical the shortage has become. If transaction logs overflow, the DPM service probably will shut down after one or more failed attempts to access the database. To resolve issues caused by a shortage of disk space for databases or transaction logs 1. Use Windows Event Viewer to get additional information about the problem. 2. Use SQL Query Analyzer to get information about the size of the databases and the use of transaction log space by entering the following commands: sp_helpdb — Reports the size of each database file. dbcc sqlperf (logspace) — Reports the current sizes of transaction logs. 3. Shrink the database and transaction logs. In SQL Query Analyzer, enter the following commands: dbcc shrinkfile (databasenamelog.dat) — Shrinks the transaction logs for the specified database. dbcc shrinkfile (databasename_dat) — Shrinks the specified database file. If disk space is still insufficient, extend the volume that contains the files to ensure sufficient disk space for the transaction log during peak performance. ·    This error can also occur if the SQL Server administrator has deleted the database, taken the database offline, or detached the database for maintenance. If the database is offline or detached for maintenance, retry the operation after the database is brought online. If the database was deleted, restore the database from tape backup, and then run DpmSync.
Error 945: Unable to connect to the DPM database because of a general database failure.	For general database failures, ensure that the DPM service is running on the DPM server. Then use Windows Event Viewer to find information about related events. On a new DPM server, ensure that SQL Server is configured correctly. To resolve issues related to general database failures 1. Ensure that the SQL Server (MSDPM2010) service is running. 2. Resolve transient issues that might be temporary database connection failures.  3. Make sure that SQL Server is configured correctly. For information about configuring SQL Server for use with DPM, see Configuring DPM. 4. If general database failures persist, contact Microsoft product support for help with troubleshooting the problem.

Database Exceptions

 

Exception	To troubleshoot this exception
Exception 5168: Login failed for user <user name>.	DpmSync fails when trying to restore the DPM database because it cannot open the DPM database requested by the login. Verify that the DPM installation contains a new DPM database and that it is online before trying to restore an older database. If a new database does not exist on the DPM installation, uninstall and then reinstall DPM. After the installation is complete, run DpmSync again.

Additional Resources for Troubleshooting Database Issues

This following is a list of additional resources you might use to help diagnose and resolve DPM database issues.

• Configuring SQL Server for use with DPM, see Installing DPM.
• Using the sp_helpdb system stored procedure for SQL Server in the Transact-SQL Reference.
• Using the Database Console Commands (DBCC), DBCC SQLPERF and DBCC SHRINKFILE for SQL Server located in the “DBCC” section of the Transact-SQL Reference.
• Using the Windows Server 2008 event logs to identify problems.
• Configuring and maintaining physical storage components in SQL Server 2008 to meet capacity, throughput, and performance requirements. For more information, see Managing Performance.

Using Merge in SQL Server 2008 R2

  --To Merge Data of different tables we can use merge statement in sql which first checks the content of both the tables and adds the data in the Target table which are missing from Soucre Table.

Syntax:

MERGE INTO [TARGETTABLE] AS Target
    USING [SOURCETABLE] AS SOURCE
    ON
        --unique key filtering condition
        Target.COLUMN1=SOURCE.COLUMN1 AND
        Target.COLUMN2=SOURCE.COLUMN2 AND
        Target.COLUMN3 =SOURCE.COLUMN3 AND
        Target.COLUMN4 =SOURCE.COLUMN4
     WHEN MATCHED THEN
        --update or delete query
        UPDATE SET
            Target.ColumnA=SOURCE.ColumnA,
            Target.ColumnB=SOURCE.ColumnB,
            Target.ColumnC=SOURCE.ColumnC

    WHEN NOT MATCHED THEN
        --INSERT QUERY
        INSERT (COLUMNA, ColumnB, ColumnC, ColumnD)
        VALUES(
                        SOURCE.ColumnA,
                        SOURCE.ColumnB,
                        SOURCE.ColumnC,
                        SOURCE.ColumnD                    )
    ;