TSQL Help (SQL Server 2005)

Question

I have been playing around with a quite complex SQL Statement for a few days, and have gotten most of it working correctly.

I am having trouble with one last part, and was wondering if anyone could shed some light on the issue, as I have no idea why it isnt working:

INSERT INTO ExistingClientsAccounts_IMPORT
SELECT DISTINCT 
cca.AccountID, cca.SKBranch, cca.SKAccount, cca.SKName, cca.SKBase, 
cca.SyncStatus, cca.SKCCY, cca.ClientType, cca.GFCID, cca.GFPID, cca.SyncInput, 
cca.SyncUpdate, cca.LastUpdatedBy, cca.Deleted, cca.Branch_Account, cca.AccountTypeID
FROM         ClientsAccounts AS cca 
INNER JOIN
(SELECT DISTINCT ClientAccount, SKAccount, SKDesc, 
    SKBase, SKBranch, ClientType, SKStatus, GFCID, 
    GFPID, Account_Open_Date, Account_Update
FROM          ClientsAccounts_IMPORT) AS ccai 
ON cca.Branch_Account = ccai.ClientAccount

Table definitions follow:

CREATE TABLE [dbo].[ExistingClientsAccounts_IMPORT](
    [AccountID] [int] NOT NULL,
    [SKBranch] [varchar](2) NOT NULL,
    [SKAccount] [varchar](12) NOT NULL,
    [SKName] [varchar](255) NULL,
    [SKBase] [varchar](16) NULL,
    [SyncStatus] [varchar](50) NULL,
    [SKCCY] [varchar](5) NULL,
    [ClientType] [varchar](50) NULL,
    [GFCID] [varchar](10) NULL,
    [GFPID] [varchar](10) NULL,
    [SyncInput] [smalldatetime] NULL,
    [SyncUpdate] [smalldatetime] NULL,
    [LastUpdatedBy] [varchar](50) NOT NULL,
    [Deleted] [tinyint] NOT NULL,
    [Branch_Account] [varchar](16) NOT NULL,
    [AccountTypeID] [int] NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[ClientsAccounts_IMPORT](
    [NEWClientIndex] [bigint] NOT NULL,
    [ClientGroup] [varchar](255) NOT NULL,
    [ClientAccount] [varchar](255) NOT NULL,
    [SKAccount] [varchar](255) NOT NULL,
    [SKDesc] [varchar](255) NOT NULL,
    [SKBase] [varchar](10) NULL,
    [SKBranch] [varchar](2) NOT NULL,
    [ClientType] [varchar](255) NOT NULL,
    [SKStatus] [varchar](255) NOT NULL,
    [GFCID] [varchar](255) NULL,
    [GFPID] [varchar](255) NULL,
    [Account_Open_Date] [smalldatetime] NULL,
    [Account_Update] [smalldatetime] NULL,
    [SKType] [varchar](255) NOT NULL
) ON [PRIMARY]

The error message I get is: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.

Answer 1

The error is because you are trying to insert data into a column in ExistingClientsAccounts_IMPORT where the column size is smaller than the length of data attempting to be inserted into it.

e.g. SKAccount column is VARCHAR(12) in the ExistingClientsAccounts_IMPORT table but is VARCHAR(255) in ClientsAccounts_IMPORT.

So if ClientsAccounts_IMPORT contains any rows where that field is longer than 12 characters, you will get that error as obv. e.g. 100 characters will not fit into a 12 character field.

You need to make sure all the columns in the table you are inserting into, are big enough - make sure each column definition matches the source table.

Answer 2

Check the field definitions. You can see some are smaller than the original ones. Now run a query on the old data - you will find some of the larger fields were used, so the insert is not possible without loosing data.

Example: SKAccount - from 255 length to 12.

Answer 3

The third column of your SELECT column list means that ExistingClientsAccounts_IMPORT.SKAccount is populated from ClientsAccounts.SKAccount - however, the source is up to 255 characters, while the destination has a capacity of 12. If there's any data that wouldn't fit, you'll get this message.

I haven't gone through all the other columns.

Answer 4

You are trying to insert values which is greater than tha max length specified for a column. using a profiler to check the data being passed to this query and verify the lenght of data against the permissible lenght for all columns.

There is a clear mismatch in the column lenghts of common columns of these two tables. ClientsAccounts_IMPORT.SKBase is 10 where as it is 16 in other table