SQLite を SQL Server に変換

-- Written by IntoSQL from a SQLite dump. Load it with: sqlcmd -b -f 65001 -d <database> -i <file>
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO

CREATE TABLE [dbo].[customers] (
    [id] bigint IDENTITY(9, 1) NOT NULL,
    [name] nvarchar(max) COLLATE Latin1_General_100_BIN2 NOT NULL,
    [email] nvarchar(450) COLLATE Latin1_General_100_CI_AS_SC NOT NULL,
    [country] nvarchar(max) COLLATE Latin1_General_100_BIN2 DEFAULT N'US' NOT NULL,
    [is_active] bit DEFAULT 1 NOT NULL,
    [signed_up] date NULL,
    [created_at] datetime2(0) DEFAULT SYSDATETIME() NOT NULL,
    CONSTRAINT [PK_customers] PRIMARY KEY ([id])
);
GO

CREATE TABLE [dbo].[orders] (
    [id] bigint IDENTITY(11, 1) NOT NULL,
    [customer_id] bigint NOT NULL,
    [status] nvarchar(450) COLLATE Latin1_General_100_BIN2 DEFAULT N'pending' NOT NULL,
    [total] decimal(10,2) NOT NULL,
    [items] int DEFAULT 1 NOT NULL,
    [ordered_at] datetime2(0) NOT NULL,
    [shipped_at] datetime2(0) NULL,
    [note] nvarchar(max) COLLATE Latin1_General_100_BIN2 NULL,
    [receipt] varbinary(max) NULL,
    CONSTRAINT [check1] CHECK ([status] IN (N'pending', N'paid', N'shipped', N'cancelled')),
    CONSTRAINT [PK_orders] PRIMARY KEY ([id])
);
GO

SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(1,N'Ana Martínez',N'ana@example.com',N'MX',1,'2023-01-15','2023-01-15 10:24:00');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(2,N'Zoë Müller',N'zoe@example.de',N'DE',1,'2023-02-03','2023-02-03 08:11:42');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(3,N'José Peña',N'jose@example.es',N'ES',0,'2023-03-21','2023-03-21 19:05:17');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(4,N'李雷',N'lilei@example.cn',N'CN',1,'2023-05-09','2023-05-09 02:40:00');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(5,N'Noah O''Brien',N'noah@example.ie',N'IE',1,NULL,'2023-06-30 14:00:00');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(6,N'Amélie Laurent',N'amelie@example.fr',N'FR',1,'2024-01-02','2024-01-02 09:30:00');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(7,N'Ravi Patel',N'ravi@example.in',N'IN',0,'2024-02-29','2024-02-29 23:59:59');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[customers] ON;
INSERT INTO [dbo].[customers] ([id], [name], [email], [country], [is_active], [signed_up], [created_at]) VALUES
(8,N'Sofia Rossi 🌻',N'sofia@example.it',N'IT',1,'2024-04-12','2024-04-12 12:12:12');
SET IDENTITY_INSERT [dbo].[customers] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(1,1,N'shipped',129.9,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(2,2,N'paid',54,1,'2024-03-02 11:15:00',NULL,N'Gift wrap 🎁',NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(3,1,N'cancelled',12.5,1,'2024-03-03 09:45:00',NULL,N'Customer changed their mind',NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(4,4,N'shipped',980,12,'2024-03-05 02:10:00','2024-03-07 08:00:00',N'请小心轻放',0x25504446);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(5,5,N'pending',45.75,2,'2024-03-08 17:20:00',NULL,CAST(N'' AS nvarchar(max))+N'Leave at the door;'+NCHAR(10)+N'ring twice',NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(6,6,N'shipped',310.4,5,'2024-03-10 13:00:00','2024-03-11 09:00:00',N'Fragile: "glass"',NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(7,8,N'paid',76.2,2,'2024-03-11 18:45:00',NULL,NULL,NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(8,2,N'paid',19.99,1,'2024-03-12 08:00:00',NULL,N'Paid at the counter',NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(9,3,N'shipped',64,2,'2024-03-14 15:30:00',NULL,N'Shipping date never recorded',NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO
SET IDENTITY_INSERT [dbo].[orders] ON;
INSERT INTO [dbo].[orders] ([id], [customer_id], [status], [total], [items], [ordered_at], [shipped_at], [note], [receipt]) VALUES
(10,7,N'paid',1499,1,'2024-03-15 21:05:00',NULL,N'C:\Users\ravi\invoice.pdf',NULL);
SET IDENTITY_INSERT [dbo].[orders] OFF;
GO

-- Indexes, foreign keys, counters and triggers, after the rows.
CREATE UNIQUE INDEX [email] ON [dbo].[customers] ([email]);
CREATE INDEX [idx_orders_customer] ON [dbo].[orders] ([customer_id]);
CREATE INDEX [idx_orders_status_date] ON [dbo].[orders] ([status], [ordered_at]);
ALTER TABLE [dbo].[orders] WITH NOCHECK ADD CONSTRAINT [FK_orders] FOREIGN KEY ([customer_id]) REFERENCES [dbo].[customers] ([id]) ON DELETE CASCADE;
BEGIN TRY ALTER TABLE [dbo].[orders] WITH CHECK CHECK CONSTRAINT [FK_orders]; END TRY BEGIN CATCH PRINT N'Foreign key ' + N'[FK_orders]' + N' has rows without a parent: it stays unchecked, and new rows are checked.'; END CATCH;
DECLARE @next decimal(38,0) = (SELECT MAX([id]) FROM [dbo].[customers]); IF @next IS NOT NULL BEGIN IF @next < 8 SET @next = 8; DBCC CHECKIDENT (N'[dbo].[customers]', RESEED, @next) WITH NO_INFOMSGS; END;
GO
DECLARE @next decimal(38,0) = (SELECT MAX([id]) FROM [dbo].[orders]); IF @next IS NOT NULL BEGIN IF @next < 10 SET @next = 10; DBCC CHECKIDENT (N'[dbo].[orders]', RESEED, @next) WITH NO_INFOMSGS; END;
GO
GO