Maybe you can populate the datetime column after it's bulk inserted. Something like below where you'd play around with "Convert" function to match the format of the the string column1 + column2.
UPDATE table SET concactenatedDateTimeColumn = CONVERT(datetime, column1 + column2) - Gabriel On Thu, Feb 11, 2010 at 11:41 AM, Ian Skinner <[email protected]> wrote: > > Not a ColdFusion specific question, other then that I will write some > ColdFusion templates to report the data once I have it imported. But > the HOF groups are just such a great resource. Repeated here as the SQL > list seems a bit quite. > > It has been quite some time since I have worked with SQL Server. I > would like to import a large (~673MB) text log file into a table. The > fields in the text file are space[ ] deliminated. The first two fields > are a date string and a time string. I would like to combine these two > fields into a single datetime field in the database table. I am finding > this surprisingly difficult to do. There does not seem to be any way to > concatenate the first two fields of the text file into one field with > the SQL Server Import and Export Wizard in the SQL Server Management Studio. > > I have a bulk loader example that would theoretically import this data > as well, but it also assumes a one to one relationship between the text > fields and the table fields. > BULK INSERT dprmisc.iis.weblog1 FROM 'c:\IIS Tools\preplog\allsites.log' > WITH ( FIELDTERMINATOR = ' ', ROWTERMINATOR = '\n' ) > > I could probably write a SQL insert statement, but I don't know how to > access a flat text file with SQL inside the Management Studio tools. > > Any help or sugestions would be appreciated, or do I just have to give > up on this dream and seperate the date and the time values in the > database table? > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330599 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
