SQL Lite Data Types VS T-SQL Data Types

Question

SQL Lite Data Types VS T-SQL Data Types

yrstruly

Hall of Fame

Points: 3475
More actions
September 11, 2019 at 7:24 am

#3679027

I am having trouble running this SQL Lite on my T-SQL. I changed the NUMBER to NUMERIC n Decimal in SQL Server. Still i am getting this error. What would be the equivalent data type in SQL Server. All of the other SQL Lite code are running on my SQL Server Express, except this:
DROP TABLE IF EXISTS CountryLanguage;
CREATE TABLE CountryLanguage (
CountryCode TEXT NOT NULL DEFAULT '',
Language TEXT NOT NULL DEFAULT '',
IsOfficial INTEGER NOT NULL DEFAULT '0',
Percentage NUMBER NOT NULL default '0.0',
PRIMARY KEY (CountryCode,Language)
);
--
-- Dumping data for table CountryLanguage
--
INSERT INTO CountryLanguage VALUES ('AFG','Pashto',1,52.4);
INSERT INTO CountryLanguage VALUES ('NLD','Dutch',1,95.6);

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Could you explain a little more what you're trying to accomplish? Sql Lite and Sql Server are very different databases. From a Sql Server perspective there are a bunch of issues with the table definition you've posted. The 'text' column type is deprecated, I think. Some column name are reserved words (please don't ever). Also, assigning default values to elements of a composite primary key is not typically done in Sql Server. As far as the specific issue of 'Cannot find data type NUMBER', the intellesense has underlined the offending word for you. If you're storing percentages then using the INT column type and dividing by 100 (when access the column) could be a useful alternative.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

Number isn't a data type

use decimal and specify the precision and scale

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017

MVDBA

Mohammad A Newbie Points: 1 More actions · Answer 3

I have the same issue but it has been resolved now

DROP TABLE IF EXISTS CountryLanguage;

CREATE TABLE CountryLanguage (

CountryCode char (3) NOT NULL DEFAULT '',

Language char (30) NOT NULL DEFAULT '',

IsOfficial INTEGER NOT NULL DEFAULT '0',

Percentage Decimal (4,1) NOT NULL DEFAULT '0.0',

PRIMARY KEY (CountryCode, Language)

);or

DROP TABLE IF EXISTS CountryLanguage;

CREATE TABLE CountryLanguage (

CountryCode char (3) NOT NULL DEFAULT '',

Language char (30) NOT NULL DEFAULT '',

IsOfficial INTEGER NOT NULL DEFAULT '0',

Percentage Float NOT NULL DEFAULT '0.0',

PRIMARY KEY (CountryCode, Language)

);

hope this helps,

Mohammad