text file into SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

text file into SQL

Author

Topic

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 03:18:13

How would I go about importing data from a txt file into my database?
I download a daily text file.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 03:32:40

There is a number of ways

1. BCP
2. DTS / SSIS
3. OPENROWSET and others

E 12°55'05.63"
N 56°04'39.26"

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 04:15:02

Thank you Peso,
I googled for 'BCP' and came across a perfect explanation at http://blogs.techrepublic.com.com/datacenter/?p=317&tag=rbxccnbtr1
which explained things beautifully.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 04:17:15

Oh yes, and there is also BULK INSERT.

E 12°55'05.63"
N 56°04'39.26"

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-23 : 04:25:01

If you will be doing it daily then SSIS is your best bet.

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 04:55:44

BULK INSERT within a daily running job will be fine too.

E 12°55'05.63"
N 56°04'39.26"

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-23 : 05:12:23

SSIS too have BULK INSERT transformation.

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 06:53:57

I cannot find decent place that explain SSIS.

So far, I have got data imported with a

CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int],
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
and then with

BULK INSERT SalesHistory FROM 'c:\SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',')

But I have still got problems because I write query of each of these and Visual Web Developer says it is unsupported syntax. It runs, and makes the table, but it cannot be saved. So I am a little lost.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 07:09:19

I cannot use SSIS because my edition is Express.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:13:37

Make it a stored procedure instead?

E 12°55'05.63"
N 56°04'39.26"

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 16:19:36

ok I will try to learn that. Thanks for the headsup.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 16:41:38

Oh Peso, man of the Yaks - Thank you!!

I have just written my first, and second Stored procedures.
CreateTable and Insert from txt file
Executed them.
Zero error.
Perfect!
You are the man.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 21:18:36

I have another question regarding this.
I have written the stored procedure. Actually written 4. Can I combine them in one?
like this...

CREATE PROCEDURE getImport

AS
--Create Import Table
CREATE TABLE [dbo].[Import]
([PlayerID] [int] NULL, ................. )

--Bulk Insert into Import from text file

BULK INSERT ImportSquad FROM 'C:\Documents and Settings\User\...................\Import.txt' WITH (FIELDTERMINATOR = ',')
RETURN

--Insert into Table1 from Import

INSERT INTO Table1 (PlayerID, .............................)
SELECT PlayerID, ........................
FROM Import

--Insert into Table2 from Import

INSERT INTO Table2 (PlayerID, .............................)
SELECT PlayerID, ........................
FROM Import

--Insert into Table3 from Import

INSERT INTO Table3 (PlayerID, .............................)
SELECT PlayerID, ........................
FROM Import

--Insert into Table4 from Import

INSERT INTO Table4 (PlayerID, .............................)
SELECT PlayerID, ........................
FROM Import

--Clean up and drop temporary table Import

DROP TABLE Import

RETURN

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-23 : 21:24:01

After BULK INSERT I have a RETURN which must not be there... mistake.. sorry!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources