Convert nvarchar to bigint in Sql server 2008

I want insert all rows of a table into another table, and I also want convert a nvarchar field into bigint, but when I use convert(bigint, col1) SQL Server shows an error:

Error converting data type nvarchar to bigint

How can I fix this problem?

Answer

You could try to use ISNUMERIC to determine those rows that are indeed numeric:

UPDATE dbo.YourTable
SET BigIntColumn = CAST(NVarcharColumn AS BIGINT)
WHERE ISNUMERIC(NVarcharColumn) = 1

That would convert those rows that can be converted – the others need to be dealt with manually.