As the SQL query is dynamic How do I loop the output of this dynamic query.
Create a temp table outside of the dynamic query, and insert into it in the dynamic query. Then you can read from the temp table.
SET @SQL = N'
INSERT INTO #tempUser(userId,IsFirst,IsTemp,inactive,createddate)
SELECT userid, isfirst, istemp, inactive, createddate
FROM ' +QUOTENAME(@clientid)+'.USER.queen_user;';
But a better overall approach might be to create a partitioned view in a seperate database over all the tables. EG
create view queen_user
as
select 123 clientId, userid, isfirst, istemp, inactive
from Client123.USER.queen_user
union all
select 124 clientId, userid, isfirst, istemp, inactive
from Client124.USER.queen_user
union all
. . .
union all
select 999 clientId, userid, isfirst, istemp, inactive
from Client999.USER.queen_user
And have a procedure that alters it any time a new client db is added.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…