troubleshooting Question

Importing txt file into SQL workbench

Avatar of nshusky
nshusky asked on
MySQL Server
25 Comments1 Solution3519 ViewsLast Modified:
I am trying to import a pipe delimited txt file into a new table that gets created in SQL Workbench.  I have written the following routine but it is erroring out on the DROP TABLE line.  Any suggestions would be great.

DELIMITER '|'

CREATE PROCEDURE OnBoard_testImportScript ()

BEGIN
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Postal_Place_Test]') AND type in (N'U'))
DROP TABLE dbo.Postal_Place_Test
CREATE TABLE Postal_Place_Test
(ZIP5 Varchar (5),
STATE2 Varchar (2),
STATE_ABBREV Varchar (2),
COUNTY5 Varchar (5),
COUNTY3 Varchar (3),
COUNTYNAME Varchar (25),
PLACENAME Varchar (28),
ZIP_TYPE Varchar (8),
MAILING_INDICATOR Varchar (1),
RESIDENTIAL_FLAG Varchar (1),
COMMUNITY_FLAG Varchar (1),
PREFD_PLACEKEY Varchar (6),
PREFD_PLACENAME Varchar (26),
ONBOARD_DATE Varchar (15))
-- Load data into table
BULK
INSERT Postal_Place_Test
FROM 'C:\Documents and Settings\Mike\Desktop\OnBoard\JC_zip-place-lkp_JUN13.txt'
WITH
(
FIRSTROW = 2, -- Removes the Header Row
FIELDTERMINATOR = '|',
ROWTERMINATOR = ''
)
END
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 25 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 25 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros