I'd like to go arounfd the block, but the Shuttle is grounded, so I guess I'll have to build a new one...
Or...create a sproc like the one below and create a scheduled job on the server...
CREATE mySPROC @FilePath varchar(200) @File_Name varchar(55)
AS
BEGIN
DECLARE @filePathAndName varchar(255), @cmd varchar(2000), @Command_String (3000)
TRUNCATE TABLE myTable1
TRUNCATE TABLE myTable2
Select @FilePathAndName = @FilePath + '\' + @File_Name
SET @cmd = 'bcp ' + @db_name + '..LOAD_TEMP in '
+ @FilePathAndName + ' -t"" -c -S' + @@servername + ' -Utaxuser -Ptaxuser'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''
Exec(@Command_String)
/* Some SQL like the following to follow the logic you need to populate your final tables */
INSERT INTO FINAL_TABLE (
TAx_Year
, Company_Cd
, Admin_Sys_Cd
, BUF_Cd
, Admin_Unit_Cd
, Contract_No
, TIN
, TIN_Type
, Return_Type_1099
, W2_Type
, State_Cd
, Tax_Event_Dt
, State_Tax_WH
, Prev_State_Tax
, Fed_Tax_WH
, Prev_Fed_Tax
, Wages
, Soc_Sec_Wages
, Soc_Sec_Withheld
, Medicare_Wages
, Medicare_Withheld
, ADV_EIC
, Emp_First_Name
, Emp_Last_Name
, Total_Record_Count
)
SELECT Substring(DataRow,1,4) AS TAx_Year
, Substring(DataRow,5,3) AS Company_Cd
, Substring(DataRow,8,3) AS Admin_Sys_Cd
, Substring(DataRow,11,3) AS BUF_Cd
, Substring(DataRow,14,1) AS Admin_Unit_Cd
, Substring(DataRow,15,20) AS Contract_No
, Substring(DataRow,35,9) AS TIN
, Substring(DataRow,44,1) AS TIN_Type
, Substring(DataRow,45,1) AS Return_Type_1099
, Substring(DataRow,46,2) AS W2_Type
, Substring(DataRow,48,2) AS State_Cd
, Substring(DataRow,50,26) AS Tax_Event_Dt
, CONVERT(money,Substring(DataRow,76,14))/100 AS State_Tax_WH
, CONVERT(money,Substring(DataRow,90,14))/100 AS Prev_State_Tax
, CONVERT(money,Substring(DataRow,104,14))/100 AS Fed_Tax_WH
, CONVERT(money,Substring(DataRow,118,14))/100 AS Prev_Fed_Tax
, CONVERT(money,Substring(DataRow,132,14))/100 AS Wages
, CONVERT(money,Substring(DataRow,146,14))/100 AS Soc_Sec_Wages
, CONVERT(money,Substring(DataRow,160,14))/100 AS Soc_Sec_Withheld
, CONVERT(money,Substring(DataRow,174,14))/100 AS Medicare_Wages
, CONVERT(money,Substring(DataRow,188,14))/100 AS Medicare_Withheld
, Substring(DataRow,202,14) AS ADV_EIC
, Substring(DataRow,216,15) AS Employee_First_Name
, Substring(DataRow,231,40) AS Employee_Last_Name
, Substring(DataRow,271,8) AS Total_Record_Count
FROM LOAD_TEMP
WHERE Substring(DataRow,1,1) <> 'H'
AND Substring(DataRow,1,14) <> 'CONTROL_TOTALS'
GO
All I wanted was a cup of coffee....
Brett
8-)
SELECT @@POST FROM Brain ORDER BY NewId()
That's correct! It's an AlphaNumeric!