bulk sql insert task can do tables with identity?

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".

Bulk Load vs Shredding

Do both methodologies achieve the same goal; migrating data from a given XML
element to its corresponding column in a relational table?
If that is correct, I would appreciate your comments asserting why one
methodology should be used rather than the other.
<%= Clinton Gallagher
METROmilwaukee "Regional Information Services"
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/XML Bulkload is generally faster and more scaling for large amounts of data.
However, it it less flexible, since it needs an annotated schema, whereas
OpenXML's shredding is more flexible with XPath, but since it loads all the
data apriori, is not as fast and scalable...
Best regards
Michael
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Do both methodologies achieve the same goal; migrating data from a given
> XML
> element to its corresponding column in a relational table?
> If that is correct, I would appreciate your comments asserting why one
> methodology should be used rather than the other.
>
> --
> <%= Clinton Gallagher
> METROmilwaukee "Regional Information Services"
> NET csgallagher AT metromilwaukee.com
> URL http://clintongallagher.metromilwaukee.com/
>|||Thank you. More reading has given me more information. No surprise there
huh? ;-)
I'm trying to kill several birds with as few stones as possible; using the
XmlTextWriter to write a file on the disk, get the data from that file and
map its data to table(s) in SQL Server and then based on a choice selected
in the form used to construct the XML apply a given XSLT to HTML or display
the raw XML in the browser.
<%= Clinton Gallagher
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e0g$XZh9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> XML Bulkload is generally faster and more scaling for large amounts of
data.
> However, it it less flexible, since it needs an annotated schema, whereas
> OpenXML's shredding is more flexible with XPath, but since it loads all
the
> data apriori, is not as fast and scalable...
> Best regards
> Michael
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
>

Bulk Load vs Shredding

Do both methodologies achieve the same goal; migrating data from a given XML
element to its corresponding column in a relational table?
If that is correct, I would appreciate your comments asserting why one
methodology should be used rather than the other.
<%= Clinton Gallagher
METROmilwaukee "Regional Information Services"
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
XML Bulkload is generally faster and more scaling for large amounts of data.
However, it it less flexible, since it needs an annotated schema, whereas
OpenXML's shredding is more flexible with XPath, but since it loads all the
data apriori, is not as fast and scalable...
Best regards
Michael
"clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Do both methodologies achieve the same goal; migrating data from a given
> XML
> element to its corresponding column in a relational table?
> If that is correct, I would appreciate your comments asserting why one
> methodology should be used rather than the other.
>
> --
> <%= Clinton Gallagher
> METROmilwaukee "Regional Information Services"
> NET csgallagher AT metromilwaukee.com
> URL http://clintongallagher.metromilwaukee.com/
>
|||Thank you. More reading has given me more information. No surprise there
huh? ;-)
I'm trying to kill several birds with as few stones as possible; using the
XmlTextWriter to write a file on the disk, get the data from that file and
map its data to table(s) in SQL Server and then based on a choice selected
in the form used to construct the XML apply a given XSLT to HTML or display
the raw XML in the browser.
<%= Clinton Gallagher
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e0g$XZh9EHA.3708@.TK2MSFTNGP14.phx.gbl...
> XML Bulkload is generally faster and more scaling for large amounts of
data.
> However, it it less flexible, since it needs an annotated schema, whereas
> OpenXML's shredding is more flexible with XPath, but since it loads all
the
> data apriori, is not as fast and scalable...
> Best regards
> Michael
> "clintonG" <csgallagher@.REMOVETHISTEXTmetromilwaukee.com> wrote in message
> news:%23aGRb9R9EHA.3708@.TK2MSFTNGP14.phx.gbl...
>

bulk inserting uniqueidentifier column

Hi at all,
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
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

Latin1_General_CI_AS

Quote:

Originally Posted by

3 SQLNCHAR 0 0 "|\0" 3 Field2

Latin1_General_CI_AS

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

SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.

--
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.

I'm sorry, cut and pasting sample text file I've removed the correct
syntax :\
In fact the last line has the terminator you specified. :)

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

>
SQLUNIQUEID is what you would use in a binary file. It's not applicable
here.

I'm migrating this bulk insert frm sql server 2000... in 2005 this
doesn't work. Is it caused by more strictly rules in 2005 engine?
Is sql converting my "char" guid in binary?

