T-SQL Tips and Solutions, Part 1

When writing T-SQL code, I find there are certain routines I do over and over again. Like, inserting data into a table, creating temp tables, or searching tables for columns needed in a Join statement. At times like these, it’s good to use T-SQL tips to make my job easier. The small, simple ones are pretty easy, like inserting data into two or three columns of a table structure that allows all columns to be NULL. In these cases I usually just manually write an insert statement like this one:

 -- Create the Table
 CREATE TABLE A1
 (
   Column1   [INT] IDENTITY(1,1) NOT NULL
  ,Column2   [Varchar](100)      NULL
  ,Column3   [Varchar](100)      NULL
  ,Column4   [Varchar](100)      NULL
  ,Column5   [Varchar](100)      NULL
  ,Column6   [Varchar](100)      NULL
  ,Column7   [Varchar](100)      NULL
  ,Column8   [Varchar](100)      NULL
  ,Column9   [Varchar](100)      NULL
  ,Column10  [Varchar](100)      NULL
  ,Column11  [Varchar](100)      NULL
  ,Column12  [Varchar](100)     NULL
  ,Column13  [Varchar](100)     NULL
  ,Column14  [Varchar](100)     NULL
  ,Column15  [Varchar](100)     NULL
 );
  
  
 --Insert the data
 INSERT A1
 ([Column2], [Column3])
 SELECT 'Column2 data row 1', 'Column3 data row 1'
 UNION
 SELECT 'Column2 data row 2', 'Column3 data row 2'
 UNION
 SELECT 'Column2 data row 3', 'Column3 data row 3';
  
  
 --Select the data as a result set
 SELECT * FROM A1
  
  
 --Drop the table A1
 DROP TABLE A1; 

And the result set is shown below:

No, the union select to get multiple rows in a single statement is NOT the tip. This is the easy way of inserting into a table where you only have to define two or three columns. But what if we have a different scenario? What if we imported a table exactly like A1 from Server SQLServer01 and we need to copy all the data from the source where column13 = ‘Important’? 

Now we have to create the table again. Set Identity_Insert on for A1, write out the 15 columns in the Insert list, and then copy those columns or write them out again in the select statement. At this point, I like to use a variable that will concatenate the columns for me. I select it out, and then copy and paste the column list instead of typing it out. This comes in handy if you are inserting or creating a number of tables in a stored proc development effort).

First we will create our table again:

USE SSG;
 GO
 -- Create the Table
 CREATE TABLE A1
 (
   Column1   [INT] IDENTITY(1,1) NOT NULL
  ,Column2   Varchar      NULL
  ,Column3   Varchar      NULL
  ,Column4   Varchar      NULL
  ,Column5   Varchar      NULL
  ,Column6   Varchar      NULL
  ,Column7   Varchar      NULL
  ,Column8   Varchar      NULL
  ,Column9   Varchar      NULL
  ,Column10  Varchar      NULL
  ,Column11  Varchar      NULL
  ,Column12  Varchar     NULL
  ,Column13  Varchar     NULL
  ,Column14  Varchar     NULL
  ,Column15  Varchar     NULL
 );

We can look at the table definition by looking at the INFORMATION_SCHEMA.COLUMNS table like so:

SELECT *
   FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'A1';

Next we will declare a variable, initialize it as blank in the declare statement, and then use the variable to select from the INFORMATION_SCHEMA.COLUMNS table as shown below:

DECLARE @v_ColumnList  VARCHAR = '';
 SELECT @v_ColumnList = @v_ColumnList + '[' + COLUMN_NAME + '], '
     FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'A1'
  ORDER BY [ORDINAL_POSITION];
 SELECT @v_ColumnList;

Now I copy the result set and paste it into my insert list and my select criteria after I remove the last comma. In this solution we imported the A1 table into SQLServer02 from SQLServer01 and named the imported table A1_From_SS01. The tip solution looks like:

USE SSG;
 GO
 -- Create the Table
 CREATE TABLE A1
 (
   Column1   [INT] IDENTITY(1,1) NOT NULL
  ,Column2   Varchar      NULL
  ,Column3   Varchar      NULL
  ,Column4   Varchar      NULL
  ,Column5   Varchar      NULL
  ,Column6   Varchar      NULL
  ,Column7   Varchar      NULL
  ,Column8   Varchar      NULL
  ,Column9   Varchar      NULL
  ,Column10  Varchar      NULL
  ,Column11  Varchar      NULL
  ,Column12  Varchar     NULL
  ,Column13  Varchar     NULL
  ,Column14  Varchar     NULL
  ,Column15  Varchar     NULL
 );
 -- Get the column list so you do not have to write it out
 DECLARE @v_ColumnList  VARCHAR = '';
 SELECT @v_ColumnList = @v_ColumnList + '[' + COLUMN_NAME + '], '
     FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'A1'
  ORDER BY [ORDINAL_POSITION];
 --Select the data as a result set
 SELECT * FROM A1
 SET IDENTITY_INSERT A1 ON
 --Insert the data
 INSERT A1
 (      [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10], [Column11], [Column12], [Column13], [Column14], [Column15])
 SELECT [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10], [Column11], [Column12], [Column13], [Column14], [Column15]
   FROM A1_From_SS01
  WHERE [Column13] = 'Important';

Summary

In review, the T-SQL tip is the concatenation of the table structure within a select statement that is then just printed or selected from the variable. This in turn is copied and pasted where you need the Column list in your code. This has saved me countless time and illuminated frustration just by eliminating a simple mundane task. We’ll share more T-SQL tips again soon.