Errors while trying to run the sql file about countries-states-cities-database HOT 1 OPEN

🤖

The errors you're encountering are due to the fact that the SQL scripts in the repository are written for MySQL, not Microsoft SQL Server. The syntax and some features used in these scripts are specific to MySQL and are not directly compatible with SQL Server, hence the errors.

To resolve these issues, you'll need to convert the MySQL scripts to SQL Server scripts. This involves several steps, including adjusting data types, modifying auto increment syntax, adjusting default values, and removing or replacing MySQL-specific commands and syntax. Here's a simplified example of how you might start to convert the table creation part of your script for SQL Server:

CREATE TABLE countries (
  id int IDENTITY(1,1) PRIMARY KEY,
  name varchar(100) NOT NULL,
  iso3 char(3) DEFAULT NULL,
  numeric_code char(3) DEFAULT NULL,
  iso2 char(2) DEFAULT NULL,
  phonecode varchar(255) DEFAULT NULL,
  capital varchar(255) DEFAULT NULL,
  currency varchar(255) DEFAULT NULL,
  currency_name varchar(255) DEFAULT NULL,
  currency_symbol varchar(255) DEFAULT NULL,
  tld varchar(255) DEFAULT NULL,
  [native] varchar(255) DEFAULT NULL,
  region varchar(255) DEFAULT NULL,
  region_id int DEFAULT NULL,
  subregion varchar(255) DEFAULT NULL,
  subregion_id int DEFAULT NULL,
  nationality varchar(255) DEFAULT NULL,
  timezones text,
  translations text,
  latitude decimal(10,8) DEFAULT NULL,
  longitude decimal(11,8) DEFAULT NULL,
  emoji varchar(191) DEFAULT NULL,
  emojiU varchar(191) DEFAULT NULL,
  created_at datetime2 NULL DEFAULT NULL,
  updated_at datetime2 NOT NULL,
  flag tinyint NOT NULL DEFAULT '1',
  wikiDataId varchar(255) DEFAULT NULL
);

This is just a starting point. Depending on the specific errors you're encountering, additional modifications might be necessary. You'll also need to convert the INSERT statements and any other parts of the script accordingly.

For more detailed information on how to convert MySQL scripts to SQL Server scripts, you might find this MySQL to SQL Server Conversion Guide helpful.

Please note that this conversion process can be complex and time-consuming, especially for larger scripts. If you're frequently working with both MySQL and SQL Server, you might want to consider using a database migration tool to automate some of these tasks.

I hope this helps! If you have any further questions or run into any issues during the conversion process, please don't hesitate to ask.

Sources

• sql/countries.sql

from countries-states-cities-database.