Create master key for external tables for cross db scripting.

Azure_external_tables_scripts.txt

/*
[aspnet_Membership]
[aspnet_Profile_CDB]
[aspnet_WebEvent_Events]
When you use insert into query, above table's columns will need to be converted to nvarchar instead of ntext for some fields
*/
/* ############# Below section only needs to be run once ###################*/
CREATE MASTER KEY ENCRYPTION BY PASSWORD='hztl@9999'
CREATE DATABASE SCOPED CREDENTIAL crossdbcred2
WITH
    IDENTITY = 'sa', -- user name of azure sql db
    SECRET = 'redacted'; --use azure sql admin password only and not others
CREATE EXTERNAL DATA SOURCE extdtsrc1 WITH
(
    TYPE = RDBMS,
    LOCATION = N'azure db url', -- example my-site-dev-sql.database.windows.net
    DATABASE_NAME = N'sitecore.Core_18-08-2023',
    CREDENTIAL = crossdbcred2
)
/* ############# Below section only needs to be run once ###################*/
/* ################ Start creating new external tables from source DB to detination DB with actual data #################*/
CREATE EXTERNAL TABLE [dbo].[aspnet_Membership_CDB](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[Password] [nvarchar](128) NOT NULL,
	[PasswordFormat] [int] NOT NULL,
	[PasswordSalt] [nvarchar](128) NOT NULL,
	[MobilePIN] [nvarchar](16) NULL,
	[Email] [nvarchar](256) NULL,
	[LoweredEmail] [nvarchar](256) NULL,
	[PasswordQuestion] [nvarchar](256) NULL,
	[PasswordAnswer] [nvarchar](128) NULL,
	[IsApproved] [bit] NOT NULL,
	[IsLockedOut] [bit] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[LastLoginDate] [datetime] NOT NULL,
	[LastPasswordChangedDate] [datetime] NOT NULL,
	[LastLockoutDate] [datetime] NOT NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL,
	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
	[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
	[Comment] [nvarchar](max) NULL,
) 
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_Membership'
)
GO
/****** Object:  Table [dbo].[aspnet_Paths]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_Paths_CDB](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[PathId] [uniqueidentifier] NOT NULL,
	[Path] [nvarchar](256) NOT NULL,
	[LoweredPath] [nvarchar](256) NOT NULL,
) 
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_Paths'
)
GO
/****** Object:  Table [dbo].[aspnet_PersonalizationAllUsers]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_PersonalizationAllUsers_CDB](
	[PathId] [uniqueidentifier] NOT NULL,
	[PageSettings] [image] NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
) 
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_PersonalizationAllUsers'
)
GO
/****** Object:  Table [dbo].[aspnet_PersonalizationPerUser]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_PersonalizationPerUser_CDB](
	[Id] [uniqueidentifier] NOT NULL,
	[PathId] [uniqueidentifier] NULL,
	[UserId] [uniqueidentifier] NULL,
	[PageSettings] [image] NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
) 
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_PersonalizationPerUser'
)
GO
/****** Object:  Table [dbo].[aspnet_Profile]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_Profile_CDB](
	[UserId] [uniqueidentifier] NOT NULL,
	[PropertyNames] [nvarchar](max) NOT NULL,
	[PropertyValuesString] [nvarchar](max) NOT NULL,
	[PropertyValuesBinary] [image] NOT NULL,
	[LastUpdatedDate] [datetime] NOT NULL,
)
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_Profile'
)
GO
/****** Object:  Table [dbo].[aspnet_Roles]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_Roles_CDB](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
	[RoleName] [nvarchar](256) NOT NULL,
	[LoweredRoleName] [nvarchar](256) NOT NULL,
	[Description] [nvarchar](256) NULL,
) 
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_Roles'
)
GO
/****** Object:  Table [dbo].[aspnet_SchemaVersions]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_SchemaVersions_CDB](
	[Feature] [nvarchar](128) NOT NULL,
	[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
	[IsCurrentVersion] [bit] NOT NULL,
)
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_SchemaVersions'
)
GO
/****** Object:  Table [dbo].[aspnet_Users]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_Users_CDB](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[UserName] [nvarchar](256) NOT NULL,
	[LoweredUserName] [nvarchar](256) NOT NULL,
	[MobileAlias] [nvarchar](16) NULL,
	[IsAnonymous] [bit] NOT NULL,
	[LastActivityDate] [datetime] NOT NULL,
)
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_Users'
)
GO
/****** Object:  Table [dbo].[aspnet_UsersInRoles]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_UsersInRoles_CDB](
	[UserId] [uniqueidentifier] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
) 
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_UsersInRoles'
)
GO
/****** Object:  Table [dbo].[aspnet_WebEvent_Events]    Script Date: 21-08-2023 12:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE EXTERNAL TABLE [dbo].[aspnet_WebEvent_Events_CDB](
	[EventId] [char](32) NOT NULL,
	[EventTimeUtc] [datetime] NOT NULL,
	[EventTime] [datetime] NOT NULL,
	[EventType] [nvarchar](256) NOT NULL,
	[EventSequence] [decimal](19, 0) NOT NULL,
	[EventOccurrence] [decimal](19, 0) NOT NULL,
	[EventCode] [int] NOT NULL,
	[EventDetailCode] [int] NOT NULL,
	[Message] [nvarchar](1024) NULL,
	[ApplicationPath] [nvarchar](256) NULL,
	[ApplicationVirtualPath] [nvarchar](256) NULL,
	[MachineName] [nvarchar](256) NOT NULL,
	[RequestUrl] [nvarchar](1024) NULL,
	[ExceptionType] [nvarchar](256) NULL,
	[Details] [nvarchar](max) NULL,
) 
WITH(
    DATA_SOURCE = extdtsrc1,
    SCHEMA_NAME = 'dbo',
    OBJECT_NAME = 'aspnet_WebEvent_Events'
)
GO
/* ################ Start creating new external tables from source DB to detination DB with actual data #################*/