Good day,
I will start with some points regarding your current code (for future understanding) and I will move to a simple solution you can try.
1. You are using "ORDER BY CONVERT(date, RIGHT([name], 10)) DESC"
but there is no need to CONVERT the data into date in order to sort it, since your data is already in great format for sorting as string. You can directly sort strings that has the format yyyy-mm-dd, and you will get the data ordered by the date. The
format 2012-06-01 give you the option to sort it as string (as it is). This is one advantage using this format (CONVERT COST resources).
declare @T table (s char(10))
insert @T (s) values ('2011-06-01'),('2012-04-02'),('2012-03-01'),('2012-06-01'),('2012-08-01')
select s from @T order by s
GO
2. You are using "CONVERT(date, RIGHT([name], 10)) > = '2012-06-01'"
You convert the data to date in order to compare it to string. In practice what is done, is that you CONVERT the data into date like implicit CONVERT! This mean that the
displaying format of the date depend on your culture! In one data base you might get format like expected, while in another database the result might be in formated like dd/mm/yyyy. You should
never work like this.
Please read this article in depth: Date displaying format, vs Date storing format
http://ariely.info/Blog/tabid/83/EntryId/161/Date-displaying-format-vs-Date-storing-format.aspx
3. Moreover as I showed in point 1 you can sort as string and therefore
you can compare directly as string. There is no reason to CONVERT to DATE just in order to let the server CONVERT it back t string implicitly.
declare @T table (s char(10))
insert @T (s) values ('2011-06-01'),('2012-04-02'),('2012-03-01'),('2012-06-01'),('2012-08-01')
select * from @T where s >= '2012-06-01'
GO
4. If you want to create one long string and execute all as one insert statement you might get better result, then execute the INSERT for each table.
In this case you do not need any loop!
You have all the data in the the table @ttable so you can use simple "for xml" in order to create one string from all the rows with no looping! I mean one query for all your tables.
Please check this article regarding Unsplit Using For XML with group by columns.
In your case there is no need fro the grouping so it is simple issue
http://ariely.info/Blog/tabid/83/EntryId/152/Unsplit-Using-For-XML-with-group-by-columns.aspx
declare @T table (s char(10))
insert @T (s) values ('2011-06-01'),('2012-04-02'),('2012-03-01'),('2012-06-01'),('2012-08-01')
SELECT
STUFF(
(
SELECT '[add any starting text]' + CAST([s] AS VARCHAR(MAX)) + '[add any closing text @@]'
FROM @T
FOR XML PATH ('')
),1,2,''
) AS NameValues
GO
* this is probably your best option, but you should test everything.
5. There is built-in procedure sp_MSforeachtable, which you can use in order to get a fast and simple solution (behind the screen it does use loop so if you millions of tables the solution above might be faster, but at the same time the length of the string
for dynamic query is limited)
check out this example:
-- Print all tables in the database:
exec sp_MSforeachtable 'print ''?'''
-- Here we reproduce an issue like yours.
-- 1. I create new database
-- 2. I create several tables with names like date format
-- 3. I use sp_MSforeachtable with simple IF in order to execute a query only on these tables with name grater then 2012-04-06
create database db
GO
use db
Go
create table [data_naTest_2012-02-01] (id int)
create table [data_naTest_2012-03-06] (id int)
create table [data_naTest_2012-04-06] (id int)
create table [data_naTest_2012-05-05] (id int)
create table [data_naTest_2012-06-03] (id int)
create table [data_naTest_2012-07-02] (id int)
GO
-- list all tables with name gretar then 2012-04-06
EXECUTE sp_MSforeachtable '
print ''?'';
print LEFT(RIGHT(''?'',11),10);
if LEFT(RIGHT(''?'',11),10) >= ''2012-04-06'' BEGIN
select ''?''
END
'
GO
You solution need to be something like (I could not test it without your tables but it should work as above example):
-- your solution should be something like:
EXECUTE sp_MSforeachtable '
if LEFT(RIGHT(''?'',11),10) >= ''2012-04-06'' BEGIN
INSERT INTO Sample_DB.dbo.TEST_JOIN
(ID, USER_ID, Date, Time, Name, Revenue, Count, Exps,
ExpDate, ExpTime, Advertiser_ID, Advertiser_Name,
ID, PID, CID, Name, AID, AName, CrID, CrName,
DirectoryID, DirectoryName, ExpNo, [Type], [Strategy],
[Group], Size_ID, [Description],
[Cost], [TotalCost], SName, SiName, [Source_Table],
[Source_Table_Name])
SELECT DISTINCT X.ID, X.USER_ID, X.Date, X.Time,
X.Name, X.Revenue, X.Count, 0 AS Exps,
CONVERT(date, XX.Time) AS Date, XX.Time AS Time, XX.AID,
CASE WHEN XX.AID = 123 THEN ''France'' ELSE ''Germany'' END AS AName,
XX.SID AS SiteID,
2 AS [Source_Table], '' + ? + '',
FROM SAMPLE_1.dbo.First_Table X
INNER JOIN [?] XX
ON X.USER_ID = cast(XX.USER_ID as varchar(255))
WHERE XX.USER_ID <> ''0'' AND
XX.Time <= ISNULL(X.Time, X.ATime)
AND XX.Time >= DATEADD(day, -30, X.ATime)
AND XX.Time > ISNULL((SELECT MAX(X1.Time)
FROM SAMPLE_1.dbo.First_Table X1
WHERE X1.USER_ID = X.USER_ID AND X1.AID < X.AID
AND ISNULL(X.Time, X.Time) <> ISNULL(X1.Time, X1.Time)), ''1-1-1900'')
ORDER BY ATime, AID, Time
END'
I hope this answer your needs :-)
Other recent topics
