Skip to content
Advertisement

Repeat insert statement without GO

Is there a “nicer” way to achieve the same results as this SQL query:

INSERT INTO [MyDB]..[MyTable] VALUES
(NULL,NULL,NULL,NULL)
GO 300

Which is to insert 300 default all NULL (except PK Id col) new lines into an empty table? It seems to take a while!

Also, the value 300 could be completely variable e.g. something like

GO select max(Id) from [SERVER_X].[MyDb].[dbo].[Logs]

but that obviously doesn’t work:

 A fatal scripting error occurred.
 Incorrect syntax was encountered while parsing GO.

Advertisement

Answer

You can use a recursive CTE:

with n as (
      select 1 as n, max(Id) as max_id 
      from [SERVER_X].[MyDb].[dbo].[Logs]
      union all
      select n + 1, max_id
      from n
      where n < max_id
     )
insert into t
    select null, null, null, null
    from n
option (maxrecursion 0);

Inserting rows where all values are NULL seems to be not very useful. But, here is a db<>fiddle with an identity id column.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement