How to easily set up Stackoverflow sample CREATE TABLE/INSERT VALUES statements

I am looking for easy way to generate the DROP TABLE, CREATE TABLE and INSERT VALUES statements from a SELECT statement for my examples that I post on Stackoverflow.

I can normally get a SELECT statement to return a small subset of the data that I want to use as a sample. From there I would like a DROP TABLE, CREATE TABLE and I开发者_运维问答NSERT VALUES to be generated.

Here is an example of the SELECT statement

SELECT ad.ApplicantDetailID,
    ad.ApplicantMasterID,
    ad.DateEffective,
    ad.CorrespondenceTypeID,
    ad.Value
FROM tblApplicantDetail AS ad
WHERE ad.CorrespondenceTypeID = 34
AND ad.ApplicantMasterID IN (41145,37046,45536,46764)
ORDER BY ad.ApplicantMasterID, ad.DateEffective

This SELECT statemenat returns 6 rows.

What I wouuld like a function to do is take a SELECT statement as input and generated the following TSQL Code (DROP TABLE, CREATE TABLE and INSERT VALUES). A second parameter can be used to send in a Temp Table name (i.e. in the example below #ApplicantDetail)

IF OBJECT_ID('tempdb..#ApplicantDetail') IS NOT NULL DROP TABLE #ApplicantDetail
CREATE TABLE #ApplicantDetail (
    [ApplicantDetailID] [int] NOT NULL,
    [ApplicantMasterID] [int] NOT NULL,
    [DateEffective] [datetime] NOT NULL,
    [CorrespondenceTypeID] [int] NOT NULL,
    [Value] [varchar](20) NULL
    )
GO   
INSERT #ApplicantDetail VALUES (197542,37046,'2003-05-13 00:00:00.000',34,'8')
INSERT #ApplicantDetail VALUES (217963,41145,'1994-03-11 00:00:00.000',34,'')
INSERT #ApplicantDetail VALUES (217965,41145,'1994-03-21 00:00:00.000',34,'NS')
INSERT #ApplicantDetail VALUES (238961,45536,'2003-10-22 00:00:00.000',34,'')
INSERT #ApplicantDetail VALUES (238963,45536,'2003-12-03 00:00:00.000',34,'4')
INSERT #ApplicantDetail VALUES (244910,46764,'2003-12-03 00:00:00.000',34,'NS')

From there, I am hoping to just cut and paste the code generated to Stackoverflow.

This function will save me (an the Stackoverflow community) a lot of time when setting up some test data samples.

Since the #ApplicantDetail is created above you can use this SELECT statement in your example. It will return two records.

SELECT * 
FROM #ApplicantDetail AS ad
WHERE ad.ApplicantMasterID = 45536

Maybe call the function GenerateSampleTSQLData.

Here is an idea on how the Function can look. I am not an expert on creating Functions so do change the syntax as needed.

GenerateSampleTSQLData('SELECT * 
  FROM #ApplicantDetail AS ad
  WHERE ad.ApplicantMasterID = 45536',
'#AppDet')

I am running on SQL Server 2005 but will be upgrading to 2008.

If you can get it to run on one of them it would be great.

Now I am off to post another question that will actually use this sample data.

Thanks a bunch.

step 1..download SSMS Tools Pack (it is free) and install it http://www.ssmstoolspack.com/

step 2 run query

step 3 right click on result and select Script Grid Results (see image below)