How to post a T-SQL question on a public forum
If you want to have faster turnaround on your forum questions, you will need to provide enough information to the forum users in order to answer your question.
In particular, talking about T-SQL questions, there are three things that your question must include:
- Table scripts
- Sample data
- Expected output
Table Script and Sample data
Please make sure that anyone trying to answer your question can quickly work on the same data set you’re working on, or, at least the problematic part of it. The data should be in the same place where you have it, which is inside your tables.
You will have to provide a script that creates your table and inserts data inside that table.
Converting your data to INSERT statements can be tedious: fortunately, some tools can do it for you.
How do you convert a SSMS results grid, a CSV file or an Excel spreadsheet to INSERT statements? In other words, how do you convert this…
into this?
USE [tempdb] GO CREATE TABLE [dbo].[Person]( [BusinessEntityID] [int] NOT NULL PRIMARY KEY CLUSTERED, [PersonType] [nchar](2) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL ) GO INSERT INTO Person VALUES (6106,'IN','Beth','Carlson'); INSERT INTO Person VALUES (17889,'IN','Dennis','Li'); INSERT INTO Person VALUES (17989,'IN','Brent','Li'); INSERT INTO Person VALUES (9424,'IN','Brad','Raji'); INSERT INTO Person VALUES (5842,'IN','Aimee','She'); INSERT INTO Person VALUES (2144,'GC','Carol','Philips'); INSERT INTO Person VALUES (2582,'IN','Gregory','Tang'); INSERT INTO Person VALUES (2012,'SC','Jian','Wang'); INSERT INTO Person VALUES (12624,'IN','Clayton','She'); INSERT INTO Person VALUES (12509,'IN','Madison','Russell'); GO
The easiest way to perform the transformation is to copy all the data and paste it over at ConvertCSV:
Another great tool for this task is SQLFiddle.
OPTIONAL: The insert statements will include the field names: if you want to make your code more concise, you can remove that part by selecting the column names with your mouse holding the ALT key and then delete the selection. Here’s a description of how the rectangular selection works in SSMS 2012 and 2014 (doesn’t work in SSMS 2008).
Expected output
The expected output should be something immediately readable and understandable. There’s another tool that can help you obtain it.
Go to https://ozh.github.io/ascii-tables/, paste your data in the “Input” textarea, press “Create Table” and grab your table from the “Output” textarea.Here’s what your output should look like:
+------------+-------------+ | PersonType | PersonCount | +------------+-------------+ | GC | 1 | | IN | 8 | | SC | 1 | +------------+-------------+
Show what you have tried
Everybody will be more willing to help you if you show that you have put some effort into solving your problem. If you have a query, include it, even if it doesn’t do exactly what you’re after.
Please please please, format your query before posting! You can format your queries online for free at PoorSQL.com
Simply paste your code then open the “Formatted SQL” tab to grab your code in a more readable way.
Putting it all together
Here is what your question should look like when everything is ok:
Hi all, I have a table called Person and I have to extract the number of rows for each person type.
This is the table script and some sample data:
USE [tempdb] GO CREATE TABLE [dbo].[Person]( [BusinessEntityID] [int] NOT NULL PRIMARY KEY CLUSTERED, [PersonType] [nchar](2) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL ) GO INSERT INTO Person VALUES (6106,'IN','Beth','Carlson'); INSERT INTO Person VALUES (17889,'IN','Dennis','Li'); INSERT INTO Person VALUES (17989,'IN','Brent','Li'); INSERT INTO Person VALUES (9424,'IN','Brad','Raji'); INSERT INTO Person VALUES (5842,'IN','Aimee','She'); INSERT INTO Person VALUES (2144,'GC','Carol','Philips'); INSERT INTO Person VALUES (2582,'IN','Gregory','Tang'); INSERT INTO Person VALUES (2012,'SC','Jian','Wang'); INSERT INTO Person VALUES (12624,'IN','Clayton','She'); INSERT INTO Person VALUES (12509,'IN','Madison','Russell');This is what I’m trying to obtain:
+------------+-------------+ | PersonType | PersonCount | +------------+-------------+ | GC | 1 | | IN | 8 | | SC | 1 | +------------+-------------+Here is what I have tried:
SELECT PersonType FROM PersonHow do I do that?
If you include this information in your posts, I promise you will get blazingly fast answers.
Posted on April 24, 2015, in SQL Server, SQL Server Central, T-SQL and tagged Forum Question, SQL Server, T-SQL. Bookmark the permalink. 15 Comments.
Bravo! Should be required reading before posting on SO or MSDN forums.
My bookmarks thank you!
Another great way to turn a set of query results into a set of create table plus insert scripts is to use the SSMS add-on tSQL-Flex available from GitHub https://github.com/nycdotnet/TSqlFlex . This does all the work for you. Just paste your query into the query area, hit and the script appears in the output pane, together with a convenient button to copy it to the clipboard. Simple! This add-on was described in SqlServerCentral – http://www.sqlservercentral.com/blogs/nycnet/2014/09/08/new-add-on-for-ssms-t-sql-flex
Thanks Tony! I had completely forgotten about TSqlFlex. It’s a great add-in.
Excellent Plugin
Great formatting tips especially for SO !, but the sensefulsolutions is not working now. I found an alternative solution here http://www.tablesgenerator.com/markdown_tables which is more intuitive.
Thank you! I didn’t notice the sensefulsolutions site went down. I will update the post to include some alternatives.
Hi,
Thanks for sharing the great information… Its useful and helpful information…Keep Sharing.
Thank you
Hari
Great fantastic post !!
Updates with good alternatives?
Thanks! I checked all the links and they seem to be working. Are you looking for something in particular?
Pingback: How to ask IT questions on the internet. | just dave info
Pingback: t sql – how to create trigger in SQL server using two tables to find stock quantity | Asking
Pingback: sql - La combinación de tablas de clave externa
Pingback: create-table - Postgresql Crear Tabla Fieldname marca de tiempo con la Zona horaria UTC
Pingback: case - La mejor Count(*) con una INSTRUCCIÓN CASE?