SQL Server – How to Create and Fill Temp Table

sql serverstored-procedurestemporary-tables

I have a stored procedure that selects some data and returns it. Now I also want to add a temp table, fill it with data and return it with the stored procedure.

I define it as follows:

create table #tempDateTable
(
    Name varchar,
    TempDate datetime
)

INSERT INTO #tempDateTable(Name, TempDate) values ('Smith', @myDate)
SELECT * FROM #tempDateTable

@myDate is parameter passed to the stored procedure.

When I execute the stored procedure it gives me:

Msg 8152, Level 16, State 14, Procedure date_samples_procedure, Line 90
String or binary data would be truncated.

What I find strange is that Line 90 is not any of the above shown lines, but if I remove all the code for this #tempDateTable, the stored procedure works normally.

Best Answer

You should specify the length of the varchar column e.g. varchar(2000).

Related Solutions

Sql-server – VarChar to NVarChar causing truncate error

Well this won't happen unless

a trigger is firing on the target table, and the error happens there
the INSERT does some processing that adds enough data to make it too wide

Also, what happens with this please? This can not fail unless there is some processing.

INSERT INTO table (col1, col2)
SELECT LEFT(column1, 200)
      ,column2
  FROM tmpTable

SQL Server 2012 – Indexing on Multiple NVARCHAR(MAX) Columns

One thing you might try is breaking those fields out into their own table(s). Then reference them by ID. You could then set up a UNIQUE index on those ID columns, and still use your hashing trick to ensure you have unique values in the new tables (if the values need to be unique there).

Example table(s):

[Shipper]
 ShipperID INT,
 ShipperName NVARCHAR(MAX)

[Consignee]
 ConsigneeID INT,
 ConsigneeName NVARCHAR(MAX)

ETC...

Unfortunately, with this type of design problem you have to get a little creative. Having that many NVARCHAR(MAX) columns is problematic on many levels.

Best Answer

Related Solutions

Sql-server – VarChar to NVarChar causing truncate error

SQL Server 2012 – Indexing on Multiple NVARCHAR(MAX) Columns

Related Question