| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-09 : 08:04:22
|
| Joe writes "What is the best way to import flat files into sql db with no delimiter or separator involved." |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-09 : 08:14:29
|
| 1) DTS, text file (source). This is the easiest way to do it. You can use the Data import/export wizard in SQL server enterprise manager.2) BCP. Way cooler but harder to set up ;) |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-09 : 10:56:30
|
| 2) BCP. Way cooler but harder to set up ;)BCP. Way cooler and easier to set up (and faster to run)in fact one line of sql.exec master..xp_cmdshell 'bcp mydb..mytbl in c:\myfile -Sservername -Uusr -Ppwd -c -t|'==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-09 : 11:57:08
|
| Nigel,Don't you need a format file with a fixed with dataset?Brett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-09 : 17:18:08
|
Oops - missed the no delimitter - assumed line feeds. .==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-11 : 15:49:46
|
| It's harder to set up IF you are used to graphical interfaces. Just like EM is easier to use for beginners who are used to graphical interfaces. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-12 : 11:53:40
|
| Yeah, but...gui's aren't repeatable...for example if you want to alter a table and add a column..1,2,3 done, but you have to do it again if you want to in another db.Now a script...well there's a thing of beauty...written once and ctrl+E as often as you like....Not to mention cutting and pasting...plus you'll never have.."how did I do that again?" It's all saved (or should be) as a *.sql fileBIG MOO here I guess.Brett8-)Edited by - x002548 on 05/12/2003 11:54:35 |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-13 : 02:30:42
|
| I'm using BCP myself Brett so don't try to convince me :)I'm just remembering how easy it was to get started with DTS. It's data transformation in the simplest (but not best) form I have seen so far. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-13 : 10:26:08
|
Kalle,quote:
I'm using BCP myself Brett so don't try to convince me :)
Didn't think I was trying to convince anyone...thought I was just stating the obvious... Brett8-) |
|
|
|
dan78vr
Starting Member
4 Posts |
Posted - 2003-05-14 : 03:57:31
|
| Hi, I'm using a DTS to export from a SQL Server to .txt files. I have a problem when I try to make a transformation. Number of columns from source is 16, and it does't work when it should create the destination in the .txt file . I think that it's a problem about number of columns for .txt files. Do you know any trick?Thanx,dan |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-14 : 04:17:53
|
| Hi Dan!It should work. I just did it with 116 columns, worked fine. A couple of questions for you:1) What error message do you get?2) What version of SQL Server are you running, and with what service pack? |
|
|
|
dan78vr
Starting Member
4 Posts |
Posted - 2003-05-14 : 04:45:57
|
Hi,I'm using MSSql 2000.Hi,I'm using MSSQL Server 2000 with the last service pack.I'm into DTS design , I chose a source (MSSQL Server ) and a destination (.txt file). I apply a Transform Data Task from MSSQL to .txt file. I do Transform Data Task Propertis to putt the code of transact sql into SQL query textarea( like --> "select * from (select fiels1, field2 from tbl_nameunion select fiels1, field2 from tbl_name2where ..unionselect fiels1, field2 from tbl_name3) aliaswhere ..."), I done pase query, Source is Ok. And when I chose Destination to Define Columns and I try read columns, with Poputation Source, and when I maxe execute, it dosn't work and I get an error (about violation memory).It's very stange because the my "SELECT code" is ok, and I sow that I've less columns , the destination it's ok. Thanx,danquote:
Hi Dan!It should work. I just did it with 116 columns, worked fine. A couple of questions for you:1) What error message do you get?2) What version of SQL Server are you running, and with what service pack?
|
|
|
|
dan78vr
Starting Member
4 Posts |
Posted - 2003-05-14 : 06:44:49
|
Hi,I tried to using wizard export from MSSQL server 2000 to .txt , using up to 46 columns and it's work very well and I'm sure that's work with many more columns, but when I'm using the same operation into my DTS package it's doesn't work. If you dont't believe, try it! Try you to crete a simple package DTS where you've a export from MSSQL 2000 to .txt file. When you define the transformation , putt a Select with 46 columns. Tell me what happened about destination mapping ?Grazie mille,danquote:
Hi Dan!It should work. I just did it with 116 columns, worked fine. A couple of questions for you:1) What error message do you get?2) What version of SQL Server are you running, and with what service pack?
|
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-05-14 : 07:06:28
|
| Hi again Dan. I did a select of 112 columns from a table, destination mapping, no problem. Transform into a text file, worked great.Maybe you should reinstall your client tools or something? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-14 : 10:25:41
|
| It's hard to believ.I've used the wizard, built them from scratch...found some weird things when trying to change connection objects (it seemed to remeber the "Old" connection and still used it instead), but never what your talking about...Stopped using it anyway...Anyway, if you've used the wizard, did you save the package?If you did you can go in and alter the transformations...see what happens then.Still though...manualy creating a dump of a table to a text file should be a breeze...Can you list, step by step, what you're doing?Brett8-) |
|
|
|
dan78vr
Starting Member
4 Posts |
Posted - 2003-05-14 : 11:48:43
|
Hi,Ok, Try this:1. You create a new package DTS on sql server 2000. 2. create a source connection sqlserver. 3. create a destination .txt file4. define a transformation data task properties5. you Set the transformation data task properties on tab source with a select like "SELECT field1,..,field40 from tbl_Person", up to 40 columns.6. you set the transformation data task properties on tab destination and chose define columns, click on populate on source and click on execute and Tell me what do you see? Can you make the transformation?NB: It is true that with wizard Import/export my problem it's ok.I don't use wizard Import/export, because for doesn't solve my problem into my context, I mean that I've a relativ complex DTS package that expect many parameters from ASP page and it will be executed by an ASP page.Thanx for all,danquote:
It's hard to believ.I've used the wizard, built them from scratch...found some weird things when trying to change connection objects (it seemed to remeber the "Old" connection and still used it instead), but never what your talking about...Stopped using it anyway...Anyway, if you've used the wizard, did you save the package?If you did you can go in and alter the transformations...see what happens then.Still though...manualy creating a dump of a table to a text file should be a breeze...Can you list, step by step, what you're doing?Brett8-)
|
|
|
|
|
Flatfile Import