August 7, 2017 at 2:57 am
Hi,
i want to write the max(Orderdate) from all Tables in DB into a #TempTable.
How can i realise this in a easy way ? (All Tables in the DB have 1 Col. with Orderdate).
CREATE TABLE #TempTbl(
[tblName] [nvarchar](50) NULL,
[lastOrderDate] [datetime] NULL)
Thanks
Regards
Nicole :rolleyes:
August 7, 2017 at 3:15 am
The only way I can really see this, unfortunately, is by using a Cursor. This isn't quick, and thus I'd strongly recommend Changing your set up (I'm not sure that a table with just a date column is really going to tell you much on it's own).
This should work, but without sample data, is untested. If you have questions about how to improve your database set up though, then please ask away.USE [YourDatabase];
GO
DECLARE @SchemaName varchar(250), @TableName varchar(250), @ColumnName varchar(250), @SQL varchar(MAX);
CREATE TABLE #LatestOrders (TableName varchar(250), MaxOrderDate date);
DECLARE LatestOrder_cursor CURSOR FOR
SELECT s.name, t.name, c.name
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.systypes st on c.system_type_id = st.xtype
WHERE t.type = 'U'
AND st.name IN ('date') --Put the data type of your column here
AND c.name = 'Orderdate';
OPEN LatestOrder_cursor;
FETCH NEXT FROM LatestOrder_cursor
INTO @SchemaName, @TableName, @ColumnName;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'Getting latest date for [' + @SchemaName + '].[' + @TableName + '].[' + @ColumnName + ']';
SELECT @SQL = 'INSERT INTO #LatestOrders (TableName, MAxOrderDate)' + CHAR(10) +
'SELECT @TableName, MAX([' + @ColumnName + '])' + CHAR(10) +
'FROM [' + @SchemaName + '].[' + @TableName + '];';
EXEC (@SQL);
FETCH NEXT FROM LatestOrder_cursor
INTO @SchemaName, @TableName, @ColumnName;
END
CLOSE LatestOrder_cursor;
DEALLOCATE LatestOrder_cursor;
SELECT *
FROM #LatestOrders;
DROP TABLE #LatestOrders;
GO
Edit: I suppose an alternative would be to create a Dynamic SQL statement that uses UNION ALL, getting all the table names and columns from the sys schema. Didn't think of that until after I wrote a CURSOR (Come shame me Jeff!!! I dare you 😉 ). Sure someone else will give that a go? If not, I'll write something up when I get a chance later.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 7, 2017 at 3:32 am
Nevermind, what I was working on suddenly wasn't wanted anymore just after I made the above post. Love it when that happens :D.
Again, this is untested :USE [YourDatabase];
GO
DECLARE @SQL varchar(MAX);
CREATE TABLE #LatestOrders (TableName varchar(250), MaxOrderDate date);
SELECT @SQL =
'INSERT INTO #LatestOrders' + CHAR(10) +
STUFF((SELECT 'UNION ALL' + CHAR(10) +
'SELECT ''' + t.name + ''' AS TableName, MAX(OrderDate) AS MaxOrderDate' + CHAR(10) +
'FROM [' + s.name + '].[' + t.name + ']' + CHAR(10)
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.systypes st on c.system_type_id = st.xtype
WHERE t.type = 'U'
AND st.name IN ('int') --Put the data type of your column here
AND c.name = 'OrderDate'
AND t.name NOT LIKE '%History'
FOR XML PATH ('')), 1 ,10,'') + ';';
PRINT @SQL;
EXEC (@SQL);
SELECT *
FROM #LatestOrders;
DROP TABLE #LatestOrders;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 7, 2017 at 6:09 am
EXEC sp_MSforeachtable 'INSERT #TempTbl ([tblName],[lastOrderDate]) SELECT ''?'',MAX(Orderdate) FROM ?';
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply