从DynamicNamed表中选择基于静态表的数据 [英] Select from DynamicNamed Table which is Based on Static Table's Data
问题描述
Hii EveryOne，
再一次，我需要你们所有人的帮助..
我在SQL数据库中有未知数量的动态表
就像下面一样，
Hii EveryOne,
Once more I need help from all of u..
I have Unknown numbers of Dynamic Tables in SQL DataBase
Just like Below,
=>User_Master
UserCode UserName
aaa AAA
bbb BBB
=>aaa_EmailInfo
UserCode EmailId
aaa aaa@gmail.com
aaa aaa1@gmail.com
=>bbb_EmailInfo
UserCode EmailId
bbb bbb@gmail.com
bbb bbb1@gmail.com
bbb bbb2@gmail.com
Declare @CheckEmail VARCHAR(500) = 'aaa@gmail.com'
SELECT
User_Master.UserCode,
(SELECT User_Master.UserCode+'_EmailInfo'.EmailId FROM User_Master.UserCode+'_EmailInfo') EmailId
FROM User_Master
这里，
User_Master是静态表，
但aaa_EmailInfo和bbb_EmailInfo是动态生成的，名称为UsedCode ..
现在我想检查@CheckEmail是否存在于动态表中？不是??
怎么可能？
任何人都可以建议我SELECT查询???
Here,
User_Master is Static Table,
But aaa_EmailInfo and bbb_EmailInfo are Dynamically generated with Named on UsedCode..
Now i want to Check that @CheckEmail is Exist in Dynamic Tables on Not??
How is it Possible??
Can anyone Suggest me SELECT Query ???
推荐答案
从内存写入，但它有点像这个：
Writing from memory, but it goes something like this:
SELECT *
FROM sys.tables WHERE name like '%_EmailInfo'
-- or any other search condition
DECLARE @email_tables table (id int IDENTITY (1,1), table_name varchar(255))
INSERT INTO @email_tables( table_name)
SELECT objectname FROM sys.tables WHERE name like '%_EmailInfo'
DECLARE @i int
SET @i = 0
DECLARE @sql_query nvarchar(MAX)
DECLARE @table_name varchar(255)
DECLARE @exists int
WHILE (SELECT TOP 1 FROM @email_tables WHERE id > @i ORDER BY id)
BEGIN
SELECT TOP 1 @i = id, @table_name = table_name FROM @email_tables ORDER BY id)
SET @sql_query = 'SELECT COUNT(*) FROM ' + @table_name + ' WHERE emailid = ''' + @CheckEmail + '''
EXEC @exists = sp_Execute @sql_query (you'll have to check exact syntax here )
END
如果这有帮助请花点时间接受解决方案。谢谢。
If this helps please take time to accept the solution. Thank you.
这篇关于从DynamicNamed表中选择基于静态表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
