inserting a special character into a table
It is possible to add that character as long as either:

the destination column is NCHAR / NVARCHAR / XML (NTEXT also works, but has been deprecated since the release of SQL Server 2005 so don't use it),
OR

the destination column is CHAR / VARCHAR and the Collation of the column uses a Code Page that supports the character(s) in question.


So, make sure to do the following:

Prefix the string with N (not required if the current Database's default Collation uses a Code Page that supports the character, but helps in that it removes the Database's default Collation from being a factor that could cause this issue -- reason being that if the current DB's default Collation uses a Code Page that doesn't support the character, then it will be converted to either a &quot;best fit&quot; match, or ? if no best fit match can be found, while it is being held in a variable or string literal).
Along those same lines, if this is coming into a stored procedure or parameterized query batch via a parameter and the parameter is of type CHAR or VARCHAR, then the current Database's default Collation does not use a Code Page that supports the character, so switch to using NVARCHAR / NCHAR as the parameter datatype.
If the destination column is VARCHAR / CHAR, then be sure that the Collation of the column uses a Code Page that supports that character.
If the destination column is VARCHAR / CHAR and you cannot find a Collation that uses a Code Page that supports the character(s) in question, then you need to convert the column to NVARCHAR / NCHAR so that it can store any character.   ( This is what worked for the O.P. )

For more info on working with Collations, please visit: Collations.info