Create and Insert N rows in sql without user interface

 To insert bulk data for testing purpose we can use below query. this query create the table and insert the rows and if required delete the table too.

CREATE TABLE TEST1 (TEST_ID INT IDENTITY(1,1), Name varchar(50), InsDate datetime)

SET IDENTITY_INSERT TEST1 ON;

DECLARE @numRows int,@i int

SET @numRows = 10000

SET @i=1

Declare @Prefix varchar(50) = '_';

WHILE @i<@numRows

BEGIN

Set @Prefix += Cast(@i as varchar(50));

if RIGHT(Cast(@i as varchar(50)), 1) = '0' Set @Prefix = '_';

    INSERT TEST1(TEST_ID,Name,InsDate) SELECT @i,('Murli' + @Prefix),Getdate()

    SET @i=@i+1

END

SET IDENTITY_INSERT TEST1 OFF;

SELECT * FROM TEST1

--DROP TABLE TEST1