Bulk insert in procedure. Use of quotes for variables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2012 » SQL Server 2012 - T-SQL » Bulk insert in procedure. Use of quotes for...

Bulk insert in procedure. Use of quotes for variables

Rate Topic

Display Mode

Topic Options

Author

Message

ohpenot

Posted Thursday, February 21, 2013 6:09 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:13 AM
Points: 20, Visits: 71

Hi everybody,

I struggled with a bulk insert in procedure and i eventually found a working piece of code. Except that i did'nt understand the general rules to wrap variables.

Exemple :
N'BULK INSERT ' + QUOTENAME(@nomtable) + N'FROM ''' +
@chemin + @filename +
N''' WITH (FIELDTERMINATOR = '';'', ROWTERMINATOR = '''', FIRE_TRIGGERS)'

This is working, but why are we using ''' ??
Now something that's not working..

SET @effacer = 'master.dbo.xp_cmdshell ''DEL '+ @chemin + @filename
EXECUTE (@effacer)

Something wrong with quotes i guess....

I'd like to understand the rule for this!

Post #1422523

ohpenot

Posted Thursday, February 21, 2013 6:12 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:13 AM
Points: 20, Visits: 71

I t works using ''''

SET @effacer = 'master.dbo.xp_cmdshell ''DEL '+ @chemin + @filename +''''

but i don't understand why (tried everything)

Post #1422527

j.miner

Posted Tuesday, April 02, 2013 10:22 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 79, Visits: 282

It all has to do about strings ... And what is truely a string?

First example does not have quotes in the string.

-- Simple string
DECLARE @X VARCHAR(128);
SET @X = 'A SIMPLE STRING';
PRINT @X;

Second example uses extra quote to denote usage as a literal, not a start of a string.

-- Escaping the quotes
DECLARE @Y VARCHAR(128);
SET @Y = 'A STRING WITH ''' + CONVERT(CHAR(10), GETDATE(), 101) + ''' QUOTES AROUND THE DATE';
PRINT @Y;

Third example uses the ANSI character for quote.

-- Using CHAR()
DECLARE @Z VARCHAR(128);
SET @Z = 'A STRING WITH ' + CHAR(39) + CONVERT(CHAR(10), GETDATE(), 101) + CHAR(39) + ' QUOTES AROUND THE DATE';
PRINT @Z;

You can use the system function QUOTENAME() to make quoted strings.

http://msdn.microsoft.com/en-us/library/ms176114.aspx

You can even change the character used for escaping (ESCAPE) in a LIKE statement.

http://msdn.microsoft.com/en-us/library/ms179859.aspx

I hope this helps.

Sincerely

John

John Miner
Crafty DBA
www.craftydba.com

Post #1437997

ohpenot

Posted Wednesday, April 03, 2013 4:58 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:13 AM
Points: 20, Visits: 71

hi,
yes it helps a lot !

thank you very much. I ll try this tomorrow at work

Post #1438614

« Prev Topic | Next Topic »

Permissions