Conversion error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Conversion error

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-08 : 14:01:13

I get the following error.

Syntax error converting the varchar value 'N' to a column of data type int.

What I am trying to do is create a temporary table and insert values from a query. And also create another query, then merge the two tables together.



	CREATE TABLE #Temp_OFFICE ( 
		[Code] varchar(255) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL,
		[Key] varchar(255) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL)

	INSERT INTO #Temp_OFFICE (Code, Key)		
	
	SELECT office.office_id AS Code,
		office.office_work_key AS Key
	FROM OFFICE



	SELECT office.office_id AS Code,
		office.office_home_key AS Key
	FROM OFFICE

	UNION ALL 
	
	SELECT *
	FROM #Temp_OFFICE
	
	-- DROP TEMPORARY TABLE
	DROP TABLE #Temp_OFFICE

I have no idea where this error is coming from and how to debug or fix it!

Any help would be greatly appreciated.

thank you

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-08 : 15:49:01

I'm guessing that office.office_id is an int. As it is in the first query in the union it will get the type from this value and so try to convert #Temp_OFFICE.Code to an int.

try
SELECT convert(varchar(255),office.office_id) AS Code,
office.office_home_key AS Key
FROM OFFICE

UNION ALL

SELECT *
FROM #Temp_OFFICE

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 02:34:01

When you use Union or Union All, then make sure that they match with datatypes and number of columns

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources