Migrate databases to SQL Server

--
-- Script was generated by Devart dbForge Studio for MySQL, Version 2025.2.109.0
-- Product home page: https://www.devart.com/dbforge/mysql/studio
-- Script date 13-Nov-25 14:21:44
-- Server version: 9.1.0
--

--
-- Disable foreign keys
--
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

--
-- Set SQL mode
--
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE salesdb;

--
-- Drop table `orderitems`
--
DROP TABLE IF EXISTS orderitems;

--
-- Drop table `products`
--
DROP TABLE IF EXISTS products;

--
-- Drop table `orders`
--
DROP TABLE IF EXISTS orders;

--
-- Drop table `customers`
--
DROP TABLE IF EXISTS customers;

--
-- Set default database
--
USE salesdb;

--
-- Create table `customers`
--
CREATE TABLE customers (
  CustomerID int NOT NULL,
  Name varchar(100) DEFAULT NULL,
  Email varchar(100) DEFAULT NULL,
  Phone varchar(20) DEFAULT NULL,
  Address varchar(150) DEFAULT NULL,
  City varchar(50) DEFAULT NULL,
  PRIMARY KEY (CustomerID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci,
ROW_FORMAT = DYNAMIC;

--
-- Create table `orders`
--
CREATE TABLE orders (
  OrderID int NOT NULL,
  CustomerID int DEFAULT NULL,
  OrderDate date DEFAULT NULL,
  TotalAmount decimal(10, 2) DEFAULT NULL,
  Status varchar(50) DEFAULT NULL,
  ShippingMethod varchar(50) DEFAULT NULL,
  PRIMARY KEY (OrderID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci,
ROW_FORMAT = DYNAMIC;

--
-- Create foreign key
--
ALTER TABLE orders
ADD CONSTRAINT orders_ibfk_1 FOREIGN KEY (CustomerID)
REFERENCES customers (CustomerID);

--
-- Create table `products`
--
CREATE TABLE products (
  ProductID int NOT NULL,
  Name varchar(100) DEFAULT NULL,
  Category varchar(50) DEFAULT NULL,
  Price decimal(10, 2) DEFAULT NULL,
  StockQty int DEFAULT NULL,
  Supplier varchar(100) DEFAULT NULL,
  PRIMARY KEY (ProductID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 4096,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci,
ROW_FORMAT = DYNAMIC;

--
-- Create table `orderitems`
--
CREATE TABLE orderitems (
  OrderItemID int NOT NULL,
  OrderID int DEFAULT NULL,
  ProductID int DEFAULT NULL,
  Quantity int DEFAULT NULL,
  UnitPrice decimal(10, 2) DEFAULT NULL,
  SubTotal decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (OrderItemID)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 3276,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci,
ROW_FORMAT = DYNAMIC;

--
-- Create foreign key
--
ALTER TABLE orderitems
ADD CONSTRAINT orderitems_ibfk_1 FOREIGN KEY (OrderID)
REFERENCES orders (OrderID);

--
-- Create foreign key
--
ALTER TABLE orderitems
ADD CONSTRAINT orderitems_ibfk_2 FOREIGN KEY (ProductID)
REFERENCES products (ProductID);

--
-- Restore previous SQL mode
--
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

--
-- Enable foreign keys
--
/*!40014 SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS */;
 

 -- Use the target database
USE salesdb;
GO

-- Drop tables if exist in correct order due to FKs
IF OBJECT_ID('dbo.orderitems', 'U') IS NOT NULL DROP TABLE dbo.orderitems;
IF OBJECT_ID('dbo.products', 'U') IS NOT NULL DROP TABLE dbo.products;
IF OBJECT_ID('dbo.orders', 'U') IS NOT NULL DROP TABLE dbo.orders;
IF OBJECT_ID('dbo.customers', 'U') IS NOT NULL DROP TABLE dbo.customers;
GO

-- Create table customers
CREATE TABLE dbo.customers (
  CustomerID int NOT NULL PRIMARY KEY,
  Name nvarchar(100) NULL,
  Email nvarchar(100) NULL,
  Phone nvarchar(20) NULL,
  Address nvarchar(150) NULL,
  City nvarchar(50) NULL
);
GO

-- Create table orders
CREATE TABLE dbo.orders (
  OrderID int NOT NULL PRIMARY KEY,
  CustomerID int NULL,
  OrderDate date NULL,
  TotalAmount decimal(10, 2) NULL,
  Status nvarchar(50) NULL,
  ShippingMethod nvarchar(50) NULL,
  CONSTRAINT FK_orders_customers FOREIGN KEY (CustomerID) REFERENCES dbo.customers(CustomerID)
);
GO

-- Create table products
CREATE TABLE dbo.products (
  ProductID int NOT NULL PRIMARY KEY,
  Name nvarchar(100) NULL,
  Category nvarchar(50) NULL,
  Price decimal(10, 2) NULL,
  StockQty int NULL,
  Supplier nvarchar(100) NULL
);
GO

-- Create table orderitems
CREATE TABLE dbo.orderitems (
  OrderItemID int NOT NULL PRIMARY KEY,
  OrderID int NULL,
  ProductID int NULL,
  Quantity int NULL,
  UnitPrice decimal(10, 2) NULL,
  SubTotal decimal(10, 2) NULL,
  CONSTRAINT FK_orderitems_orders FOREIGN KEY (OrderID) REFERENCES dbo.orders(OrderID),
  CONSTRAINT FK_orderitems_products FOREIGN KEY (ProductID) REFERENCES dbo.products(ProductID)
);
GO

-- Set database default collation if needed externally (not within table creation)
-- UTF8 requires SQL Server 2019+ and specifying COLLATE Latin1_General_100_CI_AS_SC_UTF8 on VARCHAR columns for Unicode UTF-8 storage.
-- Here we use NVARCHAR for Unicode storage.