December 11, 2008 at 8:59 am
I am new here. I did a forum search first but did not find anything that can help me.
I am converting my database from MySQL to SQL server 2005. Normally I use a LODA DATA INFILE statement to dump a text file into a table in MySQL. This command does not seem to work in SQL 2005. Is there an alternative to this? Here is the command I am using now:
LOAD DATA INFILE 'C:\PATH\TO\FILE.txt' INTO TABLE 'mytable' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r'
There is also a "backslash n" after the "\r" in the code but for some reason the forums don't like that code and they are taking out of the post.
I hope someone can help me with this.
Thanks in advance for your help.
December 11, 2008 at 9:13 am
replace "slash r" and "slash n" with the command that the forum obviously hates.
--the TSQL equivilent would be this command: note this assumes the table already exists,
and the number of columns in the table match the number of fields delimited by the field terminator
BULK INSERT mytable FROM 'C:\PATH\TO\FILE.txt' '
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',' , --typically this is a comma or tab
ROWTERMINATOR = 'slash n', --or slash r if it's coming from unix/linix
FIRSTROW = 2 --assuming the first row had column names...if not firstrow=1
)
Lowell
December 11, 2008 at 9:21 am
Thank you very very much for the fast reply. It works great and does what I need it to.
Thanks again.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy