SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Field not found in temp table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Menorel
Starting Member

USA
15 Posts

Posted - 07/05/2013 :  16:27:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 07/05/2013 :  16:42:38  Show Profile  Reply with Quote
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
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/08/2013 :  07:56:06  Show Profile  Reply with Quote
Do I need the aliases at all?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 07/08/2013 :  08:19:33  Show Profile  Reply with Quote
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.
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/15/2013 :  09:05:29  Show Profile  Reply with Quote
Thanks TG that got me going in the right direction.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000