September 10, 2003 at 2:06 pm
I have a requirement where I need to BCP out the data from database (from all 1100 tables) into individual tables along with the format file (for BCP) & deliver to another group.
I could generate the flat files using generating BCP statements, then executing the BCP script. But I don't know :
1. how to generate along with the format files in a single shot for all the tables.
2. Some fields in some tables has NULL values. The users need this to be filled with spaces.
Can someone help???
THANKS IN ADVANCE.
.
September 10, 2003 at 3:07 pm
As far as I know there is no way to automate or script the task to create the fmt files. If you're familiar with FMT files, .. er, lemme put it this way. You could either write all 1100 format files, in which case you will become familiar with format files. Or, you could bcp out and answer all 1100 series of prompts... OR write a query that loops through information_schema.columns view for each table and does some string building to create these (this one being my choice)
Heh. None of these are less than tedious sorry. Another idea would be to take a glance at the script library here. I know I've seen a script bouncing around that does this exact exercise already - I'm just not sure where I've seen it.
Scripting this still won't alleviate your problem of having to create 1100 individual format files. if you go the scripting route, you may want to pick up that extended stored procedure I've seen here that let's you write to text files. Still, bleh. Sounds like a lot of work.
-Ken
September 10, 2003 at 4:15 pm
Thanks spongemagnet for your response. I even saw this kinda script earlier. But didn't remember where... I tried searching thru the script library here, but .... :-((
Sill by 2nd question remains OPEN.
Can someone please help??
.
September 10, 2003 at 4:24 pm
This seems to me like it might better be accomplished with DTS. Especially since you want to transform the NULL data to have spaces. Other than what spongemagnet stated I don't have any better ideas.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 10, 2003 at 4:26 pm
since this is SQL Server 6.5, I can't use DTS.
.
September 11, 2003 at 6:03 am
Is 6.5 the only version you have?
Far away is close at hand in the images of elsewhere.
Anon.
September 11, 2003 at 8:02 am
No. But in the server's domain this is the only 6.5 server. We have few 2000 & 7.0 servers in other domains.
.
September 11, 2003 at 8:09 am
Does your network/security allow one of the sql 2000/7.0 servers to access the 6.5 server, if so than you can use DTS to retrieve data from the 6.5 server.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply