470,831 Members | 1,313 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,831 developers. It's quick & easy.

Bulk Insert with default value

We have sp's in place that do BULK INSERTS from txt files into the tables. This works fine, however they have asked us to add a field that identifies accounting ctr. The only thing that identifies accounting ctr is the last three letters of the text file being used for the BULK INSERT.

How would you suggest that I do this? Is there a way to add a default value to the .fmt or schema files for the text fields, or in the BULK INSERT statement itself?

Below is part of the process using now:
BULK INSERT DSLTesting..ExtractCRB
FROM '\\server\c$\DSLReporting\crbcfl.txt'
WITH (FORMATFILE = '\\server\c$\DSLReporting\crb.fmt')
BULK INSERT DSLTesting..ExtractCRB
FROM '\\server\c$\DSLReporting\crbctt.txt'
WITH (FORMATFILE = '\\server\c$\DSLReporting\crb.fmt')
BULK INSERT DSLTesting..ExtractCRB
FROM '\\server\c$\DSLReporting\crbflg.txt'
WITH (FORMATFILE = '\\server\c$\DSLReporting\crb.fmt')

What I need to add to new field in ExtractCRB (ACCTCTR) is CFL, CTT, FLG during the bulk inserts. This data is not in the .txt file being inserted, only identified by the last three letters of the .txt file being processed. So while it's importing the first file (crbcfl.txt) the value for that field will be 'CFL', then change for the appropriate .txt import throughout the process. Hope this makes sense!

Help?!?
Apr 9 '07 #1
0 2765

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by me | last post: by
20 posts views Thread by akej via SQLMonster.com | last post: by
6 posts views Thread by pk | last post: by
3 posts views Thread by Davy B | last post: by
11 posts views Thread by Ted | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.