Use INFORMATION_SCHEMA.COLUMNS to help build your INSERT, SELECT or other query

Last month I posted on a 'pragmatic' way to use internal tables to help build query statements dealing with large tables.

However, as INFORMATION_SCHEMA is the "new, ANSI standard" way to go, I thought I'd show that it can be used in a similar manner.

Also, I've often read posts where it is alluded that INFORMATION_SCHEMA should be used over sysinternals such as sysobjects and syscolumns.

I recently read this post from SQLServerCentral.com that shows this assertion is false at this time.
http://www.sqlservercentral.com/Forums/Topic1272370-1526-1.aspx#bm1277421

In the case of listing column names, either way is OK.  However, you should be aware of when and why you choose to use either.  There are some risks involved in always relying on INFORMATION_SCHEMA as Gail Shaw points out.

So, in the interest of preventing this site from going stale, even as I try to get on top of my project pile, I offer this short post to show that you can indeed continue to use this technique with the newer INFORMATION_SCHEMA.COLUMNS view to get a list of table column names in order of their position in the table.

Try this comparison to help you get comfortable with using the new "IS" views.


use dba -- < pick your own database containing large tables

-- pick a large table and substitute its name for 'nat'.

-- THIS IS THE OLD WAY
select name from syscolumns where id in (select id from sysobjects where name = 'nat') order by colorder
-- ANSWER SET
/*EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data*/

-- THIS IS THE NEW WAY
-- ANSWER SET
select column_name from information_schema.columns where table_name = 'nat' order by ordinal_position
/*EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data*/

Personally, I'm starting to like the new way better as I don't have to reference the INFORMATION_SCHEMA.tables in order to pull the data from INFORMATION_SCHEMA.columns.
The old way I have to use sysobjects to get the table id for the syscolumns to use.

You can use the INFORMATION_SCHEMA views in a number of interesting ways to help you reduce the time it takes to work with larger objects in your databases.


The following views apply ...

INFORMATION_SCHEMA.CHECK_CONSTRAINTS
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
INFORMATION_SCHEMA.COLUMN_PRIVILEGES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
INFORMATION_SCHEMA.DOMAINS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
INFORMATION_SCHEMA.PARAMETERS
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA.ROUTINE_COLUMNS
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.TABLE_PRIVILEGES
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
INFORMATION_SCHEMA.VIEWS

Take time to become familiar with the information these views provide.  It can really help you increase your productivity writing T-SQL.

Use syscolumns to help build your INSERT, SELECT or other query

One thing I find tedious, especially when needing to write a long ETL script is creating the INSERT, SELECT or UPDATE statements for production tables with long rows.


Try this to speed things up.


USE <your databasename here>
SELECT '['+[name]+'], ' FROM syscolumns WHERE id IN (SELECT id FROM sysobjects WHERE name = 'order') ORDER BY [colorder];

(Of course you'll need to supply your own tablename to the statement above in place of 'order').

Results set should look something like this:

[Add-charges], [CC-Exp], [CC-Name], [CC-no], [Charge-cust], [Check-no], [Comm-code], 
[Comm-code1], [Comm-rate], [Comm-rate1], [div-code], [Credit-rejet], [Cust-no], 
[Cust-po], [Deposit1], [Extra-Chargeo__1], [Extra-Chargeo__2], [Extra-Chargeo__3], 
[Extra-Chargeo__4], [Extra-Chargeo__5], [Invoice-date], [Invoice-no], [Job-no], 
[Gross-amt], [Printed], [Order-code], [Order-date], [Order-no], [Back-order], [Pay-method], 
[Price-level], [Qty-open-ord], [Qty-orig-ord], [Qty-shipped], [quote-no], [Request-date], 
[Ship-address__1], [Ship-address__2], [ship-charge], [Ship-city], [Ship-country], [Ship-name], 
[Ship-no], [Ship-st], [Ship-zip], [Slspn-code], [Slspn-code1], [Tax-code], [Term-code], 
[Update-stat], [User-id], [Via-code], [Whs-code], [Memo__1], [Memo__2], [Tax-amt__1], 
[Tax-amt__2], [Tax-amt__3], [Taxable-amt__1], [Taxable-amt__2], [Taxable-amt__3], 
[Consig-PO], [ohu-count], [Deposit__1], [Deposit__2], [Deposit__3], [note__1], [note__2], 
[note__3], [note__4], [note__5], [disc], [org-date], [org-time], [pick-hold], [pick-printed], 
[opick-status], [hold-order], [ship-complete], [override-flag], [Order-status], [pick-stage], 
[ops-date], [stage-complete], [nb-labels], [alloc-qty], [alloc-value], [trans-weight], [Qty-on-pps], 
[last-pick], [priority], [field-1], [field-10], [field-2], [field-3], [field-4], [field-5], [field-6], [field-7], 
[field-8], [field-9], [add-taxable__1], [add-taxable__2], [add-taxable__3], [charge-freight-in], 
[charge-handle], [charge-freight-out], [sat-del], [Incent-code], [item-stat], [web-order], 
[hold-reason], [over-reason], [over-userid], [Extra-Charge__1], [Extra-Charge__2], 
[Extra-Charge__3], [Extra-Charge__4], [Extra-Charge__5], [Extra-Charge__6], 
[Extra-Charge__7], [Extra-Charge__8], [Extra-Charge__9], [Extra-Charge__10], 
[bottom-memo], [top-memo], [curr-code], [curr-factor], [cc-address], [cc-cvv], [cc-zip], 
[cc-duty], [cc-freight], [cc-tax], [cc-disc-amt], [cc-ship-cntry], [cc-frzip], [cc-pay-amt], 
[cc-shipzip], [cc-transdate], [cc-acthist], [CheckDate], [EmailId], [Last-Cntct], [last-cntct-phone],
[request-by], [adate], [atime], [QueueSequenceNumber], [cc-city], [cc-state], [cc-approval], 
[cc-approval-date], [RMA], [ApprovedValue], [CCTransactionType], [IsOpened], [OpenedUser], 
[InvoiceEmailIds], [location-no], [Drop-Ship], [HandlingCode], [HoldExtAmt1], [HoldExtAmt2], 
[HoldExtAmt3], [HoldExtAmt4], [HoldExtAmt5], [HoldExtAmt6], [HoldExtAmt7], [HoldExtAmt8], 
[HoldExtAmt9], [HoldExtAmt10], [lead-code], [price-pick], [creditcardreference], 
[PreAuthorization], [cc-cust-num], [lockuserid], [pounlockdate], [userlock], [DropShipVendor], 
[OrderReference], [WebOrderNo], 


Now it is a simple matter to trim the last comma from the answer set and copy it, prefix a copy with an 'INSERT INTO <target name>(' <paste here> and close with a ')' then on the next line type a 'SELECT' <paste here> then a 'FROM <source name>;' and you're done!

Using OUTPUT with SQL Server 2005 updates

One thing I wish I had on my 2005 servers that I have on my 2008 server is the MERGE command.  Since I don't I've settled on OUTPUT to come close.


Here is an example:

Run this script to provide the proof.

USE [DBA] -- <supply your own database sandbox name here>

DECLARE @update_id TABLE(id INT)

IF EXISTS(SELECT * FROM sys.[tables] AS t where type = 'u' and name = 'tbl_sales')

DROP TABLE [tbl_sales];

-- populate a table with data

SELECT 1 [id], 250.00[total sale], 'north'[region], 'original'[row operation] INTO [tbl_sales];

INSERT INTO [tbl_sales] SELECT 2, 350.00, 'south', 'original'

INSERT INTO [tbl_sales] SELECT 3, 50.00, 'east', 'original'

INSERT INTO [tbl_sales] SELECT 4, 475.00, 'west', 'original'

-- have a look a the 'data table'

SELECT *, 'initial table population complete'[msg] FROM tbl_sales ORDER BY [id];

IF EXISTS(SELECT * FROM sys.[tables] AS t where type = 'u' and name = 'stg_sales')

DROP TABLE [stg_sales];

-- populate a staging table

SELECT 1 [id], 350.00[total sale], 'north'[region], 'original'[row operation] INTO [stg_sales];

INSERT INTO [stg_sales] SELECT 3, 750.00, 'east', 'original'

INSERT INTO [stg_sales] SELECT 5, 500.00, 'east', 'original'

INSERT INTO [stg_sales] SELECT 6, 650.00, 'west', 'original'

-- have a look at your staging data

SELECT *, 'initial staging population complete'[msg] FROM stg_sales ORDER BY [id];

-- now update the table with staged data rows; some are for inserts (new); 

-- some for updates (changes to table from staged data)

BEGIN TRANSACTION

UPDATE [tbl_sales] WITH (ROWLOCK)

SET [total sale]=s.[total sale], [row operation]='updated'

OUTPUT INSERTED.[id] INTO @update_id

FROM [tbl_sales] AS t  

INNER JOIN [stg_sales] AS s ON t.[id]=s.[id]

;

INSERT INTO [tbl_sales]([id],[total sale],[region],[row operation])

SELECT * FROM [stg_sales] WHERE id NOT IN (SELECT id FROM @update_id)

;

COMMIT TRANSACTION

-- check the results

SELECT *, 'table update complete'[msg] FROM tbl_sales ORDER BY [id];

SELECT *, 'staging table rows'[msg]FROM stg_sales ORDER BY [id];

So what happened?

Using the OUTPUT clause, we capture the id of the rows that were updated from the staging table by virture of the INNER JOIN.  We saved those in @update_id table.

Then using the NOT IN CLAUSE in the SELECT statement portion of the INSERT operation following the update, we grabbed any rows in the staging table that did not have a matching id from the UPDATE operation (because they are new and don't exist in the table) and performed the insertion of the new rows.

In practice 'id' would be anything that uniquely identifies the data row - a customer_id, an order_number, an invoice_number, anything.  The data rows would of course be longer from production systems, but the concept is simple once mastered and is very useful for ETL script loads to data marts/data warehouse tables; or, a less structured operational data store.


Back in my Teradata Master days, we had an operation called UPSERT.  This is as close as it gets to UPSERT in SQL Server 2005, but I find it vastly simpler than just about any other approach I've used.