CROSS JOIN Usage

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 292
One of the advantages of CROSS JOIN is to generate sample data for testing cases...

Below code will show you how to generate large set of data with the help of CROSS JOIN...

First the table has populated with only 10 digits and then generating 1000 records with the help of CROSS JOIN....

IF OBJECT_ID('dbo.Digits', 'U') IS NOT NULL DROP TABLE dbo.Digits;
CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.Digits(digit)
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
SELECT digit FROM dbo.Digits;

--CROSS JOIN for generating 1 to 1000 numbers

SELECT D3.digit * 100 + D2.digit * 10 + D1.digit + 1 AS n
FROM dbo.Digits AS D1
CROSS JOIN dbo.Digits AS D2
CROSS JOIN dbo.Digits AS D3
ORDER BY n;

Comments or Responses

Login to post response