How Do I Populate A Table That Contains Only An Identity Column?

Consider the following table CREATE TABLE [dbo].[Numbers] ( [Id] [INT] IDENTITY(1,1) NOT NULL ) ON [PRIMARY] How do I populate it? If I add a column (text nchar(20) say) then

Solution 1:

Specify DEFAULT VALUES:

INSERTINTO dbo.Numbers DEFAULTVALUES;

Solution 2:

Or even you can use SET IDENTITY_INSERT:

SET IDENTITY_INSERT YourTable ON;  
GO 
INSERTINTO YourTable (IdentityColumn) VALUES
(1),
(2),
(3);
GO
SET IDENTITY_INSERT YourTable OFF;

Sample:

CREATETABLE [dbo].[Numbers](
    [Id] [int] IDENTITY(1,1) NOTNULL
) ON [PRIMARY];

DECLARE@StartINT=1;
DECLARE@EndINT=100;

SET IDENTITY_INSERT [dbo].[Numbers] ON;
WITH Gen AS (
    SELECT@StartAS Num
    UNIONALLSELECT Num +1FROM Gen WHERE Num +1<=@End
)
INSERTINTO [dbo].[Numbers] (Id)
SELECT Num 
FROM Gen
OPTION (maxrecursion 100);

SET IDENTITY_INSERT [dbo].[Numbers] OFF;

SELECT*FROM [dbo].[Numbers];