sql server insert copy rows multiple tables one field changing

Question

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.

What about `*`? 'SELECT 'new value' as department, * INTO myTable FROM myTable WHERE department = 'old value';' — cha, Feb 25 '14 at 23:24
This was where I started, but quckly found that it tries to put the deprtment field into the table twice and gets this error: S0021(2705)[Microsoft][ODBC SQL Server Driver][SQL Server]Column names in each table must be unique — Russted, Feb 26 '14 at 21:39

score 0 · Accepted Answer · answered Feb 26 '14 at 01:55

0

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.

answered Feb 26 '14 at 01:55

Namphibian

12,046
7
46
76

This seems really close to what I need, but unfortunately I'm not working through SSMS and so am working on something similar that can workin my interface - the key that you gave me though is how to use the information_schema.tables. I think I'll be able to make it work. – Russted Feb 26 '14 at 21:47
@Russted you can run this from any connection you dont need SSMS. sp_MSForeachTable is not bound to SSMS. You would be able to run this as a command and execute each string returned. – Namphibian Feb 26 '14 at 21:54

score 0 · Answer 2 · answered Feb 26 '14 at 02:27

This is other way you can do it:

Declare @table sysname = 'myTable', @field sysname = 'department', 
        @oldValue nvarchar(max) = 'old value', @newValue nvarchar(max) = 'new value'

Declare @colNames nvarchar(max)

Select @colNames = COALESCE(@colNames + ', ', '') + QUOTENAME(Name)
From syscolumns 
Where id = OBJECT_ID(@table)
And name <> @field
Order by colid

Print 'SELECT ''' + @newValue + ''' as ' + QUOTENAME(@field) + ', ' + @colNames +
' INTO ' + QUOTENAME(@table) +
' FROM ' + QUOTENAME(@table) +
' WHERE ' + QUOTENAME(@field) + ' = ''' + @oldValue + ''';'

Do not use syscolumn and systables anymore. Use INFORMATION_SCHEMA. See this http://stackoverflow.com/questions/4381765/information-schema-vs-sysobjects — Namphibian, Feb 26 '14 at 21:52

sql server insert copy rows multiple tables one field changing

2 Answers2