Bulk insert into 4000+ tables from 4000+ files with corresponding name

Question

I have a folder with 4000+ pipe delimited files of which are named the same as tables in a schema.

I'm trying to do this

DECLARE @tableName varchar(max)
DECLARE @sqlCommand varchar(max)
DECLARE @path varchar(20)
SET @path =  'x:\xfer\'

DECLARE tNames CURSOR
FOR
    SELECT table_name FROM information_schema.tables
    WHERE TABLE_TYPE='BASE TABLE'
    AND TABLE_NAME = 'sales_time_pd'
    ORDER BY TABLE_NAME

OPEN tNames
    FETCH  FROM tNames
        INTO @tableName

        WHILE @@FETCH_STATUS = 0
            BEGIN

               SET @sqlCommand = 'BULK INSERT @tableName
               FROM @path + @tableName
               WITH 
                  (
                     FIELDTERMINATOR ='' |'',
                     ROWTERMINATOR ='' |\n''
                  );'

                EXEC(@sqlCommand)


                FETCH NEXT FROM tNames
                into @tableName
            END
CLOSE tNames;   
DEALLOCATE tNames;

I am looking for the concept roughly please excuse my incorrect use of apostrophes

As a test I am targeting a single file/table to do the bulk insert. I have learned that table names need to be static.

So how can I bulk insert into a collection of tables from a directory of files with a corresponding name?

if all else fails, you could try to use command line BCP and not use your tsql loop code — KM., Jan 14 '15 at 16:13
What part of this code in specific are you having trouble with? — BVernon, Jan 14 '15 at 16:14
@KM He could accomplish the same thing with a batch file and bcp, but I don't know that it would be any less work. — BVernon, Jan 14 '15 at 16:14
Thanks guys. I cannot dynamically set table names such as BULK INSERT #myTable or FROM #myTable — Splunk, Jan 14 '15 at 16:16
simple brute force method: in dos you can do: `dir > yourfile` to create a file containing all your file names, use an editor to convert each line of "yourfile" into a BCP command, and then you can run that edited "yourfile" to BCP everyhing — KM., Jan 14 '15 at 16:18
@BVernon I could use other tools. I am exploring what I can and cannot do with MSSQLMS. Moving from Informix AIX to SQL Server. — Splunk, Jan 14 '15 at 16:18
is is possible that you are not properly building the dynamic SQL? try something like: `SET @sqlCommand = 'BULK INSERT '+@tableName+' FROM '''+@path + @tableName+''' ...` verify you have a properly concatenated the sql command by replacing `EXEC(@sqlCommand)` with `print @sqlCommand` — KM., Jan 14 '15 at 16:23
@KM Thanks, but I have learned from this very community that this is not possible. See http://stackoverflow.com/questions/2838490/table-name-as-variable — Splunk, Jan 14 '15 at 16:27
@dotnetnewb the code you list in your question won't work, just like the code in your linked question. The code in my comment will build a proper sql string that can be executed. by building your SQL command in a string you make the table names static. — KM., Jan 14 '15 at 16:31
KM is right, the problem with your code is that you can't select from a variable table name. However, you can construct a query string that uses the variable name and execute that. (I know I'm just repeating what KM just said, but just wanted to make sure it was clear what he was saying). — BVernon, Jan 14 '15 at 18:30
Alternatively, if you want to try the batch file route, you can check out this link: http://stackoverflow.com/questions/4663928/batch-script-loop-through-file-names. And google something like 'dos batch commands' for more info. — BVernon, Jan 14 '15 at 18:33
@KM please leave an answer. Thanks for clarifying guys and helping me understand. Much appreciated. — Splunk, Jan 19 '15 at 09:21

score 1 · Accepted Answer · answered Jan 19 '15 at 13:33

You look like you are concatenating the SQL string incorrectly try something like:

SET @sqlCommand = 'BULK INSERT '+@tableName+' FROM '''+@path + @tableName+'''

If you use special characters within your tab;e name you can also try:

SET @sqlCommand = 'BULK INSERT ['+@tableName+'] FROM '''+@path + @tableName+'''

You can verify you have a properly concatenated the sql command by replacing EXEC(@sqlCommand) with print @sqlCommand. Take the printed command and try running it, if you get an error, take the message and look into what it means and try to fix your syntax.

By dynamically building a SQL string you take the @Variable table name and make it a static literal that is concatenated into the command.

Bulk insert into 4000+ tables from 4000+ files with corresponding name

1 Answers1