Skip to content

Instantly share code, notes, and snippets.

@sinairv
Created July 25, 2012 04:51
Show Gist options
  • Save sinairv/3174468 to your computer and use it in GitHub Desktop.
Save sinairv/3174468 to your computer and use it in GitHub Desktop.
Generate test data from values specified for a subset of columns
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment