SELECT query with variable table names

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT query with variable table names

Author

Topic

Ambiance44
Starting Member

16 Posts

Posted - 2009-04-22 : 17:41:33

Hello everyone,

I have a database with 44 tables that have the same structure (2 columns: enum_id INT PK, value VARCHAR(255)).

I created two tables to consolidate the data in all the 44 tables, since they all have the same structure and they don't change much. The two tables I created are as follows:

1: enum_value_type (enum_value_type_id INT PK, description VARCHAR(255))
2: enum_value (enum_value_type_id INT FK, enum_id INT, value VARCHAR(255))

In table 1, I have typed out the names of 44 tables in the description field, and given each of them an ID. I would like to periodically reload all data from each of these 44 tables into the enum_value table.

Is it possible to create a query that will use the description field's value as the source table name and copy that source table's data into the enum_value table?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 18:06:44

Easiest to generate a script then copy that into an sp.
something like
select 'insert enum_value select ' + convert(varchar(20),enum_value_type_id) + ', enum_id, value from ' + description
from enum_value_type

if you want it to be dynamic
declare @s varchar(max)
select @s = @s + char(13) + char(10) + 'insert enum_value select ' + convert(varchar(20),enum_value_type_id) + ', enum_id, value from ' + description
from enum_value_type
exec (@s)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ambiance44
Starting Member

16 Posts

Posted - 2009-04-23 : 10:23:49

nr: You are a genius!! Thank you very much for your help. The first script works perfectly. I really appreciate it.

Ambiance44
Starting Member

16 Posts

Posted - 2009-04-23 : 11:22:33

I tweaked nr's query a little bit and created a stored procedure. Below is the SP with comments.

Thanks


-- Holds the present enum_value_type_id
DECLARE @id  int

-- Holds the present dynamically generated SQL query
DECLARE @sql VARCHAR(255)

-- Temporary table to hold the dynamically generated SQL queries
CREATE TABLE #commands (id INT, cmd VARCHAR(255))

-- Empty out the enum_value table
DELETE FROM enum_value

-- Generate SQL queries and store in temporary table
INSERT #commands 
SELECT convert(varchar(20),enum_value_type_id)
	, 'insert enum_value select ' + convert(varchar(20),enum_value_type_id) + ', id, value from ' + description 
FROM enum_value_type

-- Initially assign the minimum enum_value_type_id to @id
SELECT @id = MIN(id) FROM #commands

-- Iterate through all the SQL queries in the temporary table and execute them
WHILE @id IS NOT NULL
BEGIN
	SELECT @sql = cmd
	FROM #commands
	WHERE id = @id

	EXECUTE(@sql)
	SELECT @id = MIN(id) FROM #commands WHERE id > @id
END

-- Drop the temporary table
DROP TABLE #commands

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources