248

I've asked a question about a simple SQL query, and now some users are telling me that I need to include a Minimal, Reproducible Example (MRE/MCVE).

Obviously I've read the help pages, but my question is so simple that I can't understand why I need to provide such an example. I wouldn't even know how to go about doing that if I wanted to, and besides, I'm not allowed to share personal data. Oh, and the database design is proprietary.

Canonical link: [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952)

Return to FAQ index

4
  • 13
    If it's simple, then it's that much easier to make an MCVE. Commented Oct 9, 2020 at 12:31
  • 1
    if you ask a question with a minimal reproducible example, it doesn't need an expert to answer, Even a Noob programmer can try the problem and will find a solution for you.
    – Vignesh
    Commented Feb 25, 2021 at 10:10
  • 2
    Doesn't this reasoning apply for everything, not just SQL? Commented Jul 10, 2022 at 23:59
  • Canonical link is useful too.
    – IsmailS
    Commented Aug 17, 2023 at 4:40

2 Answers 2

365

Lots of questions about the construction of SQL queries are asked on Stack Overflow every day, each competing for attention with the dozens of others that are asked. Those that take the trouble to provide minimal, reproducible examples are far more likely to be answered quickly, and accurately.

It's easy to provide a sample data set. It doesn't have to be real data. It just has to fairly reflect the nature of the problem at hand.

Similarly for the schema itself; to solve your specific problem, we don't generally need to see the whole thing—and we definitely don't need to see some dull entity relationship diagram, or scrappy screen captures of Excel spreadsheets. Instead, only the relevant tables and a dozen thoughtfully chosen rows of data is usually enough to understand what should be included and/or excluded from a desired result set.

So, it's nice to see three things:

  1. CREATE and INSERT statements (and/or an sqlfiddle or rextester or db-fiddle or db<>fiddle) for all relevant tables, so that we can more easily replicate the problem. Ideally, these should clearly identify PRIMARY/UNIQUE KEYS and incorporate the proper use of data types—so dates are properly formatted (YYYY-MM-DD).

    An ASCII data table generator tool can assist you with the construction of ASCII data tables which some of the websites mentioned above can then convert directly into CREATE TABLE and INSERT statements for multiple database systems like MySQL, PostgreSQL or SQL Server (MSSQL).

  2. A desired result set that corresponds with the information provided in step 1.

  3. Your best efforts to date. These don't have to be stellar pieces of cogent analysis. We just like to see that you've taken some steps towards attempting to solve the problem for yourself. It also helps us to know where to start in our explanation.

6
  • 7
    You can also format your SQL query using this website. To generate tables, I use this website.
    – DxTx
    Commented Mar 23, 2019 at 15:01
  • @DxTx: more like web apps than sites
    – mike3996
    Commented Aug 6, 2019 at 12:16
  • 3
    If the SQL question is performance based those will benefit by adding a explain plan for your RDMS and can also benefit when you add a CREATE TABLE ... for each table search in your RDMS manual or on internet as there might be a easy way to generate this from a existing table so that datatypes and defined indexes are directly known to us.. Commented Oct 10, 2019 at 9:32
  • 1
    Is there any online website, where I paste my table, and then generate the sql query corresponding the create table .. ?
    – Areza
    Commented Nov 22, 2019 at 8:34
  • @user702846 See the text to ddl feature at sqlfiddle.com
    – Strawberry
    Commented Nov 22, 2019 at 10:26
  • 1
    @DxTx - I realise it's now almost 2 years since your comment about making tables, but for everyone finding this post from now on you can create tables in SO posts using the GitHub-flavored markdown format.
    – Tony
    Commented Jan 27, 2021 at 22:48
3

Primarily and most importantly, because it's a simple thing to do and a courtesy to others who are hoping to help. To make things even simpler for myself (and others if they care to use it), I've created this script below to be able to quickly create a minimal reproducible example copy of any original table including actual sample data, without having to manually script out the tables and data. Then you can easily change any proprietary info in the results before posting it to a question.

This script uses any original table and generates the following PRINT statements:

  • DROP and CREATE a temp table with structure matching the original table
  • INSERT INTO statement using samples from the actual data of the original table

Just add the original table name into the variable listed, along with the number of sample records required from the table. It will generate all of the statements needed in the Messages window in SSMS. Then just copy and paste those statements into your question, so those answering have something to work with.

