Is there a “nicer” way to achieve the same results as this SQL query:
x
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.