| Author |
Topic |
|
vwilsonjr
Starting Member
45 Posts |
 Posted - 2003-04-01 : 11:38:40
|
| I have a flat file with date fields in it. I'm trying to import it into a table. When I use DTS it barks at the date field during the translation. Any suggestion. Here is an example of the text fileJohn Smith 10/20/52Table Design is FName varchar 25LName varchar 25DOB datetime 8 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-01 : 11:59:02
|
| Why use dts to import a flat file?You can do it in one line of sql using bulk insert if it's simple.I would always import into a staging table then transfer (ok that's 2 sql commands) and you can reformat the (char) date while doing that.==========================================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. |
 |
|
|
vwilsonjr
Starting Member
45 Posts |
Posted - 2003-04-01 : 12:09:16
|
| Can you direct me where I can find out how to do that? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-01 : 12:09:38
|
| create table ##a (fname varchar(25), lname varchar(25), dte varchar(10))exec master..xp_cmdshell 'bcp ##a in e:\tst.txt -c -t" "'select fname, lname, convert(datetime,dte,1)from ##adrop table ##a==========================================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. |
|
|
|
vwilsonjr
Starting Member
45 Posts |
Posted - 2003-04-01 : 12:12:30
|
| Real dump question, I havent done much in sql. Is ##a = table name? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-01 : 12:35:34
|
| global temp table so can be created in the sp.you might want a permanent one.==========================================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. |
|
|
|
vwilsonjr
Starting Member
45 Posts |
Posted - 2003-04-01 : 13:11:56
|
| Thanks for all your help. Could you explain the "dte varchar(10)" What does the dte mean. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-01 : 13:31:49
|
| dte is just a column name. It could be named anything.You really need to have a look at the CREATE TABLE statement and the SELECT statement in SQL Server Books Online so that you can decipher what everything means.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-01 : 13:31:55
|
| dte is the name of the field.You can call it DOB if you wish.==========================================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. |
|
|
|
|
Data Import