I know that you can get similar results in SSMS through Tasks>Generate Scripts, but this script gets things down to the basic minimal amount of code that's useful for posting here without all of the unnecessary info that SSMS generates automatically. It's just a quick way to create a reproduced version of a simple table with actual sample data in it.

Please note: This doesn't work 100% if you run it for very wide tables, but you don't really want that for a minimal reproducible example on SO anyway.


/* ---------------------------------------
-- For creating minimal reproducible examples
-- based on original table and data,
-- builds the following statements
--    -- CREATE temp table with structure matching original table
--    -- INSERT statements based on actual data
--
-- Note: May not work for very wide tables due to limitations on 
--         PRINT statements
*/ ---------------------------------------

DECLARE @tableName NVARCHAR(MAX) = 'testTable', -- original table name HERE
        @recordCount INT = 5,                   -- top number of records to insert to temp table
        @buildStmt NVARCHAR(MAX),
        @insertStmt NVARCHAR(MAX),
        @valuesStmt NVARCHAR(MAX),
        @insertCol NVARCHAR(MAX),
        @strAgg NVARCHAR(MAX),
        @insertOutput NVARCHAR(MAX)

IF (EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tableName))
BEGIN

    -- build DROP and CREATE statements for temp table from original table
    SET @buildStmt = 'IF OBJECT_ID(''tempdb..#' + @tableName + ''') IS NOT NULL DROP TABLE #' + @tableName + CHAR(10) + CHAR(10) +
               'CREATE TABLE #' + @tableName + ' (' + CHAR(10)

    SELECT @buildStmt = @buildStmt + ' ' + C.[Name] + ' ' +
                T.[Name] +
                CASE WHEN T.[Name] IN ('varchar','varchar','char','nchar') THEN '(' + CAST(C.[Length] AS VARCHAR) + ') ' ELSE ' ' END +
                'NULL,' + CHAR(10)
    FROM sysobjects O
    JOIN syscolumns C ON C.id = O.id
    JOIN systypes T ON T.xusertype = C.xusertype
    WHERE O.[name] = @TableName
    ORDER BY C.ColID

    SET @buildStmt = SUBSTRING(@buildStmt,1,LEN(@buildStmt) - 2) + CHAR(10) + ')' + CHAR(10)
    PRINT @buildStmt

    -- build INSERT INTO statement from original table
    SELECT @insertStmt =  'INSERT INTO #' + @tableName + ' (' +
        STUFF ((
            SELECT ', [' + C.[Name] + ']'
            FROM sysobjects O
            JOIN syscolumns C ON C.id = O.id
            WHERE O.[name] = @TableName
            ORDER BY C.ColID
            FOR XML PATH('')), 1, 1, '') 
        +')' 

    PRINT @insertStmt

    -- build VALUES portion of INSERT from data in original table
    SELECT @insertCol = STUFF ((
            SELECT '''''''''+CONVERT(NVARCHAR(200),' +
            '[' + C.[Name] + ']' + 
            ')+'''''',''+' 
            FROM sysobjects O
            JOIN syscolumns C ON C.id = O.id
            JOIN systypes T ON T.xusertype = C.xusertype
            WHERE O.[name] = @TableName
            ORDER BY C.ColID
            FOR XML PATH('')), 1, 1, '')
    SET @insertCol = SUBSTRING(@insertCol,1,LEN(@insertCol) - 1) 

    SELECT @strAgg = ';WITH CTE AS (SELECT TOP(' + CONVERT(VARCHAR,@recordCount) + ') * FROM ' + @tableName + ') ' +
                     ' SELECT @valuesStmt = STRING_AGG(CAST(''' + @insertCol + ' AS NVARCHAR(MAX)),''),  ('') ' +
                     ' FROM CTE' 
    EXEC sp_executesql @strAgg,N'@valuesStmt NVARCHAR(MAX) OUTPUT', @valuesStmt OUTPUT

    PRINT 'VALUES (' +REPLACE(SUBSTRING(@valuesStmt,1,LEN(@valuesStmt) - 1),',)',')') + ')'

END
ELSE
BEGIN
    PRINT 'Table does NOT exist'
END
1
  • 1
    Apparently, one of the criteria for faq-proposed questions is that they contain one answer, and that answer addresses all points. While an admirable endeavour, this may be better suited as an answer to a separate question.
    – Strawberry
    Commented Apr 15, 2021 at 12:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .