CODEWITHSUNDEEP
sqlsql-server
Aligator3000
Aligator3000

Reputation: 345

Datatype compatibility: int vs. nvarchar

I am using below sql statement to read and import data from excel spreadsheet to database table:

INSERT INTO [dbo].[TestTable] ([ColumnA], [ColumnB], [ColumnC], [ColumnD])
SELECT A.[AAA], A.[BBB], A.[CCC], A.[DDD] FROM OPENROWSET 
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\TestFile\01-TestFile.xlsx;HDR=YES', 'select * from [Sheet$]') AS A;

TestTable has four columns, only accepting integer datatypes. The import works well with numbers in the database spreadsheet. However, if for some reason there is a mistake in the file (i.e. there is text instead of digits), it fails and I get an error converting nvarchar to int datatype. Is there any way to ensure the upload still works - that text is ommitted and appears as NULL in the table?

Upvotes: 0

Views: 220

Answers (1)

user11380812
user11380812

Reputation:

Try TRY_CAST as shown below. Works for SQL Server 2012+. 

SELECT TRY_CAST('TEST' AS INT)

See more on TRY_CAST (Transact-SQL)

Upvotes: 2

Related Questions