Error in merge statement in sqlserver 2008

-- begin of insert using merge

insert into dbo.tblDimSCDType2Example

( --Table and columns in which to insert the data

  SourceID1,

  SourceID2,

  Attribute1,

  Attribute2,

  Check_Sum,

  EffectiveDate,

  EndDate

)

-- Select the rows/columns to insert that are output from this merge statement
-- In this example, the rows to be inserted are the rows that have changed (UPDATE).

select    

SourceID1,

SourceID2,

Attribute1,

Attribute2,

Check_Sum,

EffectiveDate,

EndDate

from

(

-- This is the beginning of the merge statement.
-- The target must be defined, in this example it is our slowly changing
-- dimension table
MERGE into dbo.tblDimSCDType2Example AS target
-- The source must be defined with the USING clause

 USING 

  (

    -- The source is made up of the attribute columns from the staging table.

    SELECT 

    SourceID1,

    SourceID2,

    Attribute1,

    Attribute2,

    Check_Sum

    from dbo.tblStaging

  ) AS source 

  ( 

    SourceID1,

    SourceID2,

    Attribute1,

    Attribute2,

    Check_Sum

  ) ON --We are matching on SourceID1 and SourceID2 in the target table and the source table.

  (

    target.SourceID1 = source.SourceID1

    and target.SourceID2 = source.SourceID2

  )

-- If the ID's match but the CheckSums are different, then the record has changed;
-- therefore, update the existing record in the target, end dating the record
-- and set the CurrentRecord flag to N
WHEN MATCHED and target.Check_Sum <> source.Check_Sum
and target.CurrentRecord='Y'
THEN

 UPDATE SET 

    EndDate=getdate()-1, 

    CurrentRecord='N', 

    LastUpdated=getdate(), 

    UpdatedBy=suser_sname()

-- If the ID's do not match, then the record is new;
-- therefore, insert the new record into the target using the values from the source.
WHEN NOT MATCHED THEN

 INSERT 

  (

    SourceID1, 

    SourceID2,

    Attribute1,

    Attribute2,

    Check_Sum

  )

  VALUES 

  (

    source.SourceID1, 

    source.SourceID2,

    source.Attribute1,

    source.Attribute2,

    source.Check_Sum

  )

OUTPUT

$action, 

    source.SourceID1, 

    source.SourceID2,

    source.Attribute1,

    source.Attribute2,

    source.Check_Sum,

    getdate(),

    '12/31/9999'

)

-- the end of the merge statement
--The changes output below are the records that have changed and will need
--to be inserted into the slowly changing dimension.
as changes

(

  action, 

  SourceID1, 

  SourceID2,

  Attribute1,

  Attribute2,

  Check_Sum,

  EffectiveDate,

  EndDate

)

where action='UPDATE';

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Try like this

set @sql = '

declare @words varchar(max), @sql nvarchar(max)

set @words = 'jain,30,Civil;baiju,28,Computer' 

       

         merge #admin AS target

	using (@words) AS source(uname, age,dept)

	on target.uname = source.uname

	when not matched by source then insert into #admin values ( source.[uname], source.[age], source.[dept];)'



      exec(@sql);



select * from #admin

Mani.R

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Try with NVARCHAR instead of VARCHAR

--
Thanks & Regards,
RNA Team

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

--define user-defined type
CREATE TYPE udtStudent AS TABLE(
	uname varchar(10),
    age int,
    dept nvarchar(50)
)
GO

-- MERGE
If object_ID(N'tempdb.dbo.#admin') is not NULL
    Drop table #admin;
create table #admin
(
        uname varchar(10) primary key,
        age int,
        dept nvarchar(50)
        
)

--insert @words data into user-defined table variable
--@words = 'jain,30,Civil;baiju,28,Computer'
declare @ptblStudent udtStudent
insert into @ptblStudent values('jain',30,'Civil')
insert into @ptblStudent values('baiju',28,N'Computer')

merge #admin AS target
using (SELECT * FROM @ptblStudent) src
on target.uname = src.uname
when not matched by Target then insert 
values (src.[uname],  src.[age], src.[dept]);

select * from #admin

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator