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!