CODEWITHSUNDEEP
sqlsql-servercursorsql-inserttemp-tables
user3453159
user3453159

Reputation: 159

how to insert Date into TempTable inside Cursor?

I have a cursor (just for practice and test cursor) and I want to Insert My Result into TempTable I tried this But I got this error

An INTO clause is not allowed in a cursor declaration.

My code:

DECLARE cursor_brand CURSOR FOR
     SELECT firstname, LastName 
     INTO #tempTable_2 
     FROM Person.Person
     JOIN Sales.SalesOrderDetail ON SalesOrderDetail.SalesOrderDetailID = Person.BusinessEntityID
     JOIN Sales.SalesOrderHeader ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
     WHERE OrderDate LIKE '%2011%' AND LineTotal > 10000
     ORDER BY LastName

OPEN cursor_brand;

FETCH NEXT FROM cursor_brand

WHILE @@FETCH_STATUS = 0
    FETCH NEXT FROM cursor_brand
    CLOSE cursor_brand
    DEALLOCATE cursor_brand;

Upvotes: 0

Views: 253

Answers (1)

GMB
GMB

Reputation: 222632

I really don't see why you would need a cursor here. To insert into the temp table, using the select ... into ... syntax is just enough, so I think your whole code should be simplified as:

select p.firstname, p.LastName 
into #tempTable_2
from Person.Person p
join Sales.SalesOrderDetail sod on sod.SalesOrderDetailID = p.BusinessEntityID
join Sales.SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID
where soh.OrderDate like '%2011%' and sod.LineTotal > 10000

Note that I used table aliases to shorten the query - I had to make assumptions about columns in the select and where clauses. If these assumptions are correct, then you are only selecting person columns, so I am actually unsure that this code does exactly what you want: if a person has more than one row that satisfies the conditions on salesOrder and salesOrderDetail, then it would be inserted more than once in the temp table. You can avoid that by using exists instead of joins:

select p.firstname, p.LastName 
into #tempTable_2
from Person.Person p
where exists (
    select 1 
    from Sales.SalesOrderDetail sod
    join Sales.SalesOrderHeader soh on soh.SalesOrderID = sod.SalesOrderID
    where 
        soh.OrderDate like '%2011%' 
        and sod.LineTotal > 10000
        and sod.SalesOrderDetailID = p.BusinessEntityID
)

Finally, if soh.OrderDate is of a date-like datatype (as it should), then you should use date functions rather than string functions to filter it. That is, replace this:

soh.OrderDate like '%2011%'

With:

soh.OrderDate >= '2011-01-01' and soh.OrderDate < '2012-01-01'

Upvotes: 1

Related Questions