Dealing with 's in insert statements

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

General SQL Server Forums

New to SQL Server Programming

Dealing with 's in insert statements

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-05-31 : 05:34:34

Hello all. Got bit of a long winded question here...........so here we go lol.

OK.......ive got data on an Excel spreadsheet. Ive set the spreadsheet up as a linked server and i'm creating a set of insert statements from it by using the following code:


SELECT 'INSERT INTO TRAINREC (COURSE_NAME) VALUES ('''+
	 EMPLOY_REF + ''', '''+ 
	   
	 FROM AtriumDD...['Employee Training Records$']

For most records this generates a correct insert statement.........for example:


INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('153', 'NMA Panel');

However.........my problems start when the value for course name is containes an ' character. If it does the insert statement generated is incorrect. For example:


INSERT INTO TRAINREC (EMPLOY_REF, COURSE_NAME) VALUES ('139', 'Annual Accounting in Lloyd's Market');

can anyone suggest any ideas on how to get round this? Also if i havent explained it clearly enough just let me know and i can try and expand on it.

Thanks for reading.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-31 : 05:43:59

Try

SELECT 'INSERT INTO TRAINREC (COURSE_NAME) VALUES ('''+
	 REPLACE(test, CHAR(39), CHAR(39)+CHAR(39))  + ''', '''+ 
	   
	 FROM AtriumDD...['Employee Training Records$']

Mark

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-31 : 05:46:51

p.s. you might want to consider renaming your destination table - phonetically it's not very auspicious!

Mark

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-05-31 : 09:22:36

Hi Mark, thanks for the reply mate.

Just a little confused about what youve advised. The code i posted was actually slightly wrong. However, am i reight in thinking that the following is the way to go about it:



SELECT 'INSERT INTO TRAINREC (COURSE_NAME) VALUES ('''+
	 REPLACE(COURSE_NAME, CHAR(39), CHAR(39)+CHAR(39))  + ''', '''+    
	 FROM AtriumDD...['Employee Training Records$']

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2007-05-31 : 09:28:12

Hi Mark, forget that last question. Used your suggestion and it works prefectly. Brilliant stuff mate...........thanks very much! :D

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-31 : 10:13:59

No worries!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources