I'm not sure of the best way to ask this, so it will be through example.

I have a good size database that contains about 800 tables each containing a key piece of information for all data - For the example we'll call it Department. This is part of the unique index for these tables. I am in need of a process to go through each of these tables and copy all rows currently in one department into a new set of rows for a newly added department.

Now, I think I can do this with a single table simple enough with something like:

SELECT 'new value' as department, field2, field3, field4, field5
INTO myTable
FROM myTable
WHERE department = 'old value';

However, I am trying to take find a lazy way out and not have to list all fields for the tables (some tables have a large list of fields). I would hope to find a simple statement that could be used on every table and yield the same result, without having to list all fields (only the one being changed in the new rows). Basically something simple like my first statement - except not having to go pull all of the thousands of field names across the tables:

SELECT 'new value' as department, (magically get all other fields for the specified row in the table)
INTO myTable
FROM myTable
WHERE department = 'old value';

Has anyone ever had to do anything like this? Am I stuck having to manually create a list of all the fields for each of the 800 tables?

-- thanks.

Use sp_MSforEachtable and the INFORMATIONSCHEMA tables together to create a query like:

EXEC sp_MSforeachtable  'DECLARE @Chars VARCHAR(MAX)

SELECT @Chars = ISNULL(@Chars + '', '' +COLUMN_NAME, COLUMN_NAME)
FROM INFORMATION_SCHEMA.TABLES TAB
INNER JOIN INFORMATION_SCHEMA.COLUMNS COL
ON TAB.TABLE_NAME = COL.TABLE_NAME
AND TAB.TABLE_SCHEMA = COL.TABLE_SCHEMA
WHERE TAB.TABLE_NAME =PARSENAME(''?'',1)

SELECT ''SELECT ''''newvalue'''' as department, ''+@Chars+''  INTO ? FROM ? WHERE department =''''old_value'''''''

Switch your results to text output in SSMS and run the query. Viola.

more