Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
monkeypoop
Starting Member
1 Post |
 Posted - 2003-03-02 : 12:15:21
|
| Hi,I am trying to Import some data with a DATETIME header record of when the file was created...My question to all is how do I go about inserting this header info into the table as a row into every record contained in the imported file...Hope this is clear...ORIGINAL FILE TO IMPORT:0120020115 21:20:24.0000123,hello,555,goodbye124,mark,847,seeyaI would like this in a table:123,hello,555,goodbye,0120020115 21:20:24.0000124,mark,847,seeya,0120020115 21:20:24.0000Any ideas or have you done this before...Any help would be appreciated...Mark |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-02 : 13:08:02
|
| If you use two separate staging tables:CREATE TABLE #header (filedate varchar(30))CREATE TABLE #data (ID int, col1 varchar(20), col2 int, col3 varchar(20))You can use BULK INSERT to import each section into its respective table:BULK INSERT #header FROM 'C:\mydata.txt' WITH (ROWTERMINATOR='\n', FIELDTERMINATOR='', FIRSTROW=1, LASTROW=1)BULK INSERT #data FROM 'C:\mydata.txt' WITH (ROWTERMINATOR='\n', FIELDTERMINATOR=',', FIRSTROW=2, LASTROW=10000000)Once that's done, it's a straight INSERT into the final table:INSERT INTO myTable (ID, col1, col2, col3, dateCol)SELECT ID, col1, col2, col3, filedateFROM #data CROSS JOIN #headerSince #header will only contain one row, it's no big deal to CROSS JOIN it with the #data table. This may not work though, because the first row does not contain the FIELDTERMINATOR character, it might confuse the bulk loader.In that case, you can BULK INSERT the entire file into a single-column, varchar(8000) staging table and then parse each row properly using any of the CSV parsing routines here:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv |
 |
|
|
|
|
|
|
|
Import datetime header info into table???