i got some bulk insert tasks in SSIS inserting into some tables with identity set ON....
in 1 column. Can the bulk insert task go smoothly?
PS: i cannot find anywhere in the bulk insert task that can set the ignore identity columns...........
now my steps are prepare database -> create database -> bulk insert into tables
working on my previous problem, Jamie.
Open the Bulk Insert Task, select the Options page, select Options, open the drop down. Check "Enable Identity Insert".I am running a bulk load from a DTS package. I am able to insert the parent records succesfully into the article table, but I cannot get the child data to insert at all. The package returns an "executed succesfully" message, so I am having a tough time debugging. Here is my schema file, and a sample of the XML file I am trying to load. Please let me know if you spot anything in the schema I can fix (the xml file is supplied to me, and I have no control over it) All I am trying to insert in the child table (xmlTopic) is the Article_ID and the Topic_Type. Thanks in advance.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="ArticleTopic"
parent="xmlARTICLE"
parent-key="ARTICLE_ID"
child="xmlTOPIC"
child-key="ARTICLE_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="ARTICLE" sql:relation="xmlArticle" sql:key-fields="ARTICLE_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="ATTRIBUTION" type="xs:string" sql:field="ATTRIBUTION"/>
<xs:element name="BLURB" type="xs:string" sql:field="BLURB"/>
<xs:element name="BODY" type="xs:string" sql:field="ARTICLE_BODY"/>
<xs:element name="BRAND" type="xs:string" sql:field="BRAND"/>
<xs:element name="BYLINE" type="xs:string" sql:field="BYLINE"/>
<xs:element name="COPYRIGHT" type="xs:string" sql:field="COPYRIGHT"/>
<xs:element name="FEATURE_BLURB" type="xs:string" sql:field="FEATURE_BLURB"/>
<xs:element name="FEATURE_IMAGE" type="xs:string" sql:field="FEATURE_IMAGE"/>
<xs:element name="HEADLINE" type="xs:string" sql:field="HEADLINE"/>
<xs:element name="NEWS_TYPE" type="xs:string" sql:field="NEWS_TYPE"/>
<xs:element name="SOURCE" type="xs:string" sql:field="SOURCE"/>
<xs:element name="TEASER" type="xs:string" sql:field="TEASER"/>
<xs:element name="URL" type="xs:string" sql:field="URL"/>
<xs:element name="TOPICS" sql:mapped="false">
<xs:complexType>
<xs:sequence>
<xs:element name="TOPIC" sql:relation="xmlTopic" sql:relationship="ArticleTopic" sql:key-fields="xmlTopicID">
<xs:complexType>
<xs:attribute name="TOPIC_TYPE" type="xs:string" sql:field="TOPIC_TYPE"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ARTICLE_ID" type="xs:string" sql:field="ARTICLE_ID"/>
<xs:attribute name="POSTING_DATE" type="xs:string" sql:field="POSTING_DATE"/>
<xs:attribute name="ARCHIVE_DATE" type="xs:string" sql:field="ARCHIVE_DATE"/>
</xs:complexType>
</xs:element>
</xs:schema>
<?xml version="1.0"?>
<!DOCTYPE NEWSFEED SYSTEM "http://www.blah.com">
<NEWSFEED>
<ARTICLE ARTICLE_ID="531320" POSTING_DATE="08-Mar-2006" ARCHIVE_DATE="01-Mar-2007">
<NEWS_TYPE>blah</NEWS_TYPE>
<HEADLINE><![CDATA[blah]]></HEADLINE>
<BLURB><![CDATA[blah]]></BLURB>
<BYLINE><![CDATA[blah]]></BYLINE>
<BODY><![CDATA[blahblahblah]]></BODY>
<ATTRIBUTION><![CDATA[blah]]></ATTRIBUTION>
<SOURCE><![CDATA[blah]]></SOURCE>
<FEATURE_BLURB><![CDATA[blah]]></FEATURE_BLURB>
<FEATURE_IMAGE></FEATURE_IMAGE>
<TEASER><![CDATA[blah]]></TEASER>
<COPYRIGHT><![CDATA[blah]]></COPYRIGHT>
<BRAND><![CDATA[vlah]]></BRAND>
<URL><![CDATA[blah]></URL>
<TOPICS>
<TOPIC TOPIC_TYPE="BLPR"/>
<TOPIC TOPIC_TYPE="BLPR2"/>
</TOPICS>
</ARTICLE>
</NEWSFEED>
Well it turns out I answered my own question. I checked the MSDN Library(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp - see sql:relationship and the Key Ordering Rule), and found the following info:
"This means that in defining the <Customer> element, you must specify the CustomerID attribute in the schema before you specify <sql:relationship>. Otherwise, when an <Order> element enters into scope, XML Bulk Load generates a record for the CustOrder table; and when the XML Bulk Load reaches the </Order> end tag, it sends the record to SQL Server without the CustomerID foreign key column value."
So I put my sql:relationship at the end of the schema file, and it worked like a charm. As reference, here is my revised schema file:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ARTICLE" sql:relation="xmlArticle">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ATTRIBUTION" type="xsd:string" sql:field="ATTRIBUTION"/>
<xsd:element name="BLURB" type="xsd:string" sql:field="BLURB"/>
<xsd:element name="BODY" type="xsd:string" sql:field="ARTICLE_BODY"/>
<xsd:element name="BRAND" type="xsd:string" sql:field="BRAND"/>
<xsd:element name="BYLINE" type="xsd:string" sql:field="BYLINE"/>
<xsd:element name="COPYRIGHT" type="xsd:string" sql:field="COPYRIGHT"/>
<xsd:element name="FEATURE_BLURB" type="xsd:string" sql:field="FEATURE_BLURB"/>
<xsd:element name="FEATURE_IMAGE" type="xsd:string" sql:field="FEATURE_IMAGE"/>
<xsd:element name="HEADLINE" type="xsd:string" sql:field="HEADLINE"/>
<xsd:element name="NEWS_TYPE" type="xsd:string" sql:field="NEWS_TYPE"/>
<xsd:element name="SOURCE" type="xsd:string" sql:field="SOURCE"/>
<xsd:element name="TEASER" type="xsd:string" sql:field="TEASER"/>
<xsd:element name="URL" type="xsd:string" sql:field="URL"/>
<xsd:element name="TOPIC" sql:relation="xmlTopic" sql:relationship="ArticleTopic">
<xsd:complexType>
<xsd:attribute name="TYPE2" type="xsd:string" sql:field="TopicType"/>
<xsd:attribute name="TOPIC_TYPE" type="xsd:string" sql:field="TopicID"/>
<xsd:attribute name="SIGNIFICANCE_FACTOR" type="xsd:string" sql:field="SignificanceFactor"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ARTICLE_ID" type="xsd:string" sql:field="ARTICLE_ID"/>
<xsd:attribute name="POSTING_DATE" type="xsd:string" sql:field="POSTING_DATE"/>
<xsd:attribute name="ARCHIVE_DATE" type="xsd:string" sql:field="ARCHIVE_DATE"/>
</xsd:complexType>
</xsd:element>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ArticleTopic"
parent="xmlArticle"
parent-key="ARTICLE_ID"
child="xmlTopic"
child-key="ARTICLE_ID" />
</xsd:appinfo>
</xsd:annotation>
</xsd:schema>
sqlhi
hi i have common problem.
which is the best way to insert 1000 rows at a time in sql server 2000.
From where you want to insert?
You can use the following approaches
apporach 1:
Use bcp to backup the data on text file (delimited file) - from soruce server
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
Use BULK INSERT to reload the data on the target server
http://msdn2.microsoft.com/en-us/library/ms188365.aspx
approach 2:
Use DTS to load the data from one server to another server
http://msdn2.microsoft.com/en-us/library/aa902640(SQL.80).aspx
approach 3:
Use linked server to get the source data on the target server
http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx
|||If the data is in a delimited text file, use bcp; it's quick and flexible.
Read up on [bcp] in Books Online.
guid | field1 | field2
fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
In my destination table I have three column:
id (uniqueidentifier)
field1 (nvarchar)
field2 (nvarchar)
I use in bulk insert a format file like this :
9.0
3
1SQLNCHAR00"|\0"1IDLatin1_General_CI_AS
2SQLNCHAR00"|\0"2Field1Latin1_General_CI_AS
3SQLNCHAR00"|\0"3Field2Latin1_General_CI_AS
and I use this script
BULK INSERT [dbo].[KWTA2] FROM 'd:\WTA2.txt'
WITH (FORMATFILE = 'd:\wta2Format.FMT')
It doesn't work, it prints out
Msg 8152, Level 16, State 13, Line 2
String or binary data would be truncated.
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
Please can you help me?
Why sql converts alphanumerical GUID into only numbers ID?
How can I bulk insert GUID? (I didn't find anything googling around :
\ )
Thanks!
Bob(bob.speaking@.gmail.com) writes:
Quote:
Originally Posted by
I'm trying to bulk insert a uniqueidentifier column from unicode file.
In my file I have guid generated from c# application and they are
formatted in this way (separated by "|") :
>
guid | field1 | field2
fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2
>
In my destination table I have three column:
id (uniqueidentifier)
field1 (nvarchar)
field2 (nvarchar)
>
I use in bulk insert a format file like this :
>
9.0
3
1 SQLNCHAR 0 0 "|\0" 1 ID Latin1_General_CI_AS
2 SQLNCHAR 0 0 "|\0" 2 Field1
Quote:
Originally Posted by
3 SQLNCHAR 0 0 "|\0" 3 Field2
Does the file really consist of one single line?
Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.
Quote:
Originally Posted by
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>
Quote:
Originally Posted by
Does the file really consist of one single line?
>
Assuming that you have one record per line in the file, the terminator
for field 3 should be \r\0\n\0. What happens now is that Field2 in the
first record extends into the GUID in the second record, and then it
goes downhill from there.
Quote:
Originally Posted by
Quote:
Originally Posted by
I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHAR
and it works perfectly but it imports another data. For example the
guid fc0c0c42-438e-4897-96db-8b0489e873ef became
00350031-0039-0033-3100-300030003000
Thanks for the prompt reply :)
bobsql
Th@.nks to all
AB@.AB@. (b.aharon44@.gmail.com) writes:
Quote:
Originally Posted by
I have a problem about a importation of a file *.csv with SQL Server,
through a bulk insert, called in a store procedure that a c# sw calls.
This is the description of the error:
--
System.Data.SqlClient.SqlException stata individuata
Message="Bulk Insert: Unexpected end-of-file (EOF) encountered in
data file.\r\nOLE DB provider 'STREAM' reported an error. The provider
did not give any information about the error.\r\nOLE DB error trace
[OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
The provider did not give any information about the error.].\r\nThe
statement has been terminated."
1) The BULK INSERT statement.
2) Any format file you are using.
3) A short sample of the data file.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Apr, 23:40, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
AB@. (b.aharo...@.gmail.com) writes:
Quote:
Originally Posted by
I have a problem about a importation of a file *.csv with SQL Server,
through a bulk insert, called in a store procedure that a c# sw calls.
This is the description of the error:
--
System.Data.SqlClient.SqlException stata individuata
Message="Bulk Insert: Unexpected end-of-file (EOF) encountered in
data file.\r\nOLE DB provider 'STREAM' reported an error. The provider
did not give any information about the error.\r\nOLE DB error trace
[OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
The provider did not give any information about the error.].\r\nThe
statement has been terminated."
1) We sometimes get files that are cut off prematurely and a line
will only be a fraction completed. This will fail a bulk-insert.
2) Sometimes an extra carriage return is at the end of the file.
I've seen this fail the bulk-insert with an Unexecpected EOF message.
3) Sometimes I just couldn't figure out the answer and using DTS
instead of bulk insert resolved the problem.
I hope that helps somebody. :D
-Utah
Ok so I got this working:
BULK INSERT
dbo.tbCheckPointTest
FROM 'c:\inetpub\wwwroot\upload\maxie_task_aging.csv'
WITH
(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',
FIRSTROW = 2, ROWS_PER_BATCH = 6, ROWTERMINATOR = '\n')
My data file looks like this:
Total of 6 rows, so I indicate to start on Row 2, yet only the last three lines are getting into my table.
I have changed and manipulated the query varies counts, but still just the last three lines get imported.
Thoughts?
Thanks!
Hi,
From the looks of your data file, it is difficult to tell how many blank lines are associated with your data file.
It appears that "Research" may begin on line 4, in which case you would have only three rows of data: "Research", "State", and "Program".
I am confused by the fact that you get the last three rows: "State", "Program", and "Program Tech".
Is it possible that some rows are terminated by "0A0D" and others only by "0A" or "0D", and "\n" is being interpreted as strictly "0A0D" (hexadecimal)?
I also am confused by your use of "FIELDTERMINATOR = ',' ", since I don't see any commas in the data you have displayed.
Dan
|||Sorry. I had the .csv open with excel when I copied and pasted it:
REPORT_EXECUTED_ON:20070404_110001
Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160
I am going to see if anything you suggested will help.
|||The problem seems to be that the BULK INSERT function seems to expect all the rows in the file to have the correct number of fields for the target table (even those being skipped). So it is ignoring the newlines until it has found enough commas (and then counts any other commas until it finds the row terminator as part of that field).
Try loading the file:
REPORT_EXECUTED_ON:20070404_110001,,,
,,,
Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160
Though you will need a FIRSTROW = 3
If you put the last field into a varchar rather than an int then if you add commas to the end (e.g. 2635,,,,) then you will find that these commas end up in that field (if not then it will complain about unexpected characters when it tries to convert it to an int or whatever).
So any rows to be skipped should have the correct number of field separators in them (3) or they will not be included in the skip count.
|||Thank you, that solved the issue. Now I have to get w/the programmer who delivers the file to me.
Nice catch, Dhericean!
Dan
I read in Microsoft SQL Server 2005 Integration Services by Kirk Haselden that the BULK INSERT task was provided for backward compatibility and its use is disrecommended.
But after looking on the web I cannot find information supporting this.
Do you think the BULK INSERT task should be used for new development?
Thanks
It's not currently marked for deprecation. It may be in the future, because using the data flow and SQL Server Destinations or using the "fast load" option on the OLE DB destinations will be much more flexible.I would avoid using the Bulk Insert Task simply because of its rigid restrictions and the fact that it requires a CSV formatted file. That's way too limiting for me.|||
I agree with you, the Data flow task is much more flexible. And it performed better than the BULK INSERT in my case...
But the big advantage of the BULK INSERT (and some see it as a disadvantage) is that it uses Format Files.
In our case, we have flat files from about 20 providers. There's is no way of suppling the Data flow task with a transformation schema, so that would mean creating/maintaining 20 different Data flow tasks (each in a different package).
Another implementation would be to use a generic SSIS with a Bulk Insert Task taking as parameters the source/destination and a format file. In that case, we would have to create/maintain 20 different format files.
So I'm currently weighting each option. The restrictions of the BULK INSERT such as the flat file requirement are not a problem in this case. Performance is at a certain extent. Usability, deployment and maintenance are the most important criteria.
Thanks!
|||You might want to provide feedback over on connect.microsoft.com and share your feelings.Perhaps Matt Masson, Michael Entin, or others from MS can jump in and share their opinions or even MS' official stance.|||
Are you suggesting I ask them about the BULK INSERT being deprecated or about adding format file option to the Data flow task? I'm not sure...Phil Brammer wrote:
You might want to provide feedback over on connect.microsoft.com and share your feelings. Perhaps Matt Masson, Michael Entin, or others from MS can jump in and share their opinions or even MS' official stance.
What I would want is a place to seek advice on implementation. There's always many ways to do the same thing, especially when using T-SQL and SSIS. But do I post in the T-SQL forum or in the SSIS forum?
|||Are you suggesting I ask them about the BULK INSERT being deprecated or about adding format file option to the Data flow task? I'm not sure...
What I would want is a place to seek advice on implementation. There's always many ways to do the same thing, especially when using T-SQL and SSIS. But do I post in the T-SQL forum or in the SSIS forum?
I'm suggesting that you post your feedback (via https://connect.microsoft.com/SQLServer/feedback)on how you want the BULK INSERT task to stick around and not be deprecated.
As far as which forum, well, that just depends on the scenario. Don't be too worried about which forum to post in as if we feel it needs to be moved, there are a few of us here that can do that accordingly.|||
There may have been deprecation discussions when Kirk was originally writing his book, but as of now, there are no plans to deprecate the Bulk Insert task.
As previously mentioned, you get more flexibility using the data flow approach, and if you’re able to use the “fast load” option, you might even get a 10-15% performance increase over the BULK INSERT tasks. You can weigh out both options, but as Phil has suggested, using a data flow is generally the recommended approach.
|||I think the inflexibility is a decision point between using the Data Flow over the Bulk Insert Task and whilst I would normally use Data Flow perhaps more out of habit, I think fleo's point about being able to load files of any format, by dynamically setting properties is very valid.
It is something that cannot be achieved with the Data Flow task, and should not be underestimated. It can be a very powerful tool, and one reason why many people use the T-SQL equivalent or even BCP still. I just like being able to get that functionality within the SSIS framework so I can use configurations and logging and all that good stuff.
Use it and save time, and of course Matt has responded in the positive too!
|||hI Matt,
so the Flat File Source has better performance than the BULK INSERT Task...
Where can I found this kind of information?
Also about the deprecation topic, why hasn't it written in the MSDN documentation?
Thank you
|||Hi Antonio,
Unfortunately, the 10-15% performance increase that I quoted wasn't from any official testing or documentation... I believe it came from a developer from an internal group that was testing out both methods. There's typically a lot of variables involved in these tests, so I'd recommend trying out both approaches yourself and seeing which works best in your situation.
I'm not sure what you're asking about the deprecation topic. I wasn't able to find any official reference to the task being deprecated when I first looked into this, and MSDN typically doesn't explicitly mention that a task is NOT going to be deprecated.
Thanks,
~Matt
I read in Microsoft SQL Server 2005 Integration Services by Kirk Haselden that the BULK INSERT task was provided for backward compatibility and its use is disrecommended.
But after looking on the web I cannot find information supporting this.
Do you think the BULK INSERT task should be used for new development?
Thanks
It's not currently marked for deprecation. It may be in the future, because using the data flow and SQL Server Destinations or using the "fast load" option on the OLE DB destinations will be much more flexible.I would avoid using the Bulk Insert Task simply because of its rigid restrictions and the fact that it requires a CSV formatted file. That's way too limiting for me.|||
I agree with you, the Data flow task is much more flexible. And it performed better than the BULK INSERT in my case...
But the big advantage of the BULK INSERT (and some see it as a disadvantage) is that it uses Format Files.
In our case, we have flat files from about 20 providers. There's is no way of suppling the Data flow task with a transformation schema, so that would mean creating/maintaining 20 different Data flow tasks (each in a different package).
Another implementation would be to use a generic SSIS with a Bulk Insert Task taking as parameters the source/destination and a format file. In that case, we would have to create/maintain 20 different format files.
So I'm currently weighting each option. The restrictions of the BULK INSERT such as the flat file requirement are not a problem in this case. Performance is at a certain extent. Usability, deployment and maintenance are the most important criteria.
Thanks!
|||You might want to provide feedback over on connect.microsoft.com and share your feelings.Perhaps Matt Masson, Michael Entin, or others from MS can jump in and share their opinions or even MS' official stance.|||
Are you suggesting I ask them about the BULK INSERT being deprecated or about adding format file option to the Data flow task? I'm not sure...You might want to provide feedback over on connect.microsoft.com and share your feelings. Perhaps Matt Masson, Michael Entin, or others from MS can jump in and share their opinions or even MS' official stance.
What I would want is a place to seek advice on implementation. There's always many ways to do the same thing, especially when using T-SQL and SSIS. But do I post in the T-SQL forum or in the SSIS forum?
|||Are you suggesting I ask them about the BULK INSERT being deprecated or about adding format file option to the Data flow task? I'm not sure...
What I would want is a place to seek advice on implementation. There's always many ways to do the same thing, especially when using T-SQL and SSIS. But do I post in the T-SQL forum or in the SSIS forum?
I'm suggesting that you post your feedback (via https://connect.microsoft.com/SQLServer/feedback)on how you want the BULK INSERT task to stick around and not be deprecated.
As far as which forum, well, that just depends on the scenario. Don't be too worried about which forum to post in as if we feel it needs to be moved, there are a few of us here that can do that accordingly.|||
There may have been deprecation discussions when Kirk was originally writing his book, but as of now, there are no plans to deprecate the Bulk Insert task.
As previously mentioned, you get more flexibility using the data flow approach, and if you’re able to use the “fast load” option, you might even get a 10-15% performance increase over the BULK INSERT tasks. You can weigh out both options, but as Phil has suggested, using a data flow is generally the recommended approach.
|||I think the inflexibility is a decision point between using the Data Flow over the Bulk Insert Task and whilst I would normally use Data Flow perhaps more out of habit, I think fleo's point about being able to load files of any format, by dynamically setting properties is very valid.
It is something that cannot be achieved with the Data Flow task, and should not be underestimated. It can be a very powerful tool, and one reason why many people use the T-SQL equivalent or even BCP still. I just like being able to get that functionality within the SSIS framework so I can use configurations and logging and all that good stuff.
Use it and save time, and of course Matt has responded in the positive too!
|||hI Matt,
so the Flat File Source has better performance than the BULK INSERT Task...
Where can I found this kind of information?
Also about the deprecation topic, why hasn't it written in the MSDN documentation?
Thank you
|||Hi Antonio,
Unfortunately, the 10-15% performance increase that I quoted wasn't from any official testing or documentation... I believe it came from a developer from an internal group that was testing out both methods. There's typically a lot of variables involved in these tests, so I'd recommend trying out both approaches yourself and seeing which works best in your situation.
I'm not sure what you're asking about the deprecation topic. I wasn't able to find any official reference to the task being deprecated when I first looked into this, and MSDN typically doesn't explicitly mention that a task is NOT going to be deprecated.
Thanks,
~Matt
sqlI have no problem importing a file.txt to my table (mehet).
Bulk Insert mehet From 'C:\test.txt'
With (DataFileType = 'char', FIELDTERMINATOR = ',')
But I would appreciated if someone could help me how to import only 1 or 2 columns.
instead of all columns.
Thanks.
juvan
hi,
you can perhaps have a look at format file support, that can be used by BULK operations..
define a file to be imported, say
d:\imp\data.txt like
[d:\imp\data.txt]
Andrea,Montanari,5
Juvan,Juvan,2
[/d:\imp\data.txt]
you can then use a format file to specify the required mappings... define a first one like
[fmt.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 3 Id ""
[/fmt.txt]
define another fmt file like
[fmt2.txt]
9.0
3
1 SQLCHAR 0 10 "," 1 lName ""
2 SQLCHAR 0 10 "," 2 fName ""
3 SQLCHAR 0 7 "\r\n" 0 Id ""
[/fmt2.txt]
all included columns will be mapped to a column of the target table (see SQL script);
SET NOCOUNT ON;USE tempdb;
GO
CREATE TABLE dbo.Test (
fName varchar(10),
lName varchar(10) DEFAULT 'not set',
Id int DEFAULT -1000
);
GO
BULK INSERT dbo.Test
FROM 'd:\imp\Data.txt'
WITH (formatfile='d:\imp\fmt.txt');
SELECT * FROM dbo.Test;
GO
PRINT 'modify the fmt file putting 0 in the ID column as fmt2.txt';
BULK INSERT dbo.Test
FROM 'd:\imp\Data.txt'
WITH (formatfile='d:\imp\fmt2.txt');
SELECT * FROM dbo.Test;
GO
PRINT 'just use an INSERT ... specifying the required colums';
SELECT lName
FROM OPENROWSET(BULK 'd:\imp\Data.txt',
FORMATFILE='d:\imp\fmt2.txt'
) AS t1;
INSERT INTO dbo.Test (fName)
SELECT lName
FROM OPENROWSET(BULK 'd:\imp\Data.txt',
FORMATFILE='d:\imp\fmt2.txt'
) AS t1;
SELECT * FROM dbo.Test;
GO
DROP TABLE dbo.Test;
--<-
fName lName Id
- - --
Andrea Montanari 5
Juvan Juvan 2
modify the fmt file putting 0 in the ID column as fmt2.txt
fName lName Id
- - --
Andrea Montanari 5
Juvan Juvan 2
Andrea Montanari -1000
Juvan Juvan -1000
just use an INSERT ... specifying the required colums
lName
-
Andrea
Juvan
fName lName Id
- - --
Andrea Montanari 5
Juvan Juvan 2
Andrea Montanari -1000
Juvan Juvan -1000
Andrea not set -1000
Juvan not set -1000
as you can see, you can play with the fmt file as required, excluding unwanted colums at the retrival source time, or later if you just use an INSERT SELECT FROM OPENROWSET..
regards
|||Hi Andrea,
Thanks for your help. I got it right thanks to you.
I have one more question, if you don't mind?
Sometimes I go out of town where there has no internet. It is possible to connect to my Database at home by using
phoneline direct? If there is a way, Can you let me know please.
Thanks.
Juvan
|||hi,
you can connect over the net to your server from your home pc... but a direct SQL Server service open over the internet is not that secure... you should eventually read about V(irtual) P(rivate) N(etwork) connections to protect your server and not to allow someone to just open a connection over your TCP/IP xxx-xxx-x-xxx IP address ...
stay secure as you can
regards
|||hi,
Yes, but as I have said that the area where I go to has no internet connection at all. So in this case what should I do?
Thanks.
Juvan
quote:
BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM
'data_file' }...
'data_file'
Is the full path of the data file that contains data to copy into the
specified table or view. BULK INSERT can copy data from a disk (including
network, floppy disk, hard disk, and so on).
data_file must specify a valid path from the server on which SQL Server is
running. If data_file is a remote file, specify the Universal Naming
Convention (UNC) name.
Quote:
Originally Posted by
Does sql server have a way to handle errors in a sproc which would allow
one to insert rows, ignoring rows which would create a duplicate key
violation? I know if one loops one can handle the error on a row by row
basis. But is there a way to skip the loop and do it as a bulk insert?
It's easy to do in Access, but I'm curious to know if SQL Server proper
can handle like this. I am guessing that a looping operation would be
slower to execute?
In the latter case, use
INSERT tbl
SELECT ...
FROM src
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.keycol = src.keycol)
which should be the normal way to do it in Access - or any other SQL engine
for that matter - as well.
If you are specifically talking bulk load from file, then above is still
possible in SQL 2005 if you use OPENROWSET(BULK) as the table source. If
you use BULK INSERT or BCP (the only options on SQL 2000), I believe it's
possible by using the IGNORE_DUP_KEY option on the index, but a more
common procedure is to load the file to staging table and move on from
there.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. I meant a regular sql operation. I will take a look at
your suggestion, it looks good. Access has another way of handling this
(non-sql) and while the syntax you suggest probably works in Access,
I've never tried it.
