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?
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.