Hello Thank you for your help. I have commented an UPDATE statement about 3/4 of the way down. The update statement is producing an error that I am sure has something to do with the Declaration and Size of the data field but I am simply not understanding it. I've tried giving the declaration and the convert function a size of (20) and (max) but the error still persists. I am not a real SQL programmer. Help will be greatly appreciated. Also, I am interested in any comments regarding design of this query/update. One of the problems I have with coding is always knowing there has to be a better, more efficient way, and that always bothers me! :)
ERROR MESSAGE:
Msg 8152, Level 16, State 14, Line 70
String or binary data would be truncated.
The statement has been terminated.
TABLE STRUCTURE:
USE [TestExcelQuery]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_FocusAgents](
[idx] [float] NULL,
[AgyCode] [float] NULL
) ON [PRIMARY]
GO
ERRONEOUS SQL:
DECLARE @i int
DECLARE @numrows int
DECLARE @agency_id int
DECLARE @agency_code varchar(max)
DECLARE @agency_entire_code varchar(max)
DECLARE @AssociatedAgencyCodes varchar(max)
DECLARE @agency_name varchar(max)
DECLARE @agency_lob varchar(max)
DECLARE @agency_table TABLE
(
idx smallint Primary Key IDENTITY(1,1)
,agency_id varchar(max)
,agency_name varchar(max)
,agency_lob varchar(max)
,AssociatedAgencyCodes varchar(max)
)
INSERT INTO @agency_table
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\MSSQLTips\Agents.xls;',
'SELECT AgyCode, AgencyName, LineofBusiness, AssociatedAgencyCodes FROM [Sheet1$]')
Select * from @agency_table
SET @agency_entire_code = CONVERT(VARCHAR(max), '000') + CONVERT(VARCHAR(max), @agency_code);
SET @i = 0
SET @numrows = (SELECT COUNT(*) FROM @agency_table)
IF @numrows > 0
WHILE (@i <= (SELECT MAX(idx) FROM @agency_table))
BEGIN
SET @agency_id = (SELECT agency_id FROM @agency_table WHERE idx = @i)
SET @agency_lob = (Select agency_lob FROM @agency_table WHERE idx = @i)
SET @AssociatedAgencyCodes = replace((SELECT AssociatedAgencyCodes FROM @agency_table WHERE idx = @i),' ','')
PRINT 'Associated Agency Codes: ' + CAST(@AssociatedAgencyCodes AS VARCHAR(MAX))
Select * FROM [AdventureWorks].[dbo].[tbl_FocusAgents] WHERE Focus_FocusAgentCd = @agency_entire_code
BEGIN
UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
SET Focus_FarmFocus = CASE WHEN @agency_lob = 'Farm' THEN 'Y' ELSE 'N''' END,
Focus_CommercialFocus = CASE WHEN @agency_lob = 'Commerical' THEN 'Y' ELSE 'N''' END,
Focus_PersonalFocus = CASE WHEN @agency_lob = 'Personal' THEN 'Y' ELSE 'N''' END
WHERE Focus_FocusAgentCd= @agency_entire_code
eND
DECLARE @List nvarchar(100)
DECLARE @ListItem nvarchar(MAX)
DECLARE @Pos int
SET @List = @AssociatedAgencyCodes
WHILE LEN(@List) > 0
BEGIN
SET @Pos = CHARINDEX(',', @List)
IF @Pos = 0
BEGIN
SET @ListItem = @List
End
ELSE
BEGIN
SET @ListItem = CONVERT(VARCHAR(MAX), '000') + SUBSTRING(@List, 1, @Pos - 1)
End
BEGIN
UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
SET
Focus_FarmFocus =
CASE WHEN @agency_lob = 'Farm' THEN 'Y' ELSE 'N''' END,
Focus_CommercialFocus =
CASE WHEN @agency_lob = 'Commerical' THEN 'Y' ELSE 'N''' END,
Focus_PersonalFocus =
CASE WHEN @agency_lob = 'Personal' THEN 'Y' ELSE 'N''' END
WHERE Focus_FocusAgentCd= @ListItem
END
PRINT 'SET ASSOCIATED CODE'
PRINT @agency_lob
PRINT @ListItem
IF @Pos = 0
BEGIN
SET @List = ''
END
ELSE
Begin
SET @List = SUBSTRING(@List, @Pos + 1, LEN(@List) - @Pos)
End
End
SET @i = @i + 1
END
GO