Hello all, i have dynamic sql code as below, it has an insert into a table part (INTO #CHECK_PERC), but it will not run that DDL. Does dynamic SQL not run DDL statements?
Also, note that i it runs fine as a 'normal' select statement when taking out the "..INTO.." part that makes it DDL.
DECLARE
@COL VARCHAR(500) =
'FL_Mature'
,@SQLCMD VARCHAR(MAX)
SELECT @SQLCMD = 'SELECT ' + @COL + ', COUNT(*) AS CNT_TOT INTO #CHECK_PERC FROM DIM_PROJECT GROUP BY ' + @COL
EXECUTE(@SQLCMD)
Hello,
So what errors are you seeing? Always test dynamic query
PRINT (@SQLCMD)
The code should run perfectly ... and the temp table will then disappear when you exit the dynamic code, since it runs in its own session.
You could create a table in the outer code, prior to the dynamic, sql, then do an INSERT INTO rather than a SELECT INTO in the dynamic code.
Sorry, yes, did test it, just left out the print command..I should have mentioned that the code runs fine, its just that when i try to open the temp table is when i get the error. Below is the error and and below that the full code i selected to run all at once..
===================================================
from messages after running the below code: (from the print command)
SELECT FL_Mature, COUNT(*) AS CNT_TOT INTO #CHECK_PERC FROM DIM_PROJECT GROUP BY FL_Mature
The code:
DECLARE
@COL VARCHAR(500) =
'FL_Mature'
,@SQLCMD VARCHAR(MAX)
SELECT @SQLCMD = 'SELECT ' + @COL + ', COUNT(*) AS CNT_TOT INTO #CHECK_PERC FROM DIM_PROJECT GROUP BY ' + @COL
PRINT(@SQLCMD)
EXECUTE(@SQLCMD)
SELECT * FROM #CHECK_PERC
create table #CHECK_PERC(FL_Mature varchar(50), CNT_TOT int)
INSERT INTO #CHECK_PERC
EXECUTE(@SQLCMD)
SELECT * FROM #CHECK_PERC
It doesn't have to be a global temp table, a regular temp table will do:
create table #t1 ( col1 int )
exec('insert into #t1 values(1),(2)')
select * from #t1hi
please google search ,, with the error ..
Lots of articles , Videos will come
They will talk about different kind of solutions
You will have to find the one that works for you !!!
For Example ..
Many thanks to everyone for their input, especially the links, that explained exactly what i need to do..
Note: I realize i could construct this without the dynamic SQL, but am just getting used to this method, so am doing it more for practice/theory than for practical usage..
Essentially i am running through all columns in a table (there are many of them) to see what values (if any ) are in the columns and then do a total and percentage of each value in the column in the table.
I am doing one column at a time and so wanted something to auto execute based on the variable of the column name. That would create a temp table with totals and percents. The grand total for the percents would be fixed (that is the total number of records in the table) but the values in the column could possible differ. So, there might be 1000 total records, 100 of which are NULL, the other 900 are "Y". The dynamic sql would run based on the column and its values and spit out totals of 100 and 900 and 10% and 90% in the row columns designated as totals and percents respectively..
In any case, it sounds like i need to put all that into one @SQLCMD, which i did for a quick test and it worked perfectly.
Thanks again everyone for your valuable input! This will go far for me when i need to use it where it is not practical or feasible to do some other way..
might want to leverage sys objects.
select t.name, c.name
from sys.tables t
join sys.columns c on t.object_id = c.object_id
you can even use these sys objects to find the data type of the column etc to do some summation only on money, int or decimal column types
Why are you doing one column at a time? That will cause separate scans of the table (in most cases) for every column.
Because, there's so few records (3096) total in the table..As I mentioned before, i'm doing each one to get a look at the values in their to see when the values stop in time, if they indeed stop, or if there's all nulls or a variety of values and how much of each values (% wise) contributes to the total records. This is an adhoc thing, not a permanent report and so was looking to make things a little easier and teach myself something about dynamic SQL while i was at it..cheers!