I am working on some exciting project but my problem is not exciting and I have not been able to find a valid solution... (all related fix are arround BCP or BULK INSERT).
The server where the DB is located is not allowing BCP / Bulk insert command ... this is why I need to use the OPENDATASOURCE command. Also, the PIPE symbole is something that is forced from our IT dep and I can't influence this...
How can I load a set of data (here in 5 collumns) when the each data is separated by a pipe symbole?
I am loading the data in the table variable @tablename that is created before the command and has bellow format:
If it loads all your data in a single record, single column, then I'm guessing that your file doesn't terminate lines correctly (CRLF) and SQL can't figure out where the line ends, so it considers it one big line.
Try setting up a file manually and give it a go. If it gets inserted, then try to fix the source of your file, or you'll be forced to get it into a field and use a sproc to cut it into the appropriate fields.
Thank you for the feedbacks. This is still not working with the opendatasource command however, I have been able to load data with the bulk insert by using a format file that looks like this (for other devellopers not to stay hungry):