Using OUTPUT to set up foreign keys' data

So, I was looking at a process that inserts records into table A (one at a time), then uses SCOPE_IDENTITY() from each insert to get the ID and uses that for the insert into table B, in order to provide a cross-reference tabled (foreign key).  

Obviously, there's a better way - OUTPUT, usable since SQL Server 2005.

An example.  We populate “blah”, then use the values from that to insert into “blah2”, with a foreign key ID of the first table.  

CREATE TABLE blah (id INT IDENTITY, logon_name VARCHAR(50))

CREATE TABLE blah2 (id INT IDENTITY, blahID int, dn VARCHAR(200))

INSERT INTO blah

      SELECT TOP 10 name FROM sysusers

INSERT INTO blah

      SELECT TOP 10 name FROM sysusers

DELETE FROM blah

--incrementing to show different ID values.

DECLARE @insertedlist TABLE (id int, the_logonname VARCHAR(50))

INSERT INTO blah

output inserted.id, inserted.logon_name INTO @insertedlist

SELECT TOP 10 name

FROM sysusers ORDER BY createdate

INSERT INTO blah2 (blahid, dn)

SELECT insertedlist.id, sysusers.uid

FROM @insertedlist insertedlist INNER JOIN sysusers

ON insertedlist.the_logonname = sysusers.name

SELECT * FROM blah

SELECT * FROM blah2

[Free Space] finding what filegroups your data is saved onto.

Using this to tell me where my indexes are, but more importantly - where my data is. What filegroup/file. It produces multiple rows when there are multiple files for a filegroup; need to code a better way. Note that all the WHERE clauses are optional; we couldn't figure out where our data was until we dropped them. 10 gig in a heap table, and 20gb in a service broker table.

SELECT

o.name AS Table_Name ,

i.NAME AS Index_Name ,

CASE i.type

WHEN 0 THEN 'Heap'

WHEN 1 THEN 'C'

WHEN 2 THEN 'NC'

ELSE '?' END AS [Type],

p.rows AS [#Records] ,

a.total_pages * 8 / 1024 AS [Reserved(mb)] ,

a.used_pages * 8 / 1024 AS [Used(mb)] ,

s.user_seeks ,

s.user_scans ,

s.user_lookups,

fg.name,

f.name,

f.physical_name

FROM sys.indexes AS i

INNER JOIN sys.partitions AS p ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID

INNER JOIN sys.allocation_units AS a ON ( a.type = 2

AND p.partition_id = a.container_id

)

OR ( ( a.type = 1

OR a.type = 3

)

AND p.hobt_id = a.container_id

)

INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S ON S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = DB_ID(DB_NAME())

AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!

LEFT OUTER JOIN sys.database_files f ON f.data_space_id = a.data_space_id

LEFT OUTER JOIN sys.filegroups fg ON fg.data_space_id = a.data_space_id

--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100

WHERE OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1

--AND i.TYPE_DESC <> 'HEAP'

AND i.type <> 1 -- clustered index

ORDER BY o.NAME ,

i.name

[Index] Size, usage, and location of your indexes

Updated my old code, since we were trying to figure out what indexes needed to get moved to the secondary (index) filegroup. You can even filter based off the usage (commented out below)
Standard disclaimer applies. Select the contents of the post, then copy/paste.

Note that there can be dupes - this is by design.  If a table spans multiple files, you'll see multiple rows returned with everything identical except for the filename.

SELECT
        o.name AS Table_Name ,
        i.NAME AS Index_Name ,
--        i.type_desc,
  CASE i.type
   WHEN 0 THEN 'Heap'
   WHEN 1 THEN 'C'
   WHEN 2 THEN 'NC'
   ELSE '?' END AS [Type],

   p.partition_number,

   p.rows AS [#Records] ,
        a.total_pages * 8 / 1024 AS [Reserved(mb)] ,
        a.used_pages * 8 / 1024 AS [Used(mb)] ,
        s.user_seeks ,
        s.user_scans ,
        s.user_lookups,
        fg.name, 
        f.name,
        f.physical_name
FROM    sys.indexes AS i
        INNER JOIN sys.partitions AS p 
   ON i.object_id = p.object_id
            AND i.index_id = p.index_id
        INNER JOIN SYS.OBJECTS O 
   ON I.OBJECT_ID = O.OBJECT_ID
        INNER JOIN sys.allocation_units AS a 
   ON ( a.type = 2
    AND p.partition_id = a.container_id
               )
   OR 
    ( ( a.type = 1 OR a.type = 3)
    AND p.hobt_id = a.container_id)
        INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S 
   ON S.OBJECT_ID = I.OBJECT_ID
   AND I.INDEX_ID = S.INDEX_ID
   AND DATABASE_ID = DB_ID(DB_NAME())
   AND o.type_desc NOT IN ( 'SYSTEM_TABLE', 'INTERNAL_TABLE' ) -- No system tables!
  LEFT OUTER JOIN sys.database_files f 
   ON f.data_space_id = a.data_space_id
  LEFT OUTER JOIN sys.filegroups fg 
   ON fg.data_space_id = a.data_space_id
WHERE   OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
        AND i.TYPE_DESC <> 'HEAP'
        AND i.type <> 1 -- clustered index
--AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100 

ORDER BY o.NAME ,
        i.name