Alternate ways to append delimited text file to SQL Server

[Phil5673]

I have a situation in which I need to append data from comma-delimited text files to varying tables on SQL Server 2000. I am currently using Bulk Insert in a DTS Package to accomplish this. The problem is that some of the tables have primary key fields or Date fields that have default values and are not part of the file I am trying to import.

***********************************************************
I know that you get around this by using a format file
***********************************************************
however, my boss doesn't want to use a format file because he doesn't want to have to create one every time a new table is created.

All of my reasearch says that bulk insert is the way to go. What other options do I have that wouldn't involve a format file of some sort?

Oh, by the way, the method needs to be flexible enough so that one DTS Package (or stored proceedure) can handle appending to multiple tables in multiple databases possibly on multiple servers.

Thanks for your help.

Phil

 

[mrdenny]

There are three ways to get data from a text file into SQL Server DTS, BCP, and bulk insert.

I would recommend using bulk insert loading it into a temporary table, then from there moving the data into the production table.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[DrSql]

Denny suggestion is the best method that I seen out there, because easier to troubleshoot best way to minuplate the data.

Create a table


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ut_tmptbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ut_tmptbl]
GO

CREATE TABLE [dbo].[ut_tmptbl] (
[data] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


Then execute this to import the data from your file to you table.
[tt]
insert into ut_time1 exec master..xp_cmdshell 'type C:\Imports\File.txt'
[/tt]
Now you can create a cursor to minuplate the data and insert into any tables as you desire...



Dr.Sql
Good Luck.

 

[Phil5673]

Thanks for the quick reply, guys. I'll give that method a shot.