Often, Newbies to SQL Server are left feeling puzzled by the difficulty of 'Getting stuff in and out' of data tables.
At first it does seem daunting, and I have to admit that I'm still a bit vague on some aspects myself. How, (or why) would one get image data in or out of a table for example? There are, of course, the standard ways of importing data that you can read in the manuals, but I'd like to touch on a few methods that the standard tutorials would'nt want to mention, or which would cause them to hiss in disapproval, and I'd be interested in any other of the more unusual methods that readers use get data in and out of SQL Server. (as usual, we'll offer an award to the most ingenious)
The job of 'getting stuff in and out' can be quite subtle. As an example, the other day, an accountant phoned me about a system I'd installed, asking that the invoices that go to customers who pay in Euros should have the Euro exchange rate current at the date of issue showing against the VAT calculation. It had to be done quickly. It took me half an hour to get a reliable feed going and tested. The whole job was finished in three hours.
I used the command-line version of CURL called by xp_cmdshell. The feed was one stored procedure and no external application. I could call it on the scheduler once a day using an account with access to xp_cmdshell. Most of the work was around 'productionising' it so it would work robustly when unattended and alert the DBA if anything went wrong.
CURL is great. The feed I had to use was HTTPS, and it turned out not to be XML, but some Java format. Even if it had involved setting cookies, complicated authentication or impersonating a different source address it would all have worked fine. I've used it for all sorts of slightly questionable uses getting volatile information from the internet such as news, or stock prices. Such problems as having to pass POST variables are no problem to it. It takes most work in its stride.
I can't show you the actual routine because it involved a subscription service for a payment system. However, there are plenty of non-subscription sources. Just as an example, I use here the Bank of Canada Exchange-rate information service. The data is in a CSV format, which is in widespread with banks. The data has been 'pivoted', and we un-pivot it with a cross-join to make it more easily stored and manipulated in SQL Server. The slicing and chopping is done behind the scenes with two useful functions that 'Slice' the serialised (comma-delimited) data into its components or into a table. If you use this service, be moderate with it as it has been provided as a public service. The procedure outputs a result consisting of a date, the name of a currency, and numeric data representing the value of the currency against the Canadian dollar.
CREATE PROCEDURE spGetLatestCanadianExchangeRates
--allow the whereabouts of the CSV file to be specified
@WhereFrom VARCHAR(255)
='http://www.bankofcanada.ca/en/markets/csv/exchange_eng.csv'
AS
/*
Note on the exchange rates:
The daily noon exchange rates for major foreign currencies are
published every business day at about 1 p.m. EST. They are
obtained from market or official sources around noon, and show
the rates for the various currencies in Canadian dollars
converted from US dollars. The rates are nominal quotations -
neither buying nor selling rates - and are intended for
statistical or analytical purposes. Ratesavailable from financial
institutions will differ.
*/
DECLARE @Headings VARCHAR(8000)
--the headings for the columns in the CSV file
DECLARE @Command VARCHAR(8000)
--the command line sent to xp_cmdshell
SELECT @Command='curl -s -S "'+@wherefrom+'"'
CREATE TABLE #rawRSS (LineNumber INT IDENTITY(1,1),
LineContents VARCHAR(8000))--for the output
INSERT INTO #rawRSS(LineContents)
EXECUTE master..xp_cmdshell @Command--get the data
--find the column headings
--(indicator will vary from file to file)
SELECT @headings= LineContents
FROM ##rawRSS WHERE LineContents LIKE 'date %'
--
and then it is one SQL Call thanks to a couple of
--utility functions
SELECT [Date]=CONVERT(DateTime,item,101),
[currency]=CONVERT(VARCHAR(50),
dbo.ufsSplit(linecontents,1,',')),
[rate]=CONVERT(numeric(9,6),
dbo.ufsSplit(linecontents,SeqNo,',')
)
FROM
(SELECT SeqNo,Item FROM dbo.ufsSplitToTable(@Headings,',')
WHERE item NOT LIKE 'Date%'
)f
CROSS JOIN
(SELECT LineContents FROM #RawRSS WHERE lineContents NOT LIKE '#%'
AND lineContents NOT LIKE 'Date%')g
WHERE ISNUMERIC(dbo.ufsSplit(linecontents,SeqNo,','))>0
--and here are the utility functions--------------------------------
ALTER FUNCTION dbo.ufsSplitToTable
(
@StringArray VARCHAR(8000),
@Delimiter VARCHAR(10)
)
RETURNS
@Results TABLE
(
SeqNo INT IDENTITY(1, 1),
Item VARCHAR(8000)
)
--splits a string into a table using the specified delimitor. Works like 'Split' in most languages
--delimiters can be multi-character
AS
BEGIN
DECLARE @Next INT
DECLARE @lenStringArray INT
DECLARE @lenDelimiter INT
DECLARE @ii INT
SELECT @ii=1, @lenStringArray=LEN(
@StringArray), @lenDelimiter=LEN(@Delimiter)
WHILE @ii@lenStringArray
BEGIN
SELECT @next=CHARINDEX(@Delimiter, @StringArray + @Delimiter, @ii)
INSERT INTO @Results (Item)
SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)
SELECT @ii=@Next+@lenDelimiter
END
RETURN
END
--------------------------------------------------------------------------
CREATE function dbo.ufsSplit
(
@String VARCHAR(8000),
@which INT,
@Delimiter VARCHAR(10) = ','
)
--splits a string to get at the nth component in the string using the specified delimiter
--delimiters can be multi-character
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @ii INT
DECLARE @Substring VARCHAR(8000)
SELECT @ii=1, @Substring=''
WHILE @ii @which
BEGIN
IF (@String IS NULL OR @Delimiter IS
NULL )
BEGIN
SELECT @Substring=''
BREAK
END
IF CHARINDEX(@Delimiter,@String) = 0
BEGIN
SELECT @subString = @string
SELECT @String=''
END
ELSE
BEGIN
SELECT @subString = SUBSTRING( @String, 1, CHARINDEX( @Delimiter, @String )-1)
SELECT @String = SUBSTRING
( @String, CHARINDEX( @Delimiter, @String )+LEN(@delimiter),LEN(@String))
END
SELECT @ii=@ii+1
END
RETURN (@subString)
END