Generate test data from values specified for a subset of columns

CreateTestData.sql

-- Populate test data from values specified for a subset of columns

USE TestDataBase;
GO

-- think of it as the main table to be filled
CREATE Table #SomeTable
(
    ID uniqueidentifier,
    Name varchar(50),
    Family varchar(50),
    Age int,
    CreationDate datetime,
    OtherData varchar(50)
)

GO

-- think of it as a table of values given to us to fill the first table accordingly.
-- this table specifies only some columns of the data, 
-- other columns are going to be filled with default or calculated values.
CREATE Table #TestData
(
    SomeName varchar(50),
    SomeFamily varchar (50),
    SomeAge int
)

GO

INSERT INTO #TestData
VALUES ('John', 'Doe', 29), ('Jane', 'Doe', 28), ('Steve', 'Black', 18)

GO

SELECT * FROM #TestData

GO

-- this is the main query doing the job
-- see how default values are generated for other columns
-- and see when the order of columns are OK, there's no need to fix
-- column names explicitly.

INSERT INTO #SomeTable 
SELECT NEWID(), TD.*, GETDATE(), NULL FROM #TestData TD

GO

SELECT * FROM #SomeTable

GO

DROP TABLE #SomeTable

GO

DROP TABLE #TestData

GO