Bulk insert using stored procedure

by

There’s nothing wrong with your stored procedure code – the point is: the BULK INSERT command cannot accept a file name as a variable.

This does work:

BULK INSERT ZIPCodes 
FROM  'e:\5-digit Commercial.csv' 
WITH

but this never works – within a stored proc or not:

DECLARE @filename VARCHAR(255)
SET @filename="e:\5-digit Commercial.csv" 

BULK INSERT ZIPCodes 
FROM @filename
WITH

So you just cannot do it this way, unfortunately. You could consider building up your BULK INSERT statement as a string (with a fixed file name) and then execute it as dynamic SQL – but I don’t really see any other solution.

DECLARE @filepath nvarchar(500)
SET @filepath = N'e:\5-digit Commercial.csv'

DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert = 
       N'BULK INSERT ZIPCodes FROM ''' + 
       @filepath + 
       N''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'

EXEC sp_executesql @bulkinsert

More Related Contents:

  1. SQL Server Insert if not exists
  2. Split values over multiple rows [duplicate]
  3. Procedure or function !!! has too many arguments specified
  4. Stored procedure that exports data into csv files only exports to one file
  5. SQL Server: should I use information_schema tables over sys tables?
  6. How to execute a stored procedure inside a select query
  7. Check if a parameter is null or empty in a stored procedure
  8. to select all the employees who have joined most recently under king
  9. How can I delete using INNER JOIN with SQL Server?
  10. Convert varchar into datetime in SQL Server
  11. sql query to return differences between two tables
  12. SQL Joins Vs SQL Subqueries (Performance)?
  13. Check if a row exists, otherwise insert
  14. Execute Stored Procedure from a Function
  15. formula for computed column based on different table’s column
  16. SQL comments on create table on SQL Server 2008
  17. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  18. SQL Server 2008- Get table constraints
  19. How does this CASE expression reach the ELSE clause?
  20. SQL Server: Cannot insert an explicit value into a timestamp column
  21. Execute Dynamic Query with go in sql
  22. is of a type that is invalid for use as a key column in an index
  23. TransactSQL to run another TransactSQL script
  24. How can I generate a temporary table filled with dates in SQL Server 2000?
  25. How can I get a list of element names from an XML value in SQL Server
  26. Why are logical reads for windowed aggregate functions so high?
  27. Comma Separated values with SQL Query
  28. Select SQL Server database size
  29. Cannot Resolve Collation Conflict
  30. Display next event date

Leave a Comment