Как Вставить ВСТАВИТЬ файл в * временную таблицу, где имя файла является переменной?

У меня есть такой код, который я использую для выполнения BULK INSERT файла данных в таблице, где файл данных и имя таблицы являются переменными:

DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'BULK INSERT ' + @tableName + ' FROM ''' + @filename + ''' WITH (CODEPAGE=''ACP'', FIELDTERMINATOR=''|'')'

EXEC (@sql)

Работает нормально для стандартных таблиц, но теперь мне нужно сделать то же самое, чтобы загружать данные во временную таблицу (например, #MyTable). Но когда я пытаюсь это сделать, я получаю сообщение об ошибке:

Invalid Object Name: #MyTable

Я думаю, проблема связана с тем, что оператор BULK INSERT строится "на лету", а затем выполняется с помощью EXEC и что #MyTable недоступен в контексте вызова EXEC.

Причиной того, что мне нужно построить инструкцию BULK INSERT, как это, является то, что мне нужно вставить имя файла в оператор, и это, кажется, единственный способ сделать это. Таким образом, кажется, что я могу либо иметь имя файла переменной, либо использовать временную таблицу, но не оба.

Есть ли другой способ достижения этого - возможно, используя OPENROWSET(BULK...)?

UPDATE: Хорошо, так что я слышу, что BULK INSERT и временные таблицы не будут работать для меня. Спасибо за предложения, но перемещение большего количества моего кода в динамическую часть SQL в моем случае нецелесообразно.

Пробовав OPENROWSET(BULK...), кажется, что это страдает от одной и той же проблемы, то есть не может иметь дело с переменным именем файла, и мне нужно будет динамически строить инструкцию SQL по-прежнему (и, следовательно, не иметь доступа к temp table).

Итак, это оставляет мне только один вариант, который должен использовать таблицу без temp и добиться изоляции процесса по-другому (гарантируя, что только один процесс может использовать таблицы в любой момент времени - я могу думать о несколько способов сделать это).

Это раздражает. Было бы гораздо удобнее делать это так, как я изначально планировал. Просто одна из тех вещей, которые должны быть тривиальными, но в конечном итоге есть целый день вашего времени...

Ответы

Ответ 1

Можно делать все, что вы хотите. Ответ Аарона был не совсем полным.

Его подход правильный, вплоть до создания временной таблицы во внутреннем запросе. Затем вам нужно вставить результаты в таблицу во внешнем запросе.

Следующий фрагмент кода захватывает первую строку файла и вставляет его в таблицу @Lines:

declare @fieldsep char(1) = ',';
declare @recordsep char(1) = char(10);

declare @Lines table (
    line varchar(8000)
);

declare @sql varchar(8000) = ' 
    create table #tmp (
        line varchar(8000)
    );

    bulk insert #tmp
        from '''[email protected]+'''
        with (FirstRow = 1, FieldTerminator = '''[email protected]+''', RowTerminator = '''[email protected]+''');

    select * from #tmp';

insert into @Lines
    exec(@sql);

select * from @lines

Ответ 2

Вы всегда можете построить таблицу #temp в динамическом SQL. Например, прямо сейчас, я думаю, вы пытались:

CREATE TABLE #tmp(a INT, b INT, c INT);

DECLARE @sql NVARCHAR(1000);

SET @sql = N'BULK INSERT #tmp ...' + @variables;

EXEC master.sys.sp_executesql @sql;

SELECT * FROM #tmp;

Это делает его более жестким для поддержания (удобочитаемости), но получает проблему:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'CREATE TABLE #tmp(a INT, b INT, c INT);

BULK INSERT #tmp ...' + @variables + ';

SELECT * FROM #tmp;';

EXEC master.sys.sp_executesql @sql;

EDIT 2011-01-12

В свете того, как мой почти двухлетний ответ был внезапно признан неполным и неприемлемым, кем-то, чей ответ был также неполным, как насчет:

CREATE TABLE #outer(a INT, b INT, c INT);

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON; 

CREATE TABLE #inner(a INT, b INT, c INT);

BULK INSERT #inner ...' + @variables + ';

SELECT * FROM #inner;';

INSERT #outer EXEC master.sys.sp_executesql @sql;

Ответ 4

Извините, что выкопал старый вопрос, но если кто-то наткнулся на эту тему и хочет получить более быстрое решение.

Массовая вставка файла неизвестной ширины с \n ограничителями строк в таблицу temp, созданную вне инструкции EXEC.

DECLARE     @SQL VARCHAR(8000)

IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
BEGIN
    DROP TABLE #BulkInsert
END

CREATE TABLE #BulkInsert
(
    Line    VARCHAR(MAX)
)

SET @SQL = 'BULK INSERT #BulkInser FROM ''##FILEPATH##'' WITH (ROWTERMINATOR = ''\n'')'
EXEC (@SQL)

SELECT * FROM #BulkInsert

Дальнейшая поддержка того, что динамический SQL внутри оператора EXEC имеет доступ к временным таблицам вне инструкции EXEC. http://sqlfiddle.com/#!3/d41d8/19343

DECLARE     @SQL VARCHAR(8000)

IF OBJECT_ID('TempDB..#BulkInsert') IS NOT NULL
BEGIN
    DROP TABLE #BulkInsert
END

CREATE TABLE #BulkInsert
(
    Line    VARCHAR(MAX)
)
INSERT INTO #BulkInsert
(
    Line
)
SELECT 1
UNION SELECT 2
UNION SELECT 3

SET @SQL = 'SELECT * FROM #BulkInsert'
EXEC (@SQL)

Дополнительная поддержка, написанная для MSSQL2000 http://technet.microsoft.com/en-us/library/aa175921(v=sql.80).aspx

Пример внизу ссылки

DECLARE @cmd VARCHAR(1000), @ExecError INT
CREATE TABLE #ErrFile (ExecError INT)
SET @cmd = 'EXEC GetTableCount ' + 
'''pubs.dbo.authors''' + 
'INSERT #ErrFile VALUES(@@ERROR)'
EXEC(@cmd)
SET @ExecError = (SELECT * FROM #ErrFile)
SELECT @ExecError AS '@@ERROR'