I have to insert or update in to a table in database depending on a record. I am using stored procedure to do this.I am very new SQL server and stored procedures.Followed various articles and managed to create a stored procedure using MERGE. The table looks likes this,

bl em number title email fl name opt

null 101 00659 ABC null 01 John null

null 102 00660 DEF null 02 Rosy null

CREATE PROCEDURE [dbo].[uspInsUpd]
    @bl         char(8),
    @em         char(35),
    @number     char(12),
    @title      varchar(64),
    @email      varchar(50),
    @fl         char(4),
    @name       char(32),
    @opt        char(16),

AS

MERGE [dbo].[employee] AS [Target]
USING (SELECT @bl, @em , @number, @title, @email, @fl, @name, @opt)
   AS [Source] ( [bl], [em], [number], [title], [email], [fl], [name], [opt])  
ON [Target].[em] = [Source].[em]

WHEN MATCHED THEN
     UPDATE SET [bl]      = [Source].[bl],
                [em]      = [Source].[em],
                [number]  = [Source].[number],
                [title]   = [Source].[title],
                [email]   = [Source].[email],
                [fl]      = [Source].[fl],
                [name]    = [Source].[name],
                [opt]     = [Source].[opt],
           
WHEN NOT MATCHED THEN
     INSERT ( [bl], [number], [email], [fl], [name], [opt], [em],[title])
     VALUES ( [Source].[bl], [Source].[number], [Source].[email], [Source].[fl], [Source].[name], [Source].[opt], [Source].[em], [Source].[title]);
GO

Now I am not sure how can I test this stored procedure. Can anybody tell me if my above code is right.

I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and managed to create a stored procedure using MERGE. The table looks likes this,

bl   em  number  title email fl name opt

null 101 00659   ABC   null  01 John null
 
null 102 00660   DEF   null  02 Rosy null

And the procedure:

CREATE PROCEDURE [dbo].[uspInsUpd]
    @bl         char(8),
    @em         char(35),
    @number     char(12),
    @title      varchar(64),
    @email      varchar(50),
    @fl         char(4),
    @name       char(32),
    @opt        char(16),

AS

MERGE [dbo].[employee] AS [Target]
USING (SELECT @bl, @em , @number, @title, @email, @fl, @name, @opt)
   AS [Source] ( [bl], [em], [number], [title], [email], [fl], [name], [opt])  
ON [Target].[em] = [Source].[em]

WHEN MATCHED THEN
     UPDATE SET [bl]      = [Source].[bl],
                [em]      = [Source].[em],
                [number]  = [Source].[number],
                [title]   = [Source].[title],
                [email]   = [Source].[email],
                [fl]      = [Source].[fl],
                [name]    = [Source].[name],
                [opt]     = [Source].[opt],
           
WHEN NOT MATCHED THEN
     INSERT ( [bl], [number], [email], [fl], [name], [opt], [em],[title])
     VALUES ( [Source].[bl], [Source].[number], [Source].[email], [Source].[fl], [Source].[name], [Source].[opt], [Source].[em], [Source].[title]);
GO

Now I am not sure how can I test this stored procedure. Can anybody tell me if my above code is right?

I have to insert or update in to a table in database depending on a record. I am using stored procedure to do this.I am very new SQL server and stored procedures.Followed various articles and managed to create a stored procedure using MERGE. The table looks likes this,

bl emp number title email fl name opt

null 101 00659 ABC null 01 John null

null 102 00660 DEF null 02 Rosy null

CREATE PROCEDURE [dbo].[uspInsUpd]
    @bl         char(8),
    @em         char(35),
    @number     char(12),
    @title      varchar(64),
    @email      varchar(50),
    @fl         char(4),
    @name       char(32),
    @opt        char(16),

AS

MERGE [dbo].[employee] AS [Target]
USING (SELECT @bl, @em , @number, @title, @email, @fl, @name, @opt)
   AS [Source] ( [bl], [em], [number], [title], [email], [fl], [name], [opt])  
ON [Target].[em_id] = [Source].[em_id]

WHEN MATCHED THEN
     UPDATE SET [bl]      = [Source].[bl],
                [em]      = [Source].[em],
                [number]  = [Source].[number],
                [title]   = [Source].[title],
                [email]   = [Source].[email],
                [fl]      = [Source].[fl],
                [name]    = [Source].[name],
                [opt]     = [Source].[opt],
           
WHEN NOT MATCHED THEN
     INSERT ( [bl], [number], [email], [fl], [name], [opt], [em],[title])
     VALUES ( [Source].[bl], [Source].[number], [Source].[email], [Source].[fl], [Source].[name], [Source].[opt], [Source].[em], [Source].[title]);
GO

Now I am not sure how can I test this stored procedure. Can anybody tell me if my above code is right.

I have to insert or update in to a table in database depending on a record. I am using stored procedure to do this.I am very new SQL server and stored procedures.Followed various articles and managed to create a stored procedure using MERGE. The table looks likes this,

bl em number title email fl name opt

null 101 00659 ABC null 01 John null

null 102 00660 DEF null 02 Rosy null

CREATE PROCEDURE [dbo].[uspInsUpd]
    @bl         char(8),
    @em         char(35),
    @number     char(12),
    @title      varchar(64),
    @email      varchar(50),
    @fl         char(4),
    @name       char(32),
    @opt        char(16),

AS

MERGE [dbo].[employee] AS [Target]
USING (SELECT @bl, @em , @number, @title, @email, @fl, @name, @opt)
   AS [Source] ( [bl], [em], [number], [title], [email], [fl], [name], [opt])  
ON [Target].[em] = [Source].[em]

WHEN MATCHED THEN
     UPDATE SET [bl]      = [Source].[bl],
                [em]      = [Source].[em],
                [number]  = [Source].[number],
                [title]   = [Source].[title],
                [email]   = [Source].[email],
                [fl]      = [Source].[fl],
                [name]    = [Source].[name],
                [opt]     = [Source].[opt],
           
WHEN NOT MATCHED THEN
     INSERT ( [bl], [number], [email], [fl], [name], [opt], [em],[title])
     VALUES ( [Source].[bl], [Source].[number], [Source].[email], [Source].[fl], [Source].[name], [Source].[opt], [Source].[em], [Source].[title]);
GO

Now I am not sure how can I test this stored procedure. Can anybody tell me if my above code is right.