?
Solved

How do I bulk insert only a partial record into a MS SQL db with a stored proc.?

Posted on 2010-08-16
4
Medium Priority
?
495 Views
Last Modified: 2012-06-27
Dear experts,

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...
             [phone_number]  nchar(25),
             [student_id]    int,
             [ethnicity]     nchar(25),
             [gender]        nchar(10),
             [date_of_birth] smalldatetime,
             [grade]         smallint,
             [he_she]        nchar(10),
             [him_her]       nchar(10),
             [his_hers]      nchar(10),  

...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
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;


CREATE TABLE ImportStudentTempTable
(last_name varchar(50),
first_name varchar(50),
middle_name varchar(50)
)

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT ImportStudentTempTable
FROM ''\\*********Location of txt file'
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)


-- Insert the new student records
INSERT INTO ImportDataTest
   SELECT last_name, first_name, middle_name
   FROM ImportStudentTempTable


DROP TABLE ImportStudentTempTable

END
0
Comment
Question by:bobbellows
  • 2
  • 2
4 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 total points
ID: 33445300

ALTER PROCEDURE [dbo].[uspImportStudentInfoNoDuplicates]
      -- Add the parameters for the stored procedure here
--      @p1 int = 0,
--      @p2 int = 0
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;


CREATE TABLE #ImportStudentTempTable
(last_name varchar(50),
first_name varchar(50),
middle_name varchar(50),
[phone_number]  nchar(25),
[student_id]    int,
[ethnicity]     nchar(25),
[gender]        nchar(10),
[date_of_birth] smalldatetime,
[grade]         smallint,
[he_she]        nchar(10),
[him_her]       nchar(10),
[his_hers]      nchar(10)
)

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT ImportStudentTempTable
FROM ''\\*********Location of txt file'
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)


-- Insert the new student records
INSERT INTO tblName
(last_name,first_name,middle_name,[phone_number],[student_id],
 [ethnicity],[gender],[date_of_birth],[grade],[he_she],[him_her],[his_hers])
   SELECT * -- all 12 fields
   FROM ImportStudentTempTable


DROP TABLE #ImportStudentTempTable

END

Open in new window

0
 

Author Comment

by:bobbellows
ID: 33451190
Your solution worked perfectly and I'll give you the points. I'm still learning about stored procedures having only written a few. Can you tell me why you used the "#" in front of the temp table name? Thanks. Bob
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33451203
Tables created with a name of # are temporary and live only while the session is active.
If they are created in a SP, you don't even need to drop them - they automatically disappear.
Other options are ## (these stay until the server is rebooted and live in tempdb) and @ - these are variables that work like a table.
It is not good practice to create a [real] table within an SP, even if you drop it, just for the sake of some temporary work.

Regards
0
 

Author Closing Comment

by:bobbellows
ID: 33451377
Did exactly what I needed to do and even answered a question to help me learn about Stored Procedures! Awesome
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Native ability to set a user account password via AD GPO was removed because the passwords can be easily decrypted by any authenticated user in the domain. Microsoft recommends LAPS as a replacement and I have written an article that does something …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question