Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk insert in procedure. Use of quotes for variables Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 6:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 7:28 AM
Points: 20, Visits: 106
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
Posted Thursday, February 21, 2013 6:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 7:28 AM
Points: 20, Visits: 106
I t works using ''''

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

but i don't understand why (tried everything)
Post #1422527
Posted Tuesday, April 02, 2013 10:22 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:34 PM
Points: 80, Visits: 330
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
Posted Wednesday, April 03, 2013 4:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 7:28 AM
Points: 20, Visits: 106
hi,
yes it helps a lot !

thank you very much. I ll try this tomorrow at work
Post #1438614
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse