Error in merge statement in sqlserver 2008

Posted by Klbaiju under Sql Server on 11/30/2016 | Points: 10 | Views : 418 | Status : [Member] | Replies : 4
Hi,

I want to add multiple rows in a table using merge statement

following is my code

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)

)



declare @words varchar(max), @sql nvarchar(max)
set @words = 'jain,30,Civil;baiju,28,Computer'

set @sql = '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];)'

--print @sql
exec(@sql);

select * from #admin


when I run the query Iam getting following error

Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@words".
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ';'.

(0 row(s) affected)

How to solve this

Regards

Baiju




Responses

Posted by: Adamwaisu on: 11/30/2016 [Member] Starter | Points: 25

Up
0
Down
-- 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

Posted by: Manicse on: 11/30/2016 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Rajnilari2015 on: 12/5/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Try with NVARCHAR instead of VARCHAR

--
Thanks & Regards,
RNA Team

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

Posted by: Bandi on: 1/3/2017 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--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

Login to post response