DYNAMIC TABLE CREATION

CREATE TABLE LISTID (ID INT NOT NULL)

GO

INSERT INTO LISTID  VALUES (111)

INSERT INTO LISTID  VALUES (112)

INSERT INTO LISTID  VALUES (113)

INSERT INTO LISTID  VALUES (114)

INSERT INTO LISTID  VALUES (115)

GO

DECLARE @LISTID TABLE (ID INT NOT NULL)

INSERT INTO @LISTID (ID)

SELECT DISTINCT (ID) FROM LISTID

DECLARE @LISTTALE TABLE (NAME VARCHAR(100) NOT NULL)

INSERT INTO @LISTTALE (NAME)

SELECT 'SELECT * FROM LIST' + CAST(ID AS VARCHAR(100))  FROM @LISTID

DECLARE @LISTSTR VARCHAR(8000);

SET @LISTSTR = ''SELECT @LISTSTR =  @LISTSTR + NAME + ' UNION ALL '

FROM @LISTTALE; SELECT SUBSTRING(@LISTSTR , 1, LEN(@LISTSTR)-9)

GO

DYNAMIC TABLE CREATION:

DECLARE @SQLSTRING NVARCHAR(MAX);                          

SET @SQLSTRING = '

DECLARE @LISTID TABLE (ID INT NOT NULL)

INSERT INTO @LISTID (ID)

SELECT DISTINCT (ID) FROM LISTID

DECLARE @LISTTALE TABLE (NAME VARCHAR(100) NOT NULL)

INSERT INTO @LISTTALE (NAME)

SELECT ''SELECT * FROM LIST'' + CAST(ID AS VARCHAR(100))  FROM @LISTID

DECLARE @LISTSTR VARCHAR(8000);

SET @LISTSTR = ''''SELECT @LISTSTR =  @LISTSTR + NAME + '' UNION ALL ''

FROM @LISTTALE; SELECT SUBSTRING(@LISTSTR , 1, LEN(@LISTSTR)-9)'  --- -9 IS TO REMOVE UNION ALL

PRINT @SQLSTRING

EXEC (@SQLSTRING)