Comment insérer en bloc un fichier dans une table * temporaire * où le nom de fichier est une variable?

J'ai un code comme celui-ci que j'utilise pour insérer en bloc un fichier de données dans une table, où le fichier de données et le nom de la table sont des variables: 

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

EXEC (@sql)

Le fonctionne bien pour les tables standard, mais maintenant je dois faire le même genre de chose pour charger des données dans une tabletemporaire (par exemple, #MyTable). Mais quand j'essaie ceci, j'obtiens l'erreur: 

Invalid Object Name: #MyTable

Je pense que le problème est dû au fait que l'instruction BULK INSERT est construite à la volée puis exécutée en utilisant EXEC, et que #MyTable n'est pas accessible dans le contexte de la {[4],} appeler.

La raison pour laquelle j'ai besoin de construire l'instruction BULK INSERT comme ceci est que j'ai besoin d'insérer le nom de fichier dans l'instruction, et cela semble être la seule façon de le faire. Donc, il semble que je puisse soit avoir un nom de fichier variable, ou utiliser une table temporaire, mais pas les deux.

Y a - t-il un autre moyen d'y parvenir-peut-être en utilisant OPENROWSET(BULK...)?

Mise à jour: OK, alors ce que j'entends, c'est que les tables D'insertion et temporaires en vrac ne vont pas fonctionner pour moi. Merci pour les suggestions, mais déplacer plus de mon code dans la partie SQL dynamique n'est pas pratique dans mon cas.

Après avoir essayé OPENROWSET(BULK...), Il semble que cela souffre du même problème, c'est-à-dire qu'il ne peut pas gérer un nom de fichier variable, et j'aurais besoin de construire l'instruction SQL dynamiquement comme avant (et donc ne pas pouvoir accéder à la table temporaire).

Donc, cela ne me laisse qu'une seule option qui consiste à utiliser une table non-temp et à réaliser l'isolement du processus d'une manière différente (en veillant à ce qu'un seul processus puisse utiliser les tables à la fois - je peux penser à plusieurs façons de le faire).

C'est agaçant. Il aurait été beaucoup plus pratique de le faire comme je l'avais initialement prévu. Juste une de ces choses qui devraient être triviales, mais finit par manger une journée entière de votre temps...

25
demandé sur Gary McGill 2010-03-04 20:53:56

4 réponses

Il est possible de faire tout ce que vous voulez. La réponse d'Aaron n'était pas tout à fait complète.

Son approche est correcte, jusqu'à créer la table temporaire dans la requête interne. Ensuite, vous devez insérer les résultats dans une table dans la requête externe.

L'extrait de code suivant attrape la première ligne d'un fichier et l'insère dans le tableau @Lignes:

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 '''+@filename+'''
        with (FirstRow = 1, FieldTerminator = '''+@fieldsep+''', RowTerminator = '''+@recordsep+''');

    select * from #tmp';

insert into @Lines
    exec(@sql);

select * from @lines
12
répondu Gordon Linoff 2015-01-12 17:05:26

Vous pouvez toujours construire la table # temp dans dynamic SQL. Par exemple, je suppose que vous avez essayé:

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;

Cela rend plus difficile à maintenir (lisibilité) mais obtient par le problème de portée: 

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;

Modifier 2011-01-12

À la lumière de la façon dont ma réponse de presque 2 ans a été soudainement jugée incomplète et inacceptable, par quelqu'un dont la réponse était également incomplète, Que diriez-vous: 

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;
15
répondu Aaron Bertrand 2015-01-15 21:19:52

Http://msdn.microsoft.com/en-us/library/ms191503.aspx

Je conseillerais de créer une table avec un nom unique avant l'insertion en bloc.

0
répondu Andrey 2010-03-04 18:18:21

Désolé de déterrer une vieille question mais au cas où quelqu'un trébuche sur ce fil et veut une solution plus rapide.

Insertion En bloc d'un fichier de largeur inconnue avec \ n terminateurs de ligne dans une table temporaire créée en dehors de L'instruction 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

Support supplémentaire que SQL dynamique dans une instruction EXEC a accès aux tables temporaires en dehors de L'instruction 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)

Support supplémentaire, écrit pour MSSQL2000 http://technet.microsoft.com/en-us/library/aa175921 (v=sql.80).aspx

Exemple en bas du lien 

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'
0
répondu deeg 2013-08-22 17:31:25