this query don't work
use Common
go
declare @.DateTable Varchar(8)
declare @.TableName Varchar (255)
declare @.Currency Varchar (3)
select substring (TABLE_NAME,1,8) as date FROM information_schema.tables where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'
select substring (TABLE_NAME,9,11) as Currency FROM information_schema.tables where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'
set @.DateTable = date
set @.Currency = Currency
SELECT @.TableName = TABLE_NAME
FROM information_schema.tables
where TABLE_NAME not like 'IIM%' and TABLE_NAME not like 'sys%'
INSERT INTO [Common].[dbo].[IIM_RM_EXP_EUR_TEMP]
([Sedol]
,[Date]
,[Currency]
,[Name]
,[Candidate]
,[Benchmark]
,[Min]
,[Max]
,[Initial]
,[Alpha]
,[Specific]
,[Total]
,[Price]
,[Beta]
,[Buy.Cost]
,[Sell.Cost]
,[Factor.1]
,[Factor.2]
,[Factor.3]
,[Factor.4]
,[Factor.5]
,[Factor.6]
,[Factor.7]
,[Factor.8]
,[Factor.9]
,[Factor.10]
,[Factor.11]
,[Factor.12]
,[Factor.13]
,[Factor.14]
,[Factor.15]
,[Factor.16]
,[Factor.17]
,[Factor.18]
,[Factor.19]
,[Factor.20]
,[Factor.21]
,[Factor.22]
,[Factor.23]
,[Factor.24]
,[Style.1]
,[Style.2]
,[Style.3]
,[Style.4]
,[Style.5]
,[Country.1]
,[Country.2]
,[Country.3]
,[Country.4]
,[Country.5]
,[Country.6]
,[Country.7]
,[Country.8]
,[Sector.1]
,[Sector.2]
,[Sector.3]
,[Sector.4]
,[Sector.5]
,[Sector.6]
,[Sector.7]
,[Sector.8]
,[Sector.9]
,[Sector.10]
,[Sector.11]
,[Sector.12]
,[Sector.13]
,[Sector.14]
,[Sector.15]
,[Sector.16]
,[Sector.17]
,[Sector.18]
,[Group.1]
,[Group.2]
,[Group.3]
,[Group.4]
,[Group.5]
,[Group.6]
,[Group.7]
,[Group.8]
,[Group.9]
,[Group.10]
)
SELECT [ID]
,'@.DateTable'
,'@.Currency'
,[Name]
,[Candidate]
,[Benchmark]
,cast ([Min] as real)
,cast ([Max] as real)
,cast ([Initial] as real)
,cast ([Alpha] as real)
,cast ([Specific]as real)
,cast ([Total] as real )
,cast ([Price] as real)
,cast([Beta]as real)
,cast([Buy#Cost]as int)
,cast([Sell#Cost]as int)
,cast([Factor#1]as real)
,cast([Factor#2]as real)
,cast([Factor#3]as real)
,cast([Factor#4]as real)
,cast([Factor#5]as real)
,cast([Factor#6]as real)
,cast([Factor#7]as real)
,cast([Factor#8]as real)
,cast([Factor#9]as real)
,cast([Factor#10]as real)
,cast([Factor#11]as real)
,cast([Factor#12]as real)
,cast([Factor#13]as real)
,cast([Factor#14]as real)
,cast([Factor#15]as real)
,cast([Factor#16]as real)
,cast([Factor#17]as real)
,cast([Factor#18]as real)
,cast([Factor#19]as real)
,cast([Factor#20]as real)
,cast([Factor#21]as real)
,cast([Factor#22]as real)
,cast([Factor#23]as real)
,cast([Factor#24]as real)
,cast([Style#1]as Char)
,cast([Style#2]as Char)
,cast([Style#3]as Char)
,cast([Style#4]as Char)
,cast([Style#5]as Char)
,cast([Country#1]as Char)
,cast([Country#2]as Char)
,cast([Country#3]as Char)
,cast([Country#4]as Char)
,cast([Country#5]as Char)
,cast([Country#6]as Char)
,cast([Country#7]as Char)
,cast([Country#8]as Char)
,cast([Sector#1]as Char)
,cast([Sector#2]as Char)
,cast([Sector#3]as Char)
,cast([Sector#4]as Char)
,cast([Sector#5]as Char)
,cast([Sector#6]as Char)
,cast([Sector#7]as Char)
,cast([Sector#8]as Char)
,cast([Sector#9]as Char)
,cast([Sector#10]as Char)
,cast([Sector#11]as Char)
,cast([Sector#12]as Char)
,cast([Sector#13]as Char)
,cast([Sector#14]as Char)
,cast([Sector#15]as Char)
,cast([Sector#16]as Char)
,cast([Sector#17]as Char)
,cast([Sector#18]as Char)
,cast([Group#1]as Char)
,cast([Group#2]as Char)
,cast([Group#3]as Char)
,cast([Group#4]as Char)
,cast([Group#5]as Char)
,cast([Group#6]as Char)
,cast([Group#7]as Char)
,cast([Group#8]as Char)
,cast([Group#9]as Char)
,cast([Group#10]as Char)
FROM [Common].[dbo].[@.TableName]
You can't do:
FROM [Common].[dbo].[@.TableName]
In order to do this, you'll need to dynamically create the insert statement and use EXEC
eg
Code Snippet
DECLARE @.TableName VARCHAR(100)
SET @.TableName = 'sys.objects'
EXEC ('SELECT * FROM ' + @.TableName)
