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.