Thanks for the prompt reply :)

bobsql

Bulk Insert/ one column

I 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

BULK INSERT, setting static data using the format file

Hello dbforums,

I are using a BULK INSERT to insert the data from a ascii file to a sql table. The table has a ProductInstanceId column that exists in the tables but does not exist in the ascii DICast data. I am setting the ProductInstanceId to a Guid that will be used for Metrics. I would like to create the Guid in C++ and then set it somehow during the BULK INSERT DICastRaw1hr and DICastRaw6hr. I am calling the BULK INSERT from C++/ADO. I do not see how you can set a static data in the BULK INSERT for a column that exists in the table but does not the source data ... seems there should be a way to do this with the format file?

The other way to do this is with a TRIGGER. I have the TRIGGER below. Prior to the calling the BULK INSERT using ADO I will use ADO to ALTER the TRIGGER with the new Guid. When the BULK INSERT runs the ProductInstanceId will be populated with the new Guid.

ALTER TRIGGER DICastRaw1hrInsertGuid
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT AS UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE modelrundatetime = (select max(modelrundatetime) from Alphanumericdata.dbo.DICastraw1hr(NOLOCK))

More Questions:

- The Trigger is slow. The Bulk Insert without the Trigger runs in about 10 sec ... with the Trigger in about 40 sec. I tried to use the sql code below in the TRigger but it was only doing the UPDATE on the last row. The TRIGGER must run after the BULK INSERT is complete. Now I am using the select (bad). Any comments ...

ALTER TRIGGER DICastRaw1hrInsertDate
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT
AS
DECLARE @.ID as integer
SELECT @.ID = i.recordid from inserted i
UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE recordid = @.ID

- I understand that I could set the Guid in the Default Value part of the table definition using the NEWID() function. I need the Guid to be the same for all the rows that are inserted during the BULK INSERT (all have the same modelrundatetime) ... how would I do this?

Thanks,
ChrisOr create a table that matches your file and load it...then transfer the data to it's final destination with a sproc...|||Loading into one table and then moving data into another? How about adding one more for fun?

Just look for this topic in BOL:

Using a Data File with Fewer Fields|||Originally posted by rdjabarov
Loading into one table and then moving data into another? How about adding one more for fun?

Just look for this topic in BOL:

Using a Data File with Fewer Fields

Why not...a staging table works great, especially when you need to audit data, add stuff ect.

Yes, he can use a format file...just set the server column order to 0

Lots of posts seem to deal with "unknown" file structures or missing data...or data types that are "iffy"..

Me, I prefer to let the feeder know there file is garbage, and have them resend it and have a locked down process...

Not always the case though...|||i can help with the Bulk Insert / ADO problem
but give me an example of what you are trying to load
in what kind of table|||Originally posted by Karolyn
i can help with the Bulk Insert / ADO problem
but give me an example of what you are trying to load
in what kind of table

Thanks to all for your responses.

I think I have a solution. I will create a sql function and use it to set the GUID in the Default Field attribute of the ProductInstanceId column.

Before I call the BULK INSERT from ADO I will get the new GUID (in C++) and then ALTER the FUNCTION using ADO. I will then call BULK INSERT and the ProductInstanceId will be set to the same Guid for all the rows that are inserted during the BULK INSERT.

The next time ... I will ALTER the FUNCTION again getting a new Guid and then do the same thing. This should be VERY fast.

I have only done the prototype. When I get all the code I will post it.

Off to my Neighborhood Caucus to remove Bush from office :--)

Thanks Again,
Peace and Love,
Chris|||Hi ...

Below is the code in C++ / ADO to achieve what I did. Again, During and BULK INSERT operation we want to set a column in the table that does not exist in the source file to a specific value for all the rows that will be inserted into the table for the BULK INSERT.

To do this we are using the ALTER TABLE ADD CONTRAINT to add a static string to the default value of a specific field of the table. We will set this to a GUID and then do the BULK INSERT. When we are done with the BULK INSERT we will ALTER TABLE DROP CONSTRAINT just be be safe.

Below is the code:

UINT CDICastLoadProcess::ThreadProcessLongTermWithBulkI nsert(LPVOID pParam)
{

// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
_RecordsetPtr pRecordset = NULL;

// Set the stored procedure name
csProcedureNameForecast6Hr = "DICastForecast6HrInsert";

try
{

// Get the starting tick count to do some duration measurment
nStart = GetTickCount();

// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));

csConnect.Format("Provider='%s';Data Source='%s';",
pDICastLoadProcess->GetProvider(),
pDICastLoadProcess->GetDataSource());
csTemp.Format("Initial Catalog='%s';",
pDICastLoadProcess->GetInitialCatalog());
csConnect += csTemp;
csTemp.Format("User Id=%s;Password=%s",
pDICastLoadProcess->GetUserId(),
pDICastLoadProcess->GetPassword());
csConnect += csTemp;

bstrConnect = csConnect.AllocSysString();

pConnection->Open(bstrConnect,"","",adConnectUnspecified);

pConnection->CommandTimeout = DICL_ADO_CONNECTION_TIMEOUT;

// Create the GUID object and then create the GUID using the
// CoCreateGuid() method
pguidProductInstanceId = new GUID;
hrReturn = CoCreateGuid(pguidProductInstanceId);

// Convert the GUID to a string
// must link in Rpcrt4.lib for UuidToString
UuidToString(pguidProductInstanceId, &strProductInstanceId);
delete pguidProductInstanceId;

// We are going to drop the constraint just in case that it
// exists.
try
{
// Drop the guid constrain to the table, incase it exisits
csSql = "ALTER TABLE Alphanumericdata.dbo.DICastRaw6hr";
csSql += " DROP CONSTRAINT DF_DICastRaw6hr_ProductInstanceId";
bstrSql = csSql.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);
}
catch(...)
{
// If the contraint doe not exist, as it should not then we will
// fall into here
csMessage.Format("Guid contraint does not exist prior to alter, normal status. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_DEBUG, csMessage, csMethodName);
}

// Add the guid constrain to the table
csMessage.Format("Add guid constraint to table. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csMessage, csMethodName);
csMessage.Format("Guid value strProductInstanceId: %s Thread:%d",strProductInstanceId, nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_DEBUG, csMessage, csMethodName);

csSql = "ALTER TABLE Alphanumericdata.dbo.DICastRaw6hr";
csSql += " ADD CONSTRAINT DF_DICastRaw6hr_ProductInstanceId";
// csSql += " DEFAULT 'AA9a44eb-092b-445b-a224-cc7cdd207092' FOR ProductInstanceId";
csTemp.Format(" DEFAULT '%s' FOR ProductInstanceId", strProductInstanceId);
csSql += csTemp;
bstrSql = csSql.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);

// Set the ProductId (GUID) in the recordset
// sval.SetString((const char *)strProductInstanceId);
// pRecordset->Fields->GetItem(L"ProductInstanceId")->PutValue(sval);

// pDICastLoadProcess->GetInputFilename()

// Typically the filename will come to use with a path. We only want
// the filename, we will remove the path if we find one. We are adding
// one to the position as ReverseFind() returned the position of
// the '\\', and we want the position of the next char
csInputFilename = pDICastLoadProcess->GetInputFilename();
nPos = csInputFilename.ReverseFind('\\');
nPos++;
csInputFilename = csInputFilename.Mid(nPos);

csMessage.Format("Begin sql bulk insert: %s Thread:%d", csInputFilename, nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csMessage, csMethodName);

// Create the SQL string using
csTemp = "BULK INSERT [Alphanumericdata].[dbo].[DICastRaw6Hr]";
//csTemp += " FROM '\\\\filer1\\DATA.TEST\\csv\\DICASTST.CSV'";
csTemp += " FROM '\\\\filer1\\DATA.TEST\\csv\\";
csTemp += csInputFilename;
csTemp += "' WITH (FIELDTERMINATOR = ',',";
csTemp += " FIRSTROW = 2,";
// csTemp += " LASTROW = 5000,";
csTemp += " ROWTERMINATOR = '\n',";
csTemp += " FIRE_TRIGGERS,";
csTemp += " FORMATFILE = '\\\\filer1\\DATA.TEST\\csv\\DICASTLTF.CSV')";

// Set the bstr and then execute the sql
bstrSql = csTemp.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);

csMessage.Format("Bulk insert completed successfully: %s Thread:%d", csInputFilename, nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csMessage, csMethodName);

nEnd = GetTickCount();
nElapsedTime = (float)(nEnd - nStart)/(float)1000;

// Set the end time
COleDateTime oledtEndDateTime = COleDateTime::GetCurrentTime();

// Create the time span object using the values passed
COleDateTimeSpan oledtDuration = oledtEndDateTime - oledtStartDateTime;

// Format the elapse time
CString csDuration = oledtDuration.Format("%H:%M:%S");

// write message to introduce the Method
csLogMessage.Format("Bulk insert stats: %s Count: %d Error: %d Elapsed time: %s Thread:%d",
csInputFilename,
nCount,
nError,
csDuration,
nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csLogMessage, csMethodName);

// Drop the guid constrain to the table
csMessage.Format("Drop guid constraint from table. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_INFO, csMessage, csMethodName);
csSql = "ALTER TABLE Alphanumericdata.dbo.DICastRaw6hr";
csSql += " DROP CONSTRAINT DF_DICastRaw6hr_ProductInstanceId";
bstrSql = csSql.AllocSysString();
pConnection->Execute(bstrSql, NULL, adExecuteNoRecords);


// write message to introduce the Method
csLogMessage.Format("ThreadProcessLongTermWithBulkInsert() thread ending normally. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csLogMessage, csMethodName);

}
catch(_com_error *e)
{
}
catch(...)
{
}

// Clean up objects before exit the thread.
if (pConnection)
{
if (pConnection->State == adStateOpen)
{
pConnection->Close();
}
}

delete pvTemp;

if (pDICastLoadProcess->GetOwner())
{
// Send a message that we are done
::PostMessage(pDICastLoadProcess->GetOwner()->m_hWnd,
WM_PROCESS_DICAST_THREAD,
(WPARAM) 103,
(LPARAM) nRowCount);
}

// We need these messages at the end.
// We need a flush in the Logging Class ?
csLogMessage.Format("Posting message to calling application. Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csLogMessage, csMethodName);

csLogMessage.Format("Ending ThreadProcessLongTermWithBulkInsert(). Thread:%d", nThreadCount);
pDICastLoadProcess->m_pLog->Write(MXLOG_SEVERITY_STATIC, csLogMessage, csMethodName);

// Decrement the thread count on the way out
pDICastLoadProcess->SetThreadCountDec();

return 0;
}

Bulk Insert XML with IDENTITY Column

Hi ...
I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error:
... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
Reading another article here I have added the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments are appreciated.
Thanks,
Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

///////////////////////////////////////////////////
// xml data
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

///////////////////////////////////////////////////
// xsc schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Hi ...

To allow SQL Server to set the IDENTITY column the KeepIdentity atribute needs to be set to false (KeepIdentity((bool)FALSE).

Thanks,

Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)FALSE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

Bulk Insert XML with IDENTITY Column

Hi ...
I have a program that will insert xml data into a table. When I add an IDENTITY column to the table then I get the following error:
... [Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
Reading another article here I have added the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments are appreciated.
Thanks,
Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

///////////////////////////////////////////////////
// xml data
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>

///////////////////////////////////////////////////
// xsc schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GOHi ...
To allow SQL Server to set the IDENTITY column the KeepIdentity atribute needs to be set to false (KeepIdentity((bool)FALSE).
Thanks,
Chris

///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)FALSE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}

Bulk Insert with only one column

Hi,

I have a question on inserting data for only a specific column in a table.

I have a table as follows

Table <MyTable>

{

Name varchar,

DateUpdate DateTime

}

I wanted to insert the the from a file to table.

The File contains the list of name as follows (line by line)

name1

name2

name3

name4

......

The file name actually contains the DateTime

I would like to insert the names in the file as wellas the DateTime (i.e. part of the file name ) into <MyTable>

I guess "Bulk insert " doens't allow to insert values for only one column

If i change the contents of my data file to

name1 | DateTime1

name2 | DateTime2

name3 | DateTime3

name4 | DateTime4

Then the follwoin query works fine for me.

Bulk Insert <MyTable> FROM <filePath>

With

{

FIELDTERMINATOR = '|'

ROWTERMINATOR = '\n'

}

But my original file will contains only Names and the file name contains the date that commom for all the names in the file. And also the file may contains millions of names

Is there any way this can be accomplised using " Bulk Insert" ? Or is there any alternative that i can do it fastly

Your answer will be appreciated

~mohan

Instead of achieving this task in one step, can you do in two steps making use of a staging table that matches the file structure and insert to the main table after that. There might be other solutions also, lets hear from other people.

|||

Hi Mohan,

Answer for your First Question :

Table <MyTable>

{

Name varchar,

DateUpdate DateTime

}

Create a view like :

create view vwMyTable

as

select Name from MyTable

Use this view in Bulk Insert to load the data instead of the table.

Here the assumption is your DataUpdate allows Null or any default assigned to it.

Answer for your Second Question :

Insert all your data file names into a temp table using xp_cmdshell output.

Read from the table one by one and execute the bulk insert and update the datetime from table.

Thanks

Naras.

|||

If your use SQL Server 2005, you coult use OPENROWSET with BULK option:

Code Snippet

INSERT INTO youTable

SELECT a.Name, <some expression for datetime> FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;

|||

You can build an SS Integraton Services package

1. For get a new datatime column you can use Derived Column Data Flow Transformation

2. For separating your data in Name and DateUpdate you can use Fixed Width Format in Flat File Connection Manager; in this way you put a column delimiter in desired position of initial string , depends on the size of DateUpdate.

This package can be put in a job a launched periodically if your business logic need it.

|||

I would also suggest that you use a stage table for the initial load, then do whatever 'massaging' needs to be done, and after that update your production table.

/Kenneth

Bulk Insert With Identity Field

Hi All,
Can I Bulk Insert to to SQL Table with a Identity Column in it?
My Source is a text file with 17 Columns and My Target is a SQL Server table
with 18 Columns (all 17 column of the source + 1 Identity Column as Primary
KEY).
So In this Situation How can i Bulk Insert to the SQL Table from the text
file. Please give small example if possible.
Also If my SQL table is have 2 More Extra Column Can I Boul Insert from the
above source?
Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
Column + 1 Extra column). If I want to Insert into the 17 columns and I want
the Indetity column to generate auto numbers and the Last Extra Column to be
Filled with Some "Char (1)" Value. Is that Possible?
Thanks for any Help or suggestions
Prabhat
using bulk insert there is a keepidentity parameter, using bcp it is -E...
Both are documented in books on line
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Can I Bulk Insert to to SQL Table with a Identity Column in it?
> My Source is a text file with 17 Columns and My Target is a SQL Server
table
> with 18 Columns (all 17 column of the source + 1 Identity Column as
Primary
> KEY).
> So In this Situation How can i Bulk Insert to the SQL Table from the text
> file. Please give small example if possible.
> Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
the
> above source?
> Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
> Column + 1 Extra column). If I want to Insert into the 17 columns and I
want
> the Indetity column to generate auto numbers and the Last Extra Column to
be
> Filled with Some "Char (1)" Value. Is that Possible?
> Thanks for any Help or suggestions
> Prabhat
>
|||Thanks for the Hint. I have seen that in BOL but did not get any Example.
Can you suggest any site or give me a small Example where the Target table
has a Identity Field but the Source does not have the value for Identity
Column.
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:#m#6mmwnEHA.3900@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> using bulk insert there is a keepidentity parameter, using bcp it is -E...
> Both are documented in books on line
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> table
> Primary
text[vbcol=seagreen]
> the
Identity[vbcol=seagreen]
> want
to
> be
>

Bulk Insert With Identity Field

Hi All,
Can I Bulk Insert to to SQL Table with a Identity Column in it?
My Source is a text file with 17 Columns and My Target is a SQL Server table
with 18 Columns (all 17 column of the source + 1 Identity Column as Primary
KEY).
So In this Situation How can i Bulk Insert to the SQL Table from the text
file. Please give small example if possible.
Also If my SQL table is have 2 More Extra Column Can I Boul Insert from the
above source?
Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
Column + 1 Extra column). If I want to Insert into the 17 columns and I want
the Indetity column to generate auto numbers and the Last Extra Column to be
Filled with Some "Char (1)" Value. Is that Possible?
Thanks for any Help or suggestions
Prabhatusing bulk insert there is a keepidentity parameter, using bcp it is -E...
Both are documented in books on line
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Can I Bulk Insert to to SQL Table with a Identity Column in it?
> My Source is a text file with 17 Columns and My Target is a SQL Server
table
> with 18 Columns (all 17 column of the source + 1 Identity Column as
Primary
> KEY).
> So In this Situation How can i Bulk Insert to the SQL Table from the text
> file. Please give small example if possible.
> Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
the
> above source?
> Ex: Total 19 Columns ( all 17 columns of the source text file + 1 Identity
> Column + 1 Extra column). If I want to Insert into the 17 columns and I
want
> the Indetity column to generate auto numbers and the Last Extra Column to
be
> Filled with Some "Char (1)" Value. Is that Possible?
> Thanks for any Help or suggestions
> Prabhat
>|||Thanks for the Hint. I have seen that in BOL but did not get any Example.
Can you suggest any site or give me a small Example where the Target table
has a Identity Field but the Source does not have the value for Identity
Column.
Thanks
Prabhat
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:#m#6mmwnEHA.3900@.TK2MSFTNGP10.phx.gbl...
> using bulk insert there is a keepidentity parameter, using bcp it is -E...
> Both are documented in books on line
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:OKa8j8unEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Can I Bulk Insert to to SQL Table with a Identity Column in it?
> >
> > My Source is a text file with 17 Columns and My Target is a SQL Server
> table
> > with 18 Columns (all 17 column of the source + 1 Identity Column as
> Primary
> > KEY).
> >
> > So In this Situation How can i Bulk Insert to the SQL Table from the
text
> > file. Please give small example if possible.
> >
> > Also If my SQL table is have 2 More Extra Column Can I Boul Insert from
> the
> > above source?
> > Ex: Total 19 Columns ( all 17 columns of the source text file + 1
Identity
> > Column + 1 Extra column). If I want to Insert into the 17 columns and I
> want
> > the Indetity column to generate auto numbers and the Last Extra Column
to
> be
> > Filled with Some "Char (1)" Value. Is that Possible?
> >
> > Thanks for any Help or suggestions
> > Prabhat
> >
> >
>sql

BULK INSERT UTF8 File ERROR 4864 on first row first column

we try to import a UTF8 File (at first with SSIS BULK INSERT but now with SQL to track down the errors) and always face the same issue - the first row does not import with error "Msg 4864, Level 16, State 1, Line 10 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (PSGT_ID)."

The Statements see below. We need to use utf8 because thets the only we can get - with ansi oder unicode it works - so why not utf8? (Codepage 65001?)

--CREATE TABLE [BULKTEST](
-- [PSGT_ID] [numeric](38, 0) NOT NULL primary key,
-- [PSGT_PSG_ID] [numeric](38, 0) NOT NULL,
-- [PSGT_PSP_ID] [numeric](38, 0) NOT NULL,
-- [PSGT_KURZTEXTM] [nvarchar](5) COLLATE Latin1_General_CI_AS NULL,
-- [PSGT_TEXT] [nvarchar](40) COLLATE Latin1_General_CI_AS NULL, -
- [PSGT_AENDDATUM] [datetime] NOT NULL)

truncate table [BULKTEST]
BULK INSERT [BULKTEST] FROM 'C:\test.txt' WITH( CHECK_CONSTRAINTS, CODEPAGE='65001', DATAFILETYPE='char', FIELDTERMINATOR='|', ROWTERMINATOR='\n')
select * from [BULKTEST]

As test data you could use something like this

1|2|3|4|5|2006-01-01
2|2|3|4|5|2006-01-01
3|2|3|4|5|2006-01-01
4|2|3|4|5|2006-01-01

in notepad in a text file stored as UTF8!

We really need help on this!

Best regards, HANNES

Try using the DATAFILETYPE = 'widechar'. You can also try using a format file.|||

I have tried widechar and also a format file - but it stays the same.

Maybe we are a little bit further - could it by that notepad incorrectly adds some characters at the beginning of the file?

Like  - if i display them on the comandline with type <filename>?

LG HANNES

|||Check this Blog http://sqlblogcasts.com/blogs/dong/archive/2006/11/27/how-to-bulk-insert-your-user-defined-types-udt.aspx in this case.

Bulk Insert Question

I have a simple table I have created to do a bulk insert with a SP. Everything works fine but when the sp runs, the first column in the rows are like

"0243567

My bulk insert

BULK INSERT [Mas500_app].[dbo].[Test] FROM 'C:\Documents and Settings\Chris\Desktop\testbulk1.txt' WITH (FIELDTERMINATOR = '"~"', ROWTERMINATOR = '"')

the testbulk.txt file

"0243567"~"08/26/06"

How would I go about in my bulk statement to remove the " from the first column in the row.

ThanksWell there is no such options in bulk statement to remove the " from "0243567

the trailing " is removed becuase you have specified it as the field terminator

however you have two options
1) Change the txt file to remove the " from the first row data
2) issue the follwoing update statement immediately after the bulk insert statement

