DML Usage in SQL 2008 1

[VIJSQL01]

In this thread, i need to discuss about DML commands usage in SQL 2008.

 

[VIJSQL01]

Wow! ... Thank you very much. It solved my problem. This completes my requirement. This way you made me to learn PIVOT concept by giving solution step by step.

I am so happy .

 

[VIJSQL01]

hi Markros,

First let me tell you what exactly i am gonna do with above query. The output which i am getting out of that query(query you gave me the solution) has to be given as .XLS output. Untill now, what i did was, i was using straight forward query in view and i used to call view from BCP to get the .XLS output. Now this query i put it in SP and i am facing the difficulty in executing the SP through BCP.
I tried in following many ways...

1st try:
a. Created Procedure..

Create Procedure State_12MC_SFC_Proc

As Begin

declare @SQL nvarchar(max), @Cols nvarchar(max)

set @Cols = stuff((select ', ' + quotename(Period_no)
from (select distinct Period_no from REAS_HPI_State_Final where Period_no>=193) X
order by Period_no desc for XML PATH('')),1,2,'')
set @SQL = 'Select DISTINCT R.STATE_NAME, pvt.* from
(select state_code,Period_no, HPI_12M_Change*100 as [Percents] from REAS_HPI_STATE_Final where tier_code in(0)
) src
PIVOT
( max([Percents]) For Period_no IN( '+@Cols +')) as Pvt
INNER JOIN REAS_STATE_BE r
ON pvt.STATE_CODE = R.STATE_Code'

execute (@SQL)
END;

BCP Execution:

exec master..xp_cmdshell 'bcp "EXEC REAS.dbo.State_12MC_SFC_Proc" queryout "\\10.48.164.48\datamove1\vijay\QA MKTG Source Files\State_12MC_SFC_1.xls" -U"hpi" -P"hpi" -T -c'

Error I am getting:

output
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]BCP host-files must contain at least one column
NULL

But we have State Name, STate Code and Period as the columns in the query of SP. I am confused what may be the problem with.

 

[markros]

Try adding

select * into #Temp ....

and add BCP directly to the SP and do

bcp "select * from #Temp" ...

Alternatively you can create a permanent table the same way...

PluralSight Learning Library

 

[VIJSQL01]

hi Markros,

Thanks again for the reply. In this case i can not create a permanent table since the result set i am getting are dynamic every month when i execute this query.You might have observed i have put the period number as greater than 193. Here 193 is constant and new period would be added every month. If you consider this month October as an example, i have period like from 193 to 262. And it becomes 193 to 263 in the next month run. Hence the columns in the result set are dynamic.
I am facing difficulty in creating temparory table since the columns are dynamic.

also please let me know how to insert SP result set to the temporary table...

Thanks,
VIJSQL01

 

[markros]

Create Procedure State_12MC_SFC_Proc

As Begin

declare @SQL nvarchar(max), @Cols nvarchar(max)

set @Cols = stuff((select ', ' + quotename(Period_no)
from (select distinct Period_no from REAS_HPI_State_Final where Period_no>=193) X
order by Period_no desc for XML PATH('')),1,2,'')
set @SQL = 'Select DISTINCT R.STATE_NAME, pvt.* 
INTO #Temp
from
(select state_code,Period_no, HPI_12M_Change*100 as [Percents] from REAS_HPI_STATE_Final where tier_code in(0) 
) src
PIVOT
( max([Percents]) For Period_no IN( '+@Cols +')) as Pvt 
INNER JOIN REAS_STATE_BE r
ON pvt.STATE_CODE = R.STATE_Code'

execute (@SQL)
END;

I add into #Temp into the dynamic PIVOT to create a table on the fly.


PluralSight Learning Library

 

[VIJSQL01]

MArkros,

I did execute the query you provided. Unfortunately #Temp table is not created. When i try to query this table, its saying 'Invalid Object name #Temp'.

Error i am getting,

Msg 208, Level 16, State 0, Line 1
Invalid object name '#Temp'

Sorry for bothering you again and again.

Thanks,
Vijay

 

[markros]

Try using a permanent table, e.g. instead of #Temp try
insert into Results

PluralSight Learning Library

 

[VIJSQL01]

Sorry, i am unable to create permanent table since results are dynamic every month. I am not sure why the temp table is not created when we put INTO #Temp statement over there.

 

[markros]

At the beginning of the SP add

if object_id('Results','U') is not null drop table Results

and use

select .. into Results in dynamic SQL.

It should work, I don't see why not. Are you getting an error?

PluralSight Learning Library

 

[VIJSQL01]

i added the above statement in SP and SP is altered successfully.

Here you go for SP altered....

Alter Procedure State_12MC_SFC_Proc

As Begin

declare @SQL nvarchar(max), @Cols nvarchar(max)
Begin
If object_id('Results','U') is not null drop table Results
begin
drop table #Temp
End

set @Cols = stuff((select ', ' + quotename(Period_no)
from (select distinct Period_no from REAS_HPI_State_Final where Period_no>=193) X
order by Period_no desc for XML PATH('')),1,2,'')
set @SQL = 'Select DISTINCT R.STATE_NAME, pvt.*
INTO #Temp
from
(select state_code,Period_no, HPI_12M_Change*100 as [Percents] from REAS_HPI_STATE_Final where tier_code in(0)
) src
PIVOT
( max([Percents]) For Period_no IN( '+@Cols +')) as Pvt
INNER JOIN REAS_STATE_BE r
ON pvt.STATE_CODE = R.STATE_Code'
execute (@SQL)
End;
END;

But when execute the SP, i am getting following error,

Msg 3701, Level 11, State 5, Procedure State_12MC_SFC_Proc, Line 9
Cannot drop the table '#Temp', because it does not exist or you do not have permission.

(52 row(s) affected)

Thanks,

 

[markros]

I meant:

Alter Procedure State_12MC_SFC_Proc

As Begin

declare @SQL nvarchar(max), @Cols nvarchar(max)
Begin
If object_id('Results','U') is not null drop table Results

set @Cols = stuff((select ', ' + quotename(Period_no)
from (select distinct Period_no from REAS_HPI_State_Final where Period_no>=193) X
order by Period_no desc for XML PATH('')),1,2,'')
set @SQL = 'Select DISTINCT R.STATE_NAME, pvt.* 
INTO Results
from
(select state_code,Period_no, HPI_12M_Change*100 as [Percents] from REAS_HPI_STATE_Final where tier_code in(0) 
) src
PIVOT
( max([Percents]) For Period_no IN( '+@Cols +')) as Pvt 
INNER JOIN REAS_STATE_BE r
ON pvt.STATE_CODE = R.STATE_Code'
execute (@SQL)
End;
END;

PluralSight Learning Library

 

[VIJSQL01]

You are right. This query worked for me. Thanks a ton. Let me look into the next step.
If i face problem again, i will bother you again.

Sorry about that :->

 

[VIJSQL01]

hi,

I am facing a problem in conerting the date format which is in MM/DD/YYYY to MMM-YY format. I have searched in google. I am finding conversion function for other formats. But not for this format.

Example:

Cureent format: MM/DD/YYYY
Ex: 1/1/1988

Expected format: Mon-YY
Ex:Jan-88

Thanks,

 

[markros]

Check formats 106 and 113 in CAST & CONVERT

http://msdn.microsoft.com/en-us/library/ms187928.aspx

with both of them you need to start from the 4th character (first two being dd and a space). You can also replace a space with -

Also, it's a good idea to start new topic in a new thread.

PluralSight Learning Library