How do I bulk insert only a partial record into a MS SQL db with a stored proc.?
Posted on 2010-08-16
I need to bulk insert 450 student profiles into a MS SQL db. The student profile table contains 48 fields. The school has provided a tab delimited file but only the first 12 fields have data. Mapping the insert for all 48 fields is tedious and is complicated as 1 error can throw the entire insert off. For a simple test of the bulk insert I created a table and text (tab delimited) file. The db is on a unix system. The code below is the code that worked for inserting 3 fields. How would I modify it to add a record including the other 9 fields...
...without having to map all 48?
Code that works for bulk insert 3 fields, unix system:
ALTER PROCEDURE [dbo].[uspImportStudentInfoNoDuplicates]
-- Add the parameters for the stored procedure here
-- @p1 int = 0,
-- @p2 int = 0
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE ImportStudentTempTable
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT ImportStudentTempTable
FROM ''\\*********Location of txt file'
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
-- Insert the new student records
INSERT INTO ImportDataTest
SELECT last_name, first_name, middle_name
DROP TABLE ImportStudentTempTable