Thank you for the explanation, Jeff.

I was getting errors when trying to make use of BCP or BULK INSERT with that format and I wondered what I was doing wrong.

Best...

Nice question.

I thought there was a catch somewhere but there wasn't. 😀

Thank you.

Best regards,

That's brilliant! Thank you.

Bookmarked. 🙂

Best regards,

Indeed. You're absolutely right.

There's something I'd like to ask...

mendesm (10/5/2011)

Caro compatriota,

Yes, there were generalizations in there, but my point wasn't that if one is not qualified without experience, but rather that one can be very qualified, without experience,...

IMHO you should start being curious about the dba side of your company even if it doesn't have a proper DBA. And as many have stated, some DBAs start out...

mendesm (10/5/2011)

---

Don't take this personally, but when a resume comes my way with "MCITP/MCTS" and other MS certifications I pass the resume on to the trash barrel.

These days, the more...

Jeff Moden (10/3/2011)

The BCP format file would use similar delimiters and row terminators as you have in your BULK INSERT example (which is very nicely done, BTW). Perhaps if...

Jeff Moden (10/2/2011)

---

Look at the example the OP gave, Andre... each element is on a single row with row indicators between "whole records". You don't need a splitter for...

Jeff Moden (10/2/2011)

---

codebyo (10/1/2011)

---

Now if you need to pass the filename as a variable you won't be able to use OPENROWSET that way. It needs a constant value as filename.

But...

Jeff Moden (10/2/2011)Same goes with conversions to VARCHAR(MAX) which is serious overkill for 7 digit conversions. In many cases, just switching from VARCHAR(n) to VARCHAR(MAX) will cause performance to...

Jeff Moden (10/2/2011)

---

codebyo (9/27/2011)

---

Hmm. What do you mean? September 1st is Thursday, not the first day of the week.

Day of week would be like this:

SELECT CASE DATEPART(WEEKDAY, '20110901')

...

How about registering a linked server?

Then you could do something like this:

INSERT INTO LocalServerName.DatabaseName.SchemaName.TableName(<columns>)

SELECT <columns> FROM LinkedServerName.DatabaseName.SchemaName.TableName;

More information here: http://msdn.microsoft.com/en-us/library/ms188279.aspx

And here: http://msdn.microsoft.com/en-us/library/aa213778(v=sql.80).aspx

Best regards,

Now if you need to pass the filename as a variable you won't be able to use OPENROWSET that way. It needs a constant value as filename.

But you could do...

This may be one of the simplest ways to import a text file:

DECLARE @TxtData VARCHAR(MAX);

SELECT @TxtData = BulkColumn FROM OPENROWSET(BULK 'C:\textfile.txt', SINGLE_CLOB) AS T;

SELECT @TxtData;

But, as other have mentioned, you...