Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with insert into in-memory table

Posted on 2010-01-11
17
Medium Priority
?
267 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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