0

This feels like an ‘obvious “yes” ‘ kind of question, but nevertheless: would it not be possible with OPENROWSET to have sub-queries regarding the file path that import for particular procedurally-named files, such as: [Bus] + ‘-‘ + [School] + ‘-‘ + [Time (AM/MD/PM)] + ‘.xml’ ?

So for...

CREATE TABLE Route (Bus (int, 4), School (string, 4), Time (string, 2) )
INSERT INTO Route (0804,CRES,PM),(0758,CRES, MD),(1106,LHS,MD),(0804,CHMS,PM)
GO

CREATE TABLE Excel (Name (char,16))
INSERT INTO Excel (0804-CRES-PM.xml,0758-BVES-AM.xml,1106-LHS-MD.xml)
GO

UPDATE SeatingChart
FROM OPENROWSET (...’[Bus] + ‘-‘ [School] + ‘-‘ + [Route] + ‘.xml’
SET [Route]Seat = Excel.[Route]Seat
WHERE Name = [Bus] + ‘-‘ + [School] + ‘-‘ + [Time] + ‘.xml’

The file being imported from has columns for both the AM & PM seating charts in the same spreadsheet, so I’m not sure know how to about writing the generalized statement that will input into the

2 Answers 2

1

You could in theory do this, but it would require building the update statement with dynamic SQL. Basically, you declare a string variable, build it with metadata from the database, and then execute that string. Erland Sommarskog has an epic post on this: The Curse and Blessings of Dynamic SQL.

However, that's gonna require a lot of work and a lot of debugging.

Plus, for performance reasons, you don't really wanna fire a new OPENROWSET for every row of a table you're updating.

Instead, I'd suggest doing a single OPENROWSET call to pull all of the file contents into a temp table, and then doing your update statement from the temp table.

1

To specifically address your question you can dynamically update OPENROWSET. Below I use T-SQL's REPLACE and EXECUTE (using your UPDATE pseudo code). Should you? That's probably another discussion alltogether:

CREATE TABLE Route (Bus (int, 4), School (string, 4), Time (string, 2) )
INSERT INTO Route (0804,CRES,PM),(0758,CRES, MD),(1106,LHS,MD),(0804,CHMS,PM)
GO

CREATE TABLE Excel (Name (char,16))
INSERT INTO Excel (0804-CRES-PM.xml,0758-BVES-AM.xml,1106-LHS-MD.xml)
GO

declare @cmdstring varchar(255)
set @cmdstring =
'UPDATE SeatingChart
FROM OPENROWSET (...[Bus]-[School]-[Route].xml
SET [Route]Seat = Excel.[Route]Seat
WHERE Name = [Bus]-[School]-[Time].xml'

select @cmdstring = replace(@cmdstring,'[Bus]','1106')
select @cmdstring = replace(@cmdstring,'[School]','LHS')
select @cmdstring = replace(@cmdstring,'[Time]','PM')

EXECUTE(@cmdstring)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.