update [Mas500_app].[dbo].[Test]
set column1 = replace( column1 , " , '' )

HOwever the above replace statement is helpful only if your data in column1 does not contain any " else you will have to go for more elaborate procedures|||I think this might do what you want (it trims the first character off a field value).

UPDATE MyTable
SET MyField = SubString(MyField,2,Len(MyField))

HTH ~Georgesql

bulk insert problem

hi guys, I have a data file that I like to import to a table.
the column delimiter is @., row delimiter is \n, line feed.
But here is the problem, we have a column called "comment",
basically user can enter any characters to it, so if "comment" column
contains
@. character, then sql server think it's an extra column. However, that's
why before @. character we put \ , it's \@. in comment column now. BUT How
come it doesn't do escape for @. character? Is it possible to create an
exception for delimiter in case if Sql server sees \@. , then it'll ignore it
and will not treat it as delimiter?
thanksWhy not do a global replace of @. with some other sequence of characters that
is not already found in the file? Import the file and then do another
replace in the column to put the characters back to @..
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
> hi guys, I have a data file that I like to import to a table.
> the column delimiter is @., row delimiter is \n, line feed.
> But here is the problem, we have a column called "comment",
> basically user can enter any characters to it, so if "comment" column
> contains
> @. character, then sql server think it's an extra column. However,
> that's
> why before @. character we put \ , it's \@. in comment column now. BUT
> How
> come it doesn't do escape for @. character? Is it possible to create an
> exception for delimiter in case if Sql server sees \@. , then it'll ignore
> it
> and will not treat it as delimiter?
> thanks
>|||in this case, it's possible to write perl script to do global replacement in
windows?
This way I can make it automate process.
"Andrew J. Kelly" wrote:

> Why not do a global replace of @. with some other sequence of characters th
at
> is not already found in the file? Import the file and then do another
> replace in the column to put the characters back to @..
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
>
>|||I thought this might have been a one time import but I don't see why you
can't use perl (or some other utility) to automate this.
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:6D17EF5E-0150-4F38-80BB-91D6F086CA70@.microsoft.com...
> in this case, it's possible to write perl script to do global replacement
> in
> windows?
> This way I can make it automate process.
>
> "Andrew J. Kelly" wrote:
>sql

BULK INSERT into table with identity column?

I have a file I'm trying to do some non-set-based processing with. In
order to make sure I keep the order of the results, I want to BULK
INSERT into a temp table with an identity column. The spec says that
you should be able to use either KEEPIDENTITY or KEEPNULLS, but I can't
get it to work. For once, I have full code - just add any file of your
choice that doesn't have commas/tabs. :)

Any suggestions, folks?

--create table ##Holding_Tank ( full_record varchar(500)) -- this
works
create table ##Holding_Tank (id int identity(1,1) primary key,
full_record varchar(500)) --that doesn't work

BULK INSERT ##Holding_Tank
FROM "d:\telnet_scripts\psaxresult.txt"
WITH
(
TABLOCK,
KEEPIDENTITY,
KEEPNULLS,
MAXERRORS = 0
)
select * from ##Holding_tankM Bourgon (bourgon@.gmail.com) writes:

Quote:

Originally Posted by

I have a file I'm trying to do some non-set-based processing with. In
order to make sure I keep the order of the results, I want to BULK
INSERT into a temp table with an identity column. The spec says that
you should be able to use either KEEPIDENTITY or KEEPNULLS, but I can't
get it to work. For once, I have full code - just add any file of your
choice that doesn't have commas/tabs. :)
>
Any suggestions, folks?

Even if you want the identity values to be generated for you, the column
still has to be in the file if you don't use a format file.

There are two way to go: add a 0 and a delimiter on each line in the
file, or use this format file:

8.0
1
1 SQLCHAR 0 0 "\r\n" 2 col ""

What I don't really remember is whether you actually can trust the
IDENTITY values to match the order the rows appear in the file. If you
want to play safe, you may want to write a program that reads the file
and adds a line number on each line in the 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|||

Quote:

Originally Posted by

Even if you want the identity values to be generated for you, the column
still has to be in the file if you don't use a format file.

Aha.

Quote:

Originally Posted by

What I don't really remember is whether you actually can trust the
IDENTITY values to match the order the rows appear in the file. If you
want to play safe, you may want to write a program that reads the file
and adds a line number on each line in the file.

Okay, thanks again, Erland. I was afraid of that.

BULK INSERT FROM UTF-8 FILE

Hi there,

I have some text files saved using a UTF-8 encoding. The "BULK INSERT"
statment in Sql Server 2000 is failing with a column length error. Saving
the file as ASCII removes the problem. However, I would like to import files
in UTF-8 format. I understand that the BCP tool, when used from the command
line, can take an "-F UTF8" argument, which allows it to work. Can this be
done from the SQL> prompt with the BULK INSERT statement?

Cheers,

Tobin"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:bpi91v$1osvna$1@.ID-131901.news.uni-berlin.de...
> Hi there,
> I have some text files saved using a UTF-8 encoding. The "BULK INSERT"
> statment in Sql Server 2000 is failing with a column length error. Saving
> the file as ASCII removes the problem. However, I would like to import
files
> in UTF-8 format. I understand that the BCP tool, when used from the
command
> line, can take an "-F UTF8" argument, which allows it to work. Can this be
> done from the SQL> prompt with the BULK INSERT statement?
> Cheers,
> Tobin

I believe that the -F argument is for the Sybase version of BCP. The closest
equivalent in MSSQL is probably -w, but it may not be functionally
equivalent. Using BULK INSERT, that would be "DATAFILETYPE = 'widechar'".

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3fbd17b2$1_2@.news.bluewin.ch...
> I believe that the -F argument is for the Sybase version of BCP. The
closest
> equivalent in MSSQL is probably -w, but it may not be functionally
> equivalent. Using BULK INSERT, that would be "DATAFILETYPE = 'widechar'".

Thanks for the response, Simon. Ah, I see, I must have found a Sybase
example for the -F argument! I actually discovered the 'widechar' thing in
the docs, but for some reason it was still falling over on the last column
of the first row. I tried the other options such as 'RAW' etc but with no
luck!

Currently I'm asking the developer to output the file as ASCII or ANSI, so
that should be solved (although it would be nice if the BULK INSERT
accommodated such variations). However, the files are gonna be UNIX files,
so my next trick is to have BCP understand how to read these!

Tobin

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer

Bulk Insert Format File

Hi ,

I was wondering if there was a way in a format file to load a host file data field to more than one column in a table?

Thanks

Are you using SSIS?|||Yes, and I am using a bulk insert task with a format file, I am really kinda new to this, and I have figured out how to skip columns and data fields, but not this.

BULK INSERT flat file with only one column

Hi,

I have a text file with a single column that i need to bulk insert into a table with 2 colums - an ID (with identity turned on) and col2

my text file looks like:

row1
row2
row3
...
row10

so my bulk insert i have like this:
BULK INSERT test FROM 'd:\testBig.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

but i get the error:

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

However, as you can see from the text file, there is only one column, so i dont have any field terminators.

Any ideas how to make this work?

Thanks.You'll probably need to use a format file to be able to skip columns.|||i dont think i want to skip any columns ??

the above method works if i have say 2 columns in my text file, and 3 columns in my table, since the first column in the table is an identity column.

my problem is that the bulk insert doesnt seem to be able to handle a text file with just one field per row. it seems to be looking for a field delimiter, when in fact there is only one field and then its the row delimiter etc etc.|||Create a table with one column which is used to store data from your text file and then do BULK INSERT.

BULK INSERT test FROM 'd:\testBig.txt'
WITH (
DATAFILETYPE = 'char',
--FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

At last, you can add a column for the ID.|||Of course you do, you want to skip columns in the target table by not populating them (I am talking about your identity field.) With "FORMATFILE = 'your_format_file' " you can define exactly what you want or don't want to populate.