This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 1 year has passed since last update.

sql sample

Posted at 2023-06-17

TestTable

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
	[data] [nvarchar](50) NULL,
	[number] [bigint] NOT NULL,
	[updataDate] [datetimeoffset](7) NOT NULL,
	[createDate] [datetimeoffset](7) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [DF_TestTable_updataDate]  DEFAULT (sysdatetimeoffset()) FOR [updataDate]
GO

ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [DF_TestTable_createDate]  DEFAULT (sysdatetimeoffset()) FOR [createDate]

TestTable2

USE [polybasetest]
GO

/****** Object:  Table [dbo].[TestTable2]    Script Date: 2023/06/17 9:26:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable2](
	[number] [bigint] NOT NULL,
	[data] [nvarchar](50) NOT NULL,
	[textstring] [nvarchar](10) NULL,
	[fromdate] [datetimeoffset](7) NULL,
	[todate] [datetimeoffset](7) NULL
) ON [PRIMARY]
GO

TestTable3

USE [polybasetest]
GO

/****** Object:  Table [dbo].[TestTable3]    Script Date: 2023/06/17 9:27:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable3](
	[number] [bigint] NOT NULL,
	[data] [nvarchar](50) NOT NULL,
	[textstring] [nvarchar](10) NULL,
	[fromdate] [datetimeoffset](7) NULL,
	[todate] [datetimeoffset](7) NULL
) ON [PRIMARY]
GO

TestTable4

USE [polybasetest]
GO

/****** Object:  Table [dbo].[TestTable3]    Script Date: 2023/06/17 9:27:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable3](
	[number] [bigint] NOT NULL,
	[data] [nvarchar](50) NOT NULL,
	[textstring] [nvarchar](10) NULL,
	[fromdate] [datetimeoffset](7) NULL,
	[todate] [datetimeoffset](7) NULL
) ON [PRIMARY]
GO

USE [polybasetest]
GO
/****** Object:  Table [dbo].[TestTable4]    Script Date: 2023/06/17 9:29:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable4](
	[number] [bigint] NOT NULL,
	[data] [nvarchar](50) NOT NULL,
	[textstring2] [nvarchar](10) NULL,
	[fromdate] [datetimeoffset](7) NULL,
	[todate] [datetimeoffset](7) NULL
) ON [PRIMARY]
GO

SELECT [TestTable2].[number]
      ,[TestTable2].[data]
      ,[TestTable2].[textstring]
      ,[TestTable2].[fromdate]
      ,[TestTable2].[todate]
	  ,[TestTable3].[number]
      ,[TestTable3].[data]
      ,[TestTable3].[textstring]
      ,[TestTable3].[fromdate]
      ,[TestTable3].[todate]
  FROM [TestTable2]
  INNER JOIN [TestTable3] ON [TestTable2].[number] = [TestTable3].[number]

SELECT [TestTable2].[number]
      ,[TestTable2].[data]
      ,[TestTable2].[textstring]
      ,[TestTable2].[fromdate]
      ,[TestTable2].[todate]
	  ,[TestTable3].[number]
      ,[TestTable3].[data]
      ,[TestTable3].[textstring]
      ,[TestTable3].[fromdate]
      ,[TestTable3].[todate]
  FROM [TestTable2]
  INNER JOIN [TestTable3] ON [TestTable2].[number] = [TestTable3].[textstring]

SELECT [TestTable2].[number]
      ,[TestTable2].[data]
      ,[TestTable2].[textstring]
      ,[TestTable2].[fromdate]
      ,[TestTable2].[todate]
	  ,[TestTable4].[number]
      ,[TestTable4].[data]
      ,[TestTable4].[textstring2]
      ,[TestTable4].[fromdate]
      ,[TestTable4].[todate]
  FROM [TestTable2]
  INNER JOIN [TestTable4] ON [TestTable2].[number] = [TestTable4].[textstring2]

SELECT [TestTable2].[number]
      ,[TestTable2].[data]
      ,[TestTable2].[textstring]
      ,[TestTable2].[fromdate]
      ,[TestTable2].[todate]
	  ,[TestTable4].[number]
      ,[TestTable4].[data]
      ,[TestTable4].[textstring2]
      ,[TestTable4].[fromdate]
      ,[TestTable4].[todate]
  FROM [TestTable2]
  INNER JOIN [TestTable4] ON [TestTable2].[textstring] = [TestTable4].[textstring2]

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up