MERGE Statement - Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MERGE Statement - Error

Author

Topic

stanchernov
Starting Member

3 Posts

Posted - 2011-05-04 : 14:43:53

Can anyone see a problem with this statement? I can DROP/CREATE target table and INSERT INTO it from my view without any problems... but when i try to update it with MERGE, i keep getting this error:
"Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated."

Here is my code:
---

MERGE TARGET_TABLE AS t
USING SOURCE_VIEW AS s
ON (
t.VARCHARFIELD1 = s.VARCHARFIELD1
AND t.DATEFIELD1 = s.DATEFIELD1
AND t.VARCHARFIELD2 = s.VARCHARFIELD2
)
WHEN not matched THEN INSERT VALUES(
VARCHARFIELD1,
VARCHARFIELD2,
VARCHARFIELD3,
VARCHARFIELD4,
VARCHARFIELD5,
DATEFIELD1
);

Also, can i just use a (*) to insert all of the fields into target table?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-04 : 19:55:06

From the error message, it sounds like one or more of the destination columns is smaller than the source table AND the source table has bigger data. Can you show the table definitions?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

stanchernov
Starting Member

3 Posts

Posted - 2011-05-05 : 08:04:34

quote:
Originally posted by Bustaz Kool

From the error message, it sounds like one or more of the destination columns is smaller than the source table AND the source table has bigger data. Can you show the table definitions?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Like i said, i can INSERT INTO the destination table from same view without any errors. That leads me to believe that my MERGE syntax perhaps has an error in it.

-- Final Table
CREATE TABLE TARGET_TABLE
(
INTFIELD1 int NOT NULL,
VARCHARFIELD1 varchar (25) NULL,
VARCHARFIELD2 varchar (25) NULL,
VARCHARFIELD3 varchar (25) NULL,
VARCHARFIELD4 varchar (4) NULL,
VARCHARFIELD5 varchar (4) NULL,
VARCHARFIELD6 varchar (25) NULL,
VARCHARFIELD7 varchar (50) NULL,
VARCHARFIELD8 varchar (50) NULL,
VARCHARFIELD9 varchar (250) NULL,
VARCHARFIELD10 varchar (100) NULL,
VARCHARFIELD11 varchar (100) NULL,
VARCHARFIELD12 varchar (10) NULL,
DATETIMEFIELD1 datetime NULL
)
GO

NOW as i've mentioned before, i can run this script to INSERT the data in the TABLE

-- This works
INSERT INTO TARGET_TABLE AS a
(
INTFIELD1,
VARCHARFIELD1,
VARCHARFIELD2,
VARCHARFIELD3,
VARCHARFIELD4,
VARCHARFIELD5,
VARCHARFIELD6,
VARCHARFIELD7,
VARCHARFIELD8,
VARCHARFIELD9,
VARCHARFIELD10,
VARCHARFIELD11,
VARCHARFIELD12,
DATEFIELD1
)
SELECT
INTFIELD1,
VARCHARFIELD1,
VARCHARFIELD2,
VARCHARFIELD3,
VARCHARFIELD4,
VARCHARFIELD5,
VARCHARFIELD6,
VARCHARFIELD7
VARCHARFIELD8,
VARCHARFIELD9,
VARCHARFIELD10,
VARCHARFIELD11,
VARCHARFIELD12,
DATEFIELD1
FROM SOURCE_VIEW AS b
GO

-- This is what i am trying to do
-- And it doesn't work
MERGE TARGET_TABLE AS t
USING SOURCE_VIEW AS s
ON (
t.VARCHARFIELD12 = s.VARCHARFIELD12 AND t.DATEFIELD1 = s.DATEFIELD1 AND t.VARCHARFIELD2 = s.VARCHARFIELD2)
WHEN not matched THEN INSERT VALUES(
INTFIELD1,
VARCHARFIELD1,
VARCHARFIELD2,
VARCHARFIELD3,
VARCHARFIELD4,
VARCHARFIELD5,
VARCHARFIELD6,
VARCHARFIELD7
VARCHARFIELD8,
VARCHARFIELD9,
VARCHARFIELD10,
VARCHARFIELD11,
VARCHARFIELD12,
DATEFIELD1
);

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-05 : 08:39:22

Try naming the columns you are inserting into.
MERGE TARGET_TABLE AS t
USING SOURCE_VIEW AS s
ON (
t.VARCHARFIELD12 = s.VARCHARFIELD12 AND t.DATEFIELD1 = s.DATEFIELD1 AND t.VARCHARFIELD2 = s.VARCHARFIELD2)
WHEN not matched by target THEN INSERT
(
INTFIELD1,
VARCHARFIELD1,
VARCHARFIELD2,
VARCHARFIELD3,
VARCHARFIELD4,
VARCHARFIELD5,
VARCHARFIELD6,
VARCHARFIELD7,
VARCHARFIELD8,
VARCHARFIELD9,
VARCHARFIELD10,
VARCHARFIELD11,
VARCHARFIELD12,
DATEFIELD1
)
VALUES
(
INTFIELD1,
VARCHARFIELD1,
VARCHARFIELD2,
VARCHARFIELD3,
VARCHARFIELD4,
VARCHARFIELD5,
VARCHARFIELD6,
VARCHARFIELD7
VARCHARFIELD8,
VARCHARFIELD9,
VARCHARFIELD10,
VARCHARFIELD11,
VARCHARFIELD12,
DATEFIELD1
);

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stanchernov
Starting Member

3 Posts

Posted - 2011-05-05 : 09:00:33

quote:
Originally posted by nigelrivett

Try naming the columns you are inserting into.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

YOU ROCK!!! thanks so very much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources