Still … there is a trick here, and I don’t see a lot of people using it. Maybe it’s just me, maybe I’m lazier than the average dev, but I often find myself using the Script Table As menu and choosing SELECT To and Clipboard. This creates a nice select statement with all my fields wrapped in hard brackets. I can then copy this into an INSERT query I might be working on to save myself some typing. I can quickly copy the field list from the Query ‘Script Table As’ gives me and use it in the top of my INSERT query, then I can copy the entire SELECT query into the bottom of my INSERT query and Bob’s yer Uncle, I’ve got a simple INSERT query ready to go. Note: This is most useful when I’m trying to create a new table based on an existing table with only minor changes to field names. I use this frequently when I’m establishing a reporting database based on staging tables.
That’s three ways to do it in Management Studio; the next step in the process is using SMO to script using a .NET language (C#, F#, Powershell).