CODEWITHSUNDEEP
sql-serversql-server-2014
F.kouhbor
F.kouhbor

Reputation: 35

Convert nvarchar to bigint from SQL Server 2014

I want to copy sqi_main from middleman24 to sqi_test in sqitable.

I use this command in SQL Server 2014:

INSERT INTO sqitable(sqi_test) 
    SELECT sqi_main  
    FROM Middleman24

sqi_main content is

2
3
1
-5
30
-1
50

But I get this error:

Error converting data type nvarchar to bigint.

I have tried other solutions like 

INSERT INTO sqitable(sqi_test) 
    SELECT sqi_main  
    FROM Middleman24 
    WHERE ISNUMERIC(sqi) = 1 
      AND sqi <> ''

But again I get the same error.

Upvotes: 1

Views: 2891

Answers (2)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

You can just use CAST if data is clean:

SELECT CAST(sqi_main as bigint) FROM Middleman24;

Otherwise, use TRY_PARSE:

SELECT TRY_PARSE(sqi_main as bigint) FROM Middleman24;

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

You have SOME data somewhere that can't be converted to a bigint, and as one of the commenters mentioned, ISNUMERIC isn't a perfect solution.

Since you're using SQL 2014, use the TRY_CONVERT() function (TRY_PARSE() can also be used). Google or search MSDN for all the details.

Upvotes: 1

Related Questions