Help? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help?

Author

Topic

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-03-22 : 00:56:24

Hi,

I need a help to automatically import the data from txt (, seprated) file from a specific folder at regular interval into the sqlserver 2000 database table.

Is this possiable?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:01:02

yes.
There are many ways to do this
1. BCP
2. BULK INSERT
3. SSIS (2005) or DTS (2000)

Are you using SQL Server 2000 or 2005 ? Please post is correct forum or you may get advice that is only working on that version.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 01:29:49

4. OPENQUERY, OPENROWSET

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:31:20

"at regular interval"
Is it a good idea to use OPENQUERY, OPENROWSET for this requirement ?

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-03-22 : 01:53:44

Would you please give me a example, how to inser data from text file to the database using OPENROWSET or OPENQUERY at regular interval.

Thank you very much in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:58:38

why don't you take a look at Books OnLine. There are example provided there.
On the scheduling you can schedule it using SQLAgent.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 02:23:45

In SQL Server 2005, you must use "Surface Area Configuration" to allow use of AD HOC queries such as OPENROWSET.
Try with BULK INSERT.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-22 : 02:24:41

Dupe http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81034

Peter Larsson
Helsingborg, Sweden

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-03-22 : 06:47:34

BULK INSERT OrdersBulk
FROM 'c:\file.csv'
WITH
(
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

What if file is present on remote server, we have to give http path?

quote:
Originally posted by Peso

In SQL Server 2005, you must use "Surface Area Configuration" to allow use of AD HOC queries such as OPENROWSET.
Try with BULK INSERT.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 07:42:27

i don't think it support http path. Copy the file to local drive and then BULK INSERT it

pankaj.pareek@sparrowi.co
Starting Member

26 Posts

Posted - 2007-03-23 : 01:58:53

Hi,

I am using bulk insert to import a text file into a Table.

The text file has less columns than the table. I need to import the same column into two different table fields.

example:

input file:

field1,field2,field3

aa,bb,cc

Table fields:

field1,field2,field3,field4

aa,bb,bb,cc

I need to map as follows:

field1:field1

field2:field2

field2:field3

field3:field4

Can I do this using Bulk insert

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 04:16:20

use the format file. Take a look at BOL for more detail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources