?
Solved

Help with insert into in-memory table

Posted on 2010-01-11
17
Medium Priority
?
256 Views
Last Modified: 2012-05-08
Hi,
In the attached stored proc I need to know how to get the rows returned by the select statement into the in-memory table @t. In case relevant I am (still) using SQL Server 2000.
Thanks in advance,
Louise
ALTER PROCEDURE sp_1

as

declare @start_date datetime, @end_date datetime
set @start_date = CONVERT(DATETIME, '2009-08-01 00:00:00', 102)
set @end_date = CONVERT(DATETIME, '2009-08-31 00:00:00', 102)

declare @project_code nvarchar(150), @project_office nvarchar(20), @employee nvarchar(150), @employee_office nvarchar(20), @hours float, @hourly_rate float, @hourly_cost float

declare cur cursor fast_forward read_only for 
(select ProjectID from Projects where (ProjectID in (select ProjectID from tblTime where (theDate between @start_date and @end_date))))

declare @ProjectID int
declare @t table(id int identity(1,1), project_code nvarchar(150), project_office nvarchar(20), employee nvarchar(150), employee_office nvarchar(20), hours float, hourly_rate float, hourly_cost float)

open cur
while @@FETCH_STATUS = 0
begin

fetch next from cur into @ProjectID

select p.Project_Code AS project_code,
po_list.Office_Name AS project_office,
e.known_as + ' ' + e.last_name AS Employee,
eo_list.Office_Name AS employee_office,
t.hours AS Hours,
t.hourly_rate AS hourly_rate,
t.hourly_cost AS hourly_cost
FROM            dbo.Project_Teams AS pt INNER JOIN
                         dbo.tblEmployees AS e ON pt.TeamMemberID = e.id INNER JOIN
                         dbo.tblTime AS t ON pt.projectID = t.projectID INNER JOIN
                         dbo.Company_office_List AS po_list INNER JOIN
                         dbo.tblProjectOffices AS po ON po_list.officeID = po.OfficeID ON t.projectID = po.ProjectID INNER JOIN
                         dbo.Projects AS p ON t.projectID = p.projectID INNER JOIN
                         dbo.Company_office_List AS eo_list ON e.Company_officeID = eo_list.officeID
GROUP BY p.Project_Code, po_list.Office_Name, po_list.officeID, po.ProjectID, po.OfficeID, e.known_as, e.last_name, t.hours, t.hourly_rate, t.hourly_cost, e.Company_officeID, 
                         eo_list.Office_Name, t.theDate
HAVING        (po.ProjectID = @ProjectID) AND (e.Company_officeID <>
                             (SELECT        OfficeID
                               FROM            dbo.tblProjectOffices
                               WHERE        (ProjectID = @ProjectID))) AND (t.theDate BETWEEN @start_date AND @end_date)
 
--INSERT INTO @t VALUES(@project_code, @project_office, @employee, @employee_office, @hours, @hourly_rate, @hourly_cost)

end

fetch next from cur into @ProjectID

close cur
deallocate cur

select * from @t

Open in new window

0
Comment
Question by:louise001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
17 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26287924
ALTER PROCEDURE sp_1
as
declare @start_date datetime, @end_date datetime
set @start_date = CONVERT(DATETIME, '2009-08-01 00:00:00', 102)
set @end_date = CONVERT(DATETIME, '2009-08-31 00:00:00', 102)
declare @project_code nvarchar(150), @project_office nvarchar(20), @employee nvarchar(150), @employee_office nvarchar(20), @hours float, @hourly_rate float, @hourly_cost float
declare cur cursor fast_forward read_only for
(select ProjectID from Projects where (ProjectID in (select ProjectID from tblTime where (theDate between @start_date and @end_date))))
declare @ProjectID int
declare @t table(id int identity(1,1), project_code nvarchar(150), project_office nvarchar(20), employee nvarchar(150), employee_office nvarchar(20), hours float, hourly_rate float, hourly_cost float)
open cur
while @@FETCH_STATUS = 0
begin
fetch next from cur into @ProjectID
INSERT INTO @t
select p.Project_Code AS project_code,
po_list.Office_Name AS project_office,
e.known_as + ' ' + e.last_name AS Employee,
eo_list.Office_Name AS employee_office,
t.hours AS Hours,
t.hourly_rate AS hourly_rate,
t.hourly_cost AS hourly_cost
FROM            dbo.Project_Teams AS pt INNER JOIN
                         dbo.tblEmployees AS e ON pt.TeamMemberID = e.id INNER JOIN
                         dbo.tblTime AS t ON pt.projectID = t.projectID INNER JOIN
                         dbo.Company_office_List AS po_list INNER JOIN
                         dbo.tblProjectOffices AS po ON po_list.officeID = po.OfficeID ON t.projectID = po.ProjectID INNER JOIN
                         dbo.Projects AS p ON t.projectID = p.projectID INNER JOIN
                         dbo.Company_office_List AS eo_list ON e.Company_officeID = eo_list.officeID
GROUP BY p.Project_Code, po_list.Office_Name, po_list.officeID, po.ProjectID, po.OfficeID, e.known_as, e.last_name, t.hours, t.hourly_rate, t.hourly_cost, e.Company_officeID,
                         eo_list.Office_Name, t.theDate
HAVING        (po.ProjectID = @ProjectID) AND (e.Company_officeID <>
                             (SELECT        OfficeID
                               FROM            dbo.tblProjectOffices
                               WHERE        (ProjectID = @ProjectID))) AND (t.theDate BETWEEN @start_date AND @end_date)
 
end
fetch next from cur into @ProjectID
close cur
deallocate cur
select * from @t

0
 

Author Comment

by:louise001
ID: 26288127
Hi, thanks for replying. I've tried your syntax but I get the error message "An explicit value for the identity column in table @t can only be specified when a column list is used and indentity_insert is on"
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26288146
seems like one of the column is missing in the temp table ... check the table definition and the select list
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:louise001
ID: 26288456
If so I can't find it. The definition has the identity column plus 7 other columns:
declare @t table(id int identity(1,1),
project_code nvarchar(150),
project_office nvarchar(20),
employee nvarchar(150),
employee_office nvarchar(20),
hours float,
hourly_rate float,
hourly_cost float)

and the insert statement also has those 7 items:

INSERT INTO @t
select p.Project_Code AS project_code,
po_list.Office_Name AS project_office,
e.known_as + ' ' + e.last_name AS Employee,
eo_list.Office_Name AS employee_office,
t.hours AS Hours,
t.hourly_rate AS hourly_rate,
t.hourly_cost AS hourly_cost

Unless the table definition statement @t table(id int identity(1,1) needs to be changed in some way?
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26293254
Try change the select to:

INSERT INTO @t 
select null, p.Project_Code AS project_code, 
po_list.Office_Name AS project_office, 
e.known_as + ' ' + e.last_name AS Employee, 
eo_list.Office_Name AS employee_office, 
t.hours AS Hours, 
t.hourly_rate AS hourly_rate, 
t.hourly_cost AS hourly_cost 
FROM            dbo.Project_Teams AS pt INNER JOIN 
                         dbo.tblEmployees AS e ON pt.TeamMemberID = e.id INNER JOIN 
                         dbo.tblTime AS t ON pt.projectID = t.projectID INNER JOIN 
                         dbo.Company_office_List AS po_list INNER JOIN 
                         dbo.tblProjectOffices AS po ON po_list.officeID = po.OfficeID ON t.projectID = po.ProjectID INNER JOIN 
                         dbo.Projects AS p ON t.projectID = p.projectID INNER JOIN 
                         dbo.Company_office_List AS eo_list ON e.Company_officeID = eo_list.officeID 
GROUP BY p.Project_Code, po_list.Office_Name, po_list.officeID, po.ProjectID, po.OfficeID, e.known_as, e.last_name, t.hours, t.hourly_rate, t.hourly_cost, e.Company_officeID,  
                         eo_list.Office_Name, t.theDate 
HAVING        (po.ProjectID = @ProjectID) AND (e.Company_officeID <> 
                             (SELECT        OfficeID 
                               FROM            dbo.tblProjectOffices 
                               WHERE        (ProjectID = @ProjectID))) AND (t.theDate BETWEEN @start_date AND @end_date) 
  

Open in new window

0
 

Author Comment

by:louise001
ID: 26293292
Hi zadeveloper,

Thanks for response, same error occurs though.

Louise
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26293310
try it like this:
ALTER PROCEDURE sp_1 
 
as 
 
declare @start_date datetime, 
		@end_date datetime 
		
set @start_date = CONVERT(DATETIME, '2009-08-01 00:00:00', 102) 
set @end_date	= CONVERT(DATETIME, '2009-08-31 00:00:00', 102) 
 
declare @project_code nvarchar(150), 
		@project_office nvarchar(20), 
		@employee nvarchar(150), 
		@employee_office nvarchar(20), 
		@hours float, 
		@hourly_rate float, 
		@hourly_cost float 
 
declare cur cursor fast_forward read_only for  
	(select ProjectID from Projects where (ProjectID in (
				select ProjectID from tblTime where (theDate between @start_date and @end_date)))) 
 
declare @ProjectID int 
declare @t table
(
	id int identity(1,1), 
	project_code nvarchar(150), 
	project_office nvarchar(20), 
	employee nvarchar(150), 
	employee_office nvarchar(20), 
	[hours] float, 
	hourly_rate float, 
	hourly_cost float
) 
 
open cur 

fetch next from cur into @ProjectID 

while @@FETCH_STATUS = 0 begin 
 
	
INSERT INTO @t
select 
	null,
	p.Project_Code, 
	po_list.Office_Name, 
	e.known_as + ' ' + e.last_name, 
	eo_list.Office_Name, 
	t.[hours], 
	t.hourly_rate, 
	t.hourly_cost
FROM            
	dbo.Project_Teams AS pt INNER JOIN 
	dbo.tblEmployees AS e ON pt.TeamMemberID = e.id INNER JOIN 
    dbo.tblTime AS t ON pt.projectID = t.projectID INNER JOIN 
    dbo.Company_office_List AS po_list INNER JOIN 
    dbo.tblProjectOffices AS po ON po_list.officeID = po.OfficeID ON t.projectID = po.ProjectID INNER JOIN 
    dbo.Projects AS p ON t.projectID = p.projectID INNER JOIN 
    dbo.Company_office_List AS eo_list ON e.Company_officeID = eo_list.officeID 
GROUP BY 
	p.Project_Code, po_list.Office_Name, po_list.officeID, 
	po.ProjectID, po.OfficeID, e.known_as, e.last_name, 
	t.[hours], t.hourly_rate, t.hourly_cost, e.Company_officeID,  eo_list.Office_Name, t.theDate 
HAVING        (po.ProjectID = @ProjectID) AND (e.Company_officeID <> 
                             (SELECT        OfficeID 
                               FROM            dbo.tblProjectOffices 
                               WHERE        (ProjectID = @ProjectID))) AND (t.theDate BETWEEN @start_date AND @end_date) 
  

	fetch next from cur into @ProjectID 
end  
close cur 
deallocate cur 
 
select * from @t

Open in new window

0
 

Author Comment

by:louise001
ID: 26293327
No change ...
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26293331
declare @t like this
declare @t table
(
	id int NOT NULL IDENTITY (1, 1),
	project_code nvarchar(150), 
	project_office nvarchar(20), 
	employee nvarchar(150), 
	employee_office nvarchar(20), 
	[hours] float, 
	hourly_rate float, 
	hourly_cost float
) 

Open in new window

0
 

Author Comment

by:louise001
ID: 26293363
Have tried that too, still no change
0
 
LVL 13

Accepted Solution

by:
zadeveloper earned 2000 total points
ID: 26293446
ok ...
I have tested this and its working on my side.

ALTER PROCEDURE sp_1  
  
as  
  
declare @start_date datetime,  
                @end_date datetime  
                 
set @start_date = CONVERT(DATETIME, '2009-08-01 00:00:00', 102)  
set @end_date   = CONVERT(DATETIME, '2009-08-31 00:00:00', 102)  
  
declare @project_code nvarchar(150),  
                @project_office nvarchar(20),  
                @employee nvarchar(150),  
                @employee_office nvarchar(20),  
                @hours float,  
                @hourly_rate float,  
                @hourly_cost float  
  
declare cur cursor fast_forward read_only for   
        (select ProjectID from Projects where (ProjectID in ( 
                                select ProjectID from tblTime where (theDate between @start_date and @end_date))))  
  
declare @ProjectID int  
declare @t table 
( 
        id int NOT NULL IDENTITY (1, 1), 
        project_code nvarchar(150),  
        project_office nvarchar(20),  
        employee nvarchar(150),  
        employee_office nvarchar(20),  
        [hours] float,  
        hourly_rate float,  
        hourly_cost float 
) 
  
open cur  
 
fetch next from cur into @ProjectID  
 
while @@FETCH_STATUS = 0 begin  
  
         
insert into @t (project_code, project_office, employee, employee_office, [hours], hourly_rate, hourly_cost)
select          
        p.Project_Code,  
        po_list.Office_Name,  
        e.known_as + ' ' + e.last_name,  
        eo_list.Office_Name,  
        t.[hours],  
        t.hourly_rate,  
        t.hourly_cost 
FROM             
        dbo.Project_Teams AS pt INNER JOIN  
        dbo.tblEmployees AS e ON pt.TeamMemberID = e.id INNER JOIN  
    dbo.tblTime AS t ON pt.projectID = t.projectID INNER JOIN  
    dbo.Company_office_List AS po_list INNER JOIN  
    dbo.tblProjectOffices AS po ON po_list.officeID = po.OfficeID ON t.projectID = po.ProjectID INNER JOIN  
    dbo.Projects AS p ON t.projectID = p.projectID INNER JOIN  
    dbo.Company_office_List AS eo_list ON e.Company_officeID = eo_list.officeID  
GROUP BY  
        p.Project_Code, po_list.Office_Name, po_list.officeID,  
        po.ProjectID, po.OfficeID, e.known_as, e.last_name,  
        t.[hours], t.hourly_rate, t.hourly_cost, e.Company_officeID,  eo_list.Office_Name, t.theDate  
HAVING        (po.ProjectID = @ProjectID) AND (e.Company_officeID <>  
                             (SELECT        OfficeID  
                               FROM            dbo.tblProjectOffices  
                               WHERE        (ProjectID = @ProjectID))) AND (t.theDate BETWEEN @start_date AND @end_date)  
   
 
        fetch next from cur into @ProjectID  
end   
close cur  
deallocate cur  
  
select * from @t

Open in new window

0
 

Author Comment

by:louise001
ID: 26293538
Hi again,

This still provoked the same error, but changing the insert statement to this:

INSERT INTO @t(project_code, project_office, employee, employee_office, hours, hourly_rate, hourly_cost)

allows it to run successfully, I got this from http://www.sqlstuff.dk/post/An-explicit-value-for-the-identity-column.aspx

Louise

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26293788
Hi, this is what i posed see: 12/01/10 04:56 PM, ID: 26293446
0
 

Author Comment

by:louise001
ID: 26294523
Hi zadeveloper,

Could you clarify last comment?

Louise
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26294734
Hey in my last post I also recommended changing the select statement as u described
0
 

Author Comment

by:louise001
ID: 26301777
You did indeed. Thank you. Was in a hurry yesterday.
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26301865
np. Good luck :)
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question