Field not found in temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Field not found in temp table

Author

Topic

Menorel
Starting Member

15 Posts

Posted - 2013-07-05 : 16:27:25

Below is the Stored procedure I am working with. The Data I am getting in the CSV file is prone to having duplicate information in it from previous days. So I created an Identity Field with an ID and the Date included and set it as the PK for the table in the hopes of importing the data without creating duplicate records. Of course now the SP fails due to PK violations when the duplicate records are incountered so I modified my original SP with the following. But I am getting the following messge when I run the SP. I create the temp table with the Column and have it though out the code, just not seeing where I am missing it.

Msg 4104, Level 16, State 1, Procedure usp_ImportMultipleFiles, Line 65
The multi-part identifier "#PayRollData.WORKEDINSeconds" could not be bound.


declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
declare @Filepath varchar(500)
declare @pattern varchar(100)
declare @TableName varchar(128)

set @count1 =0
set @Filepath = '\\susagpwfm01\TRE_Files\'
set @pattern = '*.csv'
set @TableName = 'tbl_Payroll'
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@Filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x 
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
--Set @Filepath = 'NET USE \\susagpwfm01\TRE_Files urban2009/User:worldshipups1 '
Set @Pattern = '*.csv'
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'
--Print @TableName
--Print @FilePath
--Print @FileName
print @query
exec (@query)

SET IDENTITY_INSERT SCCC_DB.dbo.tbl_PayRollData ON

CREATE TABLE #tblPayroll (PayRollID nchar(50))



CREATE TABLE #PayRollData (PayRollID nchar(50), EmployeeID int, StartDate datetime, TIMERECORDID int, ExternalID int, TempDept int, ActivityType nchar(6), WORKEDINSeconds int, Is_Paid bit)
 
INSERT INTO #PayRollData

SELECT     EmployeeID + REPLACE(Startdate, '/', '_') AS PayRollID, EmployeeID, Startdate, TIMERECORDID, ExternalID, 
                      CASE WHEN OrganizationName = N'ITHD TRETON' THEN N'999928' ELSE N'249983' END AS TempDept, CASE WHEN tbl_Payroll.Activity IN (N'PTO', N'JD', N'HOL') 
                      THEN Activity ELSE N'WORKED' END AS ActivityType, SUM(CASE WHEN TIMERECORDID IS NULL THEN CONVERT(decimal, Duration) * 3600 ELSE DATEDIFF(ss, 
                      StartDate + CONVERT(datetime, StartTime), EndDate + CONVERT(datetime, EndTime)) END) AS WORKEDINSeconds, 
                      CASE WHEN IsPaid = N'True' THEN 1 ELSE 0 END AS Is_Paid
FROM         tbl_Payroll
GROUP BY TIMERECORDID, ExternalID, EmployeeID, CASE WHEN IsPaid = N'True' THEN 1 ELSE 0 END, 
                      CASE WHEN OrganizationName = N'ITHD TRETON' THEN N'999928' ELSE N'249983' END, CASE WHEN tbl_Payroll.Activity IN (N'PTO', N'JD', N'HOL') 
                      THEN Activity ELSE N'WORKED' END, Startdate, EmployeeID + REPLACE(Startdate, '/', '_')
HAVING      (CASE WHEN IsPaid = N'True' THEN 1 ELSE 0 END = 1)

UPDATE SCCC_DB.dbo.tbl_PayRollData
SET SCCC_DB.dbo.tbl_PayRollData.WORKEDINSeconds = #PayRollData.WORKEDINSeconds
OUTPUT inserted.PayRollID INTO #tblPayroll
FROM SCCC_DB.dbo.tbl_PayRollData a INNER JOIN
	  #PayRollData b ON a.PayRollID = b.PayRollID



INSERT INTO tbl_PayRollData
SELECT *
FROM #PayRollData
WHERE EMPLOYEEID IS NOT NULL AND PayRollID NOT IN (SELECT PayRollID from #tblPayRoll)
DROP TABLE #PayRollData

--Clean up Imported Payroll Data for the Next Import
DELETE FROM [SCCC_DB].[dbo].[tbl_Payroll]

end



EXEC master..xp_cmdshell 'MOVE \\susagpwfm01\TRE_Files\*.csv \\susagpwfm01\TRE_Files\Processed'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-07-05 : 16:42:38

I didn't bother to try to understand all you are doing but the error is because you have assigned table aliases but are not using them. Change the update to this:


UPDATE a SET 
       a.WORKEDINSeconds = b.WORKEDINSeconds
OUTPUT inserted.PayRollID INTO #tblPayroll
FROM SCCC_DB.dbo.tbl_PayRollData a INNER JOIN
	  #PayRollData b ON a.PayRollID = b.PayRollID

Be One with the Optimizer
TG

Menorel
Starting Member

15 Posts

Posted - 2013-07-08 : 07:56:06

Do I need the aliases at all?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-08 : 08:19:33

Table aliases are not required except in cases where the meaning is not clear unless an alias is used (for example, when you join a table to itself). Even when it is not required, most people use aliases because it often improves readability and requires less typing.

Menorel
Starting Member

15 Posts

Posted - 2013-07-15 : 09:05:29

Thanks TG that got me going in the right direction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources