August 20, 2008 at 6:43 am
Hello Everyone,
We would like to have a T-SQL script to purge some data based on several conditions (not trivial).
What is the most complicated is that we do not know all the colums that we will have to compare until runtime.
Information about these "hiden" columns is stored in another table : column_name, type, ... But could also be found in the INFORMATION_SCHEMA views.
Is it something that we can acomplish using T-SQL?
Best regards.
Carl
August 20, 2008 at 6:58 am
Carl B. (8/20/2008)
Hello Everyone,We would like to have a T-SQL script to purge some data based on several conditions (not trivial).
What is the most complicated is that we do not know all the colums that we will have to compare until runtime.
Information about these "hiden" columns is stored in another table : column_name, type, ... But could also be found in the INFORMATION_SCHEMA views.
Is it something that we can acomplish using T-SQL?
Best regards.
Carl
You could use dynamic SQL, e.g.:
declare @q nvarchar(4000)
set @q = 'select ' + '1'
execute(@q)
I.e. you can build up the query you want to execute, here you can use information form the INFORMATION_SCHEMA views, and then execute your new query.
Regards,
Andras
August 20, 2008 at 7:11 am
Thank's Andras,
Can I get back the result of the execution of @q and use it afterwards?
Best regards.
Carl
August 20, 2008 at 7:30 am
Carl B. (8/20/2008)
Thank's Andras,Can I get back the result of the execution of @q and use it afterwards?
Best regards.
Carl
You can do this too. One way of doing this is to create a temporary table
"create table #foo (column1 type1, column2 type2, ...)" and your query that you build up dynamically has a INSERT INTO #foo part, so it puts the output directly to the temporary table.
Regards,
Andras
August 20, 2008 at 7:35 am
Thank's a lot and have a nice Day Andras.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply