Journey to SQL Authority

[wurzel]

The Personal Notes of Pinal Dave.....

Link




I've just wasted 2 hours trying out one of his solutions

[Spacecadet]

I don't usually have time for MVP's they tend to just regurgitate the help files or spew out regurgitated MS sales pitches of products they have spent no longer than 5 minutes with.

From what I've seen of him, Pinal Dave, is one of the few exceptions who does actually spend some time investigating and passing some proper first hand experience on.

Which of his solutions were you having problems with?

[wurzel]

I don't usually have time for MVP's they tend to just regurgitate the help files or spew out regurgitated MS sales pitches of products they have spent no longer than 5 minutes with.

From what I've seen of him, Pinal Dave, is one of the few exceptions who does actually spend some time investigating and passing some proper first hand experience on.

Which of his solutions were you having problems with?

His articles on Bulk importing from CSV into SQl Server. I want to skip the identity column. Though, to be fair, the actual problem is in the design:

from Microsoft:

With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. However, you can import into all but the last column of a table. If you have to skip any but the last column, you must create a view of the target table that contains only the columns contained in the data file. Then, you can bulk import data from that file into the view.

What a load of pony.

[OwlHoot]

His articles on Bulk importing from CSV into SQl Server. I want to skip the identity column. Though, to be fair, the actual problem is in the design:

from Microsoft:

With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. However, you can import into all but the last column of a table. If you have to skip any but the last column, you must create a view of the target table that contains only the columns contained in the data file. Then, you can bulk import data from that file into the view.

What a load of pony.

Wouldn't there some simple way of importing selectively from Excel, or writing a transformed CSV file that could be imported more easily.

Failing that, one could knock up a perl script in five minutes to do it.

[Spacecadet]

His articles on Bulk importing from CSV into SQl Server. I want to skip the identity column. Though, to be fair, the actual problem is in the design:

from Microsoft:

With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. However, you can import into all but the last column of a table. If you have to skip any but the last column, you must create a view of the target table that contains only the columns contained in the data file. Then, you can bulk import data from that file into the view.

What a load of pony.

As your quote from microsoft says:
bulk insert into a view which is a select on the table for all columns minus the identity column

[Spacecadet]

Wouldn't there some simple way of importing selectively from Excel, or writing a transformed CSV file that could be imported more easily.

Failing that, one could knock up a perl script in five minutes to do it.

If your data source is good you can create an SSIS task to do it in literally seconds. If the data source is bad then it might take as long as 2 minutes

[NorthWestPerm2Contr]

If your data source is good you can create an SSIS task to do it in literally seconds. If the data source is bad then it might take as long as 2 minutes

To date I have not seen a data problem which SSIS can't deal with. When it's too complicated just use a script task and code it to do what you want, whatever the source of the data may be.

[wurzel]

No SSIS here.... it's SQL express. Going with the view solution at the moment. Thanks.

This all has to be performed from within an MSI file. Got the joys of Wix files to deal with next.

[Spacecadet]

To date I have not seen a data problem which SSIS can't deal with. When it's too complicated just use a script task and code it to do what you want, whatever the source of the data may be.

You've not pushed it hard enough then
I hit a problem this morning whilst adding some lookups. I wanted to compare several columns in order to the lookup table - which itself is about 30 million rows.
SSIS fell over trying to cache the data and not caching took way to long.
Ended up having to move that functionality into SQL.

[NorthWestPerm2Contr]

You've not pushed it hard enough then
I hit a problem this morning whilst adding some lookups. I wanted to compare several columns in order to the lookup table - which itself is about 30 million rows.
SSIS fell over trying to cache the data and not caching took way to long.
Ended up having to move that functionality into SQL.

True say, I was referring to complexity rather than performance. You may be force to moving the data to SQL for it to deal with.