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".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
Column 'warranty_expiration_date' cannot be modified because it is a
computed column.
Here is my bulk insert statement:
BULK INSERT dbo.TestData
FROM 'TestData.dat'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='TestData.fmt')
The computed column is not referenced in the format file and the data file
does not contain the computed data.
Thankstperovic (tperovic@.compumation.com) writes:
> Using SS2K, I'm getting the following error while bulk inserting:
> Column 'warranty_expiration_date' cannot be modified because it is a
> computed column.
> Here is my bulk insert statement:
> BULK INSERT dbo.TestData
> FROM 'TestData.dat'
> WITH (CHECK_CONSTRAINTS,
> FIELDTERMINATOR='|',
> MAXERRORS = 1,
> FORMATFILE='TestData.fmt')
> The computed column is not referenced in the format file and the data file
> does not contain the computed data.
Could you provide more information, for instance a CREATE TABLE statement,
a sample data file and a sample format file that demonstrates the problem.
To wit, I created this table:
create table c (a int NOT NULL,
b as sqrt(a))
And I created this format file:
8.0
1
1 SQLCHAR 0 0 "\r\n" 1 dda_num ""
And used this data file:
12
4144
356
And this command:
bulk insert c FROM 'E:\temp\slask.bcp'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='E:\temp\slask.fmt')
And my load was successful.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Facing a deadline, I dropped the computed column and added it to a view.
Maybe later we can revisit this issue. Thanks.
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948A483591F3Yazorman@.127.0.0.1...
> tperovic (tperovic@.compumation.com) writes:
> > Using SS2K, I'm getting the following error while bulk inserting:
> > Column 'warranty_expiration_date' cannot be modified because it is a
> > computed column.
> > Here is my bulk insert statement:
> > BULK INSERT dbo.TestData
> > FROM 'TestData.dat'
> > WITH (CHECK_CONSTRAINTS,
> > FIELDTERMINATOR='|',
> > MAXERRORS = 1,
> > FORMATFILE='TestData.fmt')
> > The computed column is not referenced in the format file and the data
file
> > does not contain the computed data.
> Could you provide more information, for instance a CREATE TABLE statement,
> a sample data file and a sample format file that demonstrates the problem.
> To wit, I created this table:
> create table c (a int NOT NULL,
> b as sqrt(a))
> And I created this format file:
> 8.0
> 1
> 1 SQLCHAR 0 0 "\r\n" 1 dda_num ""
> And used this data file:
> 12
> 4144
> 356
> And this command:
> bulk insert c FROM 'E:\temp\slask.bcp'
> WITH (CHECK_CONSTRAINTS,
> FIELDTERMINATOR='|',
> MAXERRORS = 1,
> FORMATFILE='E:\temp\slask.fmt')
> And my load was successful.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
I am currently struggling with a problem bulk inserting data into sql server.
The application I am writing is multi-threaded and downloads about 2000 records every 2 seconds on x amount of threads (depending on bandwidth).
What I would like to do is find a 'friendly' way to insert this data into sql server without hammering the cpu.. I have tried the following with little success.
1, Using a single insert and thread.sleep(x * 20) to allow for massive data input, altough this made the application more stable and lowered cpu usage to very little the data takes about 60 times longer to download and process into the database.
2, Using a SqlDataAdapter and DataSet and updating the database via the .Update method of the data adapter.. Simply this was awful and took forever to process the data into the database.. (Took about 30 seconds to process 2000 records and Command Timeout was high).
3, Using OpenXML in SqlServer and parsing the data as an XML string (nText), although this method is fast its still very CPU intensive. I have to set the command timeouts very high to allow for this approach (because of the multi-threaded nature of the app).
Does anyone have any idea's on a cpu friendly approach to this problem ??
Thanks in advance..
Gary.You can actually use BCP
Hi
I have a system which Profiles people. However a new profiler has been written which is better, and all the old profiled members have to be moved to the new profiler tables.
I have a Members table with member data in it, it contains the old profile data in the table too, or at least the ID's per member.
In the new profiler things work slightly different.
I'll give an example:
Lets say MemberID 3241 is a smoker, SmokerID = 3 (Moderate Smoker)
And he drinks occasionally, AlcoholID = 2 (Light)
These ID fields are kept in the Members table which link to a table of their own i.e. memSmoking Table, or memAlcohol Table
The new profiler table works different. In the sense that the Alcohol and Smoking are all in the same table, but with different OptionID's and ValuesID's
Here is some sample code that i wrote. Just copy and paste, it will give you a base to work from. I'm trying my best to supply as much information as possible, so if i left anything out then please let me know? And thanks for the help in advance
Code Snippet
--Sample Code:
--Members Table
DECLARE @.Members TABLE (MemberID INT IDENTITY(1,1),
ClientID INT,
Name VARCHAR(50),
Surname VARCHAR(50),
GenderID CHAR(1),
MaritalStatusID INT,
MemSmokingID INT,
MemAlcoholID INT)
INSERT INTO @.Members VALUES (211, 'Carel','Greaves', 'M', 2, 1, 3)
INSERT INTO @.Members VALUES (211, 'Jill', 'Jenkins', 'F', 1, 3, 4)
SELECT * FROM @.Members
--
--Profile Attrubutes
--
DECLARE @.memGender TABLE (GenderID CHAR(1), Description VARCHAR(50))
INSERT INTO @.memGender VALUES ('M', 'Male')
INSERT INTO @.memGender VALUES ('F', 'Female')
SELECT * FROM @.memGender
DECLARE @.memMaritalStatus TABLE (MaritalStatusID INT, Description VARCHAR(50))
INSERT INTO @.memMaritalStatus VALUES (1, 'Married')
INSERT INTO @.memMaritalStatus VALUES (2, 'Single')
INSERT INTO @.memMaritalStatus VALUES (3, 'Devorced')
INSERT INTO @.memMaritalStatus VALUES (4, 'Widowed')
SELECT * FROM @.memMaritalStatus
DECLARE @.memSmoke TABLE (MemSmokingID INT, Description VARCHAR(50))
INSERT INTO @.memSmoke VALUES (1, 'Nil')
INSERT INTO @.memSmoke VALUES (2, 'Light')
INSERT INTO @.memSmoke VALUES (3, 'Moderate')
INSERT INTO @.memSmoke VALUES (4, 'Heavy')
SELECT * FROM @.memSmoke
DECLARE @.memAlcohol TABLE (MemAlcoholID INT, Description VARCHAR(50))
INSERT INTO @.memAlcohol VALUES (1, 'Nil')
INSERT INTO @.memAlcohol VALUES (2, 'Light')
INSERT INTO @.memAlcohol VALUES (3, 'Moderate')
INSERT INTO @.memAlcohol VALUES (4, 'Heavy')
SELECT * FROM @.memAlcohol
--
--New Profile Attributes
--
DECLARE @.MemberOptionAtributes TABLE (ValuesID INT IDENTITY(1,1),
OptionID INT,
DisplayName VARCHAR(50))
INSERT INTO @.MemberOptionAtributes VALUES (2, 'Male')
INSERT INTO @.MemberOptionAtributes VALUES (2, 'Female')
INSERT INTO @.MemberOptionAtributes VALUES (3, 'Married')
INSERT INTO @.MemberOptionAtributes VALUES (3, 'Single')
INSERT INTO @.MemberOptionAtributes VALUES (3, 'Devorced')
INSERT INTO @.MemberOptionAtributes VALUES (3, 'Widowed')
INSERT INTO @.MemberOptionAtributes VALUES (4, 'Nil')
INSERT INTO @.MemberOptionAtributes VALUES (4, 'Light')
INSERT INTO @.MemberOptionAtributes VALUES (4, 'Moderate')
INSERT INTO @.MemberOptionAtributes VALUES (4, 'Heavy')
INSERT INTO @.MemberOptionAtributes VALUES (5, 'Nil')
INSERT INTO @.MemberOptionAtributes VALUES (5, 'Light')
INSERT INTO @.MemberOptionAtributes VALUES (5, 'Moderate')
INSERT INTO @.MemberOptionAtributes VALUES (5, 'Heavy')
SELECT * FROM @.MemberOptionAtributes
--MemberProfileLookupValues Table
DECLARE @.MemberProfileLookupValues TABLE (EntryID INT IDENTITY(1,1),
MemberID INT,
OptionID INT,
ValueID INT)
--This is how the data should be inserted, but i don't know how to do it in bulk for all the members in the members table (There are over 1000000)!
INSERT INTO @.MemberProfileLookupValues VALUES (1, 1, 1)
INSERT INTO @.MemberProfileLookupValues VALUES (1, 3, 5)
INSERT INTO @.MemberProfileLookupValues VALUES (1, 4, 7)
INSERT INTO @.MemberProfileLookupValues VALUES (1, 5, 13)
INSERT INTO @.MemberProfileLookupValues VALUES (2, 1, 2)
INSERT INTO @.MemberProfileLookupValues VALUES (2, 3, 3)
INSERT INTO @.MemberProfileLookupValues VALUES (2, 4, 9)
INSERT INTO @.MemberProfileLookupValues VALUES (2, 5, 14)
SELECT * FROM @.MemberProfileLookupValues
--Here is a piece of actual code that i tried that i thought would work but it inserted all the fields for all options for all members (WHOOPS!)
/*SET NOCOUNT ON
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'
WHEN 2 THEN '8'
WHEN 3 THEN '9'
WHEN 4 THEN '10'
END
FROM Members M
INNER JOIN _MemberProfileLookupValues ML ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND ML.OptionID <> 6
GO
*/
Carel:
What is the objective here; I am afraid I missed it.
|||Old Profiler uses these tables
@.Members
@.memSmoking
@.memAlcohol
@.memMaritalStatus
New Profiler uses these Tables
@.MemberOptionAtributes
@.MemberProfileLookupValues
So the memberID From the @.members table has to be inserted into the @.memberProfileLookupValues table, where the OptionID matched the type of attribute i.e. Smoking, Alcohol etc
Smoking = OptionID (4 in this case) i get the OptionID values from the @.MemberOptionAtributes table
Alcohol = OptionID (5 in this case) i also get the OptionID values from the @.MemberOptionAttributes table
When it comes to the memory tables i created, just look at the INSERT STATEMENT for the @.MemberProfileLookupValues table at the bottom and compare to the fields in the @.Members table.
OptionID's Come from the @.MemberOptionAtributes table. The valuesID's just have to be set to the right places.
for example:
If you look at the memSmoking Table
it has 4 ID's
1
2
3
4
In the @.MemberOptionAtributes table the OptionID for Smoking is 4
as far as the values are concerned for smoking in the MemberOptionAtributes table
@.memSmoking .memSmokingID(1) = @.MemberOptionAtributes .ValueID(7)
@.memSmoking .memSmokingID(2) = @.MemberOptionAtributes .ValueID(8)
@.memSmoking .memSmokingID(3) = @.MemberOptionAtributes .ValueID(9)
@.memSmoking .memSmokingID(4) = @.MemberOptionAtributes .ValueID(10)
I hope this helps, i'll keep trying to give as much info as possible.
Kind Regards
Carel Greaves
=== Edited by Carel Greaves @. 23 Jun 2007 9:32 PM UTC===
This was the porst that i posted yesterday, i sat thinking about the problem again today, and came up with something that might help me a little more, and maybe unconfuse the situation.
I want to insert all the generated memberID's in the members table into the @.MemberProfileLookupValues table with the AlcoholID's and MemSmokingID's
Hovever the New Profiler accepts the MemberID, OptionID, and ValuesID
Where the old way it was done was basically a pivotted way of doing it, i'm basically just un-pivvotting the table in the new table.
--Real Code that i actually used.
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT m.MemberID, '12', CASE MC.HealthInterestID
WHEN 1 THEN '71'
WHEN 2 THEN '72'
WHEN 3 THEN '73'
WHEN 4 THEN '74'
WHEN 5 THEN '75'
WHEN 6 THEN '76'
WHEN 7 THEN '77'
WHEN 8 THEN '78'
WHEN 9 THEN '79'
WHEN 10 THEN '80'
WHEN 11 THEN '81'
WHEN 12 THEN '82'
WHEN 13 THEN '83'
WHEN 14 THEN '84'
END
FROM Members m, _MemberProfileLookupValues ml, memHealthInterests mc
WHERE m.memberID = ml.MemberID
AND m.MemberID = mc.MemberID
AND m.Active = 1
AND ml.OptionID <> 12
GO
The problem that i had with this statement is that it Inserted the memberID with all of these field per memberID, instead of looking for where the values is = lets say 9 and insering the values 79 (According to the CASE)
This was the post is started two days ago, wouold someone please be able to help me out.
Just look at my @.Members table and the @.MemberProfileLookupValues
This was the porst that i posted yesterday, i sat thinking about the problem again today, and came up with something that might help me a little more, and maybe unconfuse the situation.
I want to insert all the generated memberID's in the members table into the @.MemberProfileLookupValues table with the AlcoholID's and MemSmokingID's
Hovever the New Profiler accepts the MemberID, OptionID, and ValuesID
Where the old way it was done was basically a pivotted way of doing it, i'm basically just un-pivvotting the table in the new table.
Code Snippet
--Real Code that i actually used.
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT m.MemberID, '12', CASE MC.HealthInterestID
WHEN 1 THEN '71'
WHEN 2 THEN '72'
WHEN 3 THEN '73'
WHEN 4 THEN '74'
WHEN 5 THEN '75'
WHEN 6 THEN '76'
WHEN 7 THEN '77'
WHEN 8 THEN '78'
WHEN 9 THEN '79'
WHEN 10 THEN '80'
WHEN 11 THEN '81'
WHEN 12 THEN '82'
WHEN 13 THEN '83'
WHEN 14 THEN '84'
END
FROM Members m, _MemberProfileLookupValues ml, memHealthInterests mc
WHERE m.memberID = ml.MemberID
AND m.MemberID = mc.MemberID
AND m.Active = 1
AND ml.OptionID <> 12
GO
The problem that i had with this statement is that it Inserted the memberID with all of these field per memberID, instead of looking for where the values is = lets say 9 and insering the values 79 (According to the CASE)
|||Carel,
I've been hoping (obviously, against hope) that you would see the mistakes inherrent in attempting to shove a EAV data model into a relationation data engine. Here, I'm going to allow someone else to attempt to explain it again. (From: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61024&whichpage=2, Michael Jones:
I think that the query you posted is a perfect illustration of the biggest disadvantage of the Entity/Attribute model, that it saves a little work up front in data modeling by allowing “open ended” insertion of new attributes at the cost of having to program the true data structure into each query. Of course, there are other annoying little problems, like enforcing not null, DRI, domain integrity, default values, check constraints, creating useful indexes, transactional integrity, etc. Basically, it takes all the most useful features of a relational data model, and throws them away.
I have to revise my other non-PC comment: "Encoded in binary, then base64 encoded into text? That’s one of the stupidest thing I've ever seen, but it looks like a stoke of genius compared to using an Entity/Attribute data model!!! What brain-dead morons came up with that!? I know it had to be a committee, because no one could be that stupid all on their own!"
The 'Original Members' table is the 'best' way to store this data. Both attempts of using some form of EAV is a bastardization of the relational database, and will always cause you more grief than it is worth. The task at hand is but one more example.
Using the code you provided (and thank you for the DDL and sample data!!!), it seems that you may be over-complicating the process. I think that it will work for you like this:
Code Snippet
INSERT INTO @.MemberProfileLookupValuesEntryID MemberID OptionID ValueID
-- -- -- --
1 1 6 8
2 2 6 7
Please do yourself a favor and do some research on the issues, problems, and pitfalls related to EAV data and relational databases.
(There are EAV databases available -I don't know how they evaluate...)
|||Hi Arnie
Thanks for the help with that thread. I read what went on there and i agree with what the guys are talking about. Unfortunately i have been given the task of converting from the old model (Which is right) to the new model i.e. (EAV).
I don't have much say in the matter as the company outsourced the EAV system which was created and actually paid money for it.
When i ran my query to insert the memberID's into the EAV model then it would run through the whole case statement and insert each value per member. for example.
Instead of looking for where the value is 1 and then assigning it value 7.
It would insert values (7, 8, 9, and 10) per memberID for all memberID's. That was my problem. (And it would do it Multiple Times)
Maybe something is wrong, but it looks right to me and it makes sense. (i'm not too concerned about the type of model at present moment) i just want the data to go into the tables right.
Code Snippet
--INSERT memSmokingID Data of Members from Members Table
SET NOCOUNT ON
INSERTINTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
SELECT m.MemberID,'9',CASE M.MemSmokingID
WHEN 1 THEN'59'
WHEN 2 THEN'60'
WHEN 3 THEN'61'
WHEN 4 THEN'62'
END
FROM Members M
INNERJOIN _MemberProfileLookupValues ML ON M.MemberID = ML.MemberID
WHERE M.Active = 1
AND ml.OptionID <> 9
GO
|||Why do you JOIN to [_MemberProfileLookupValues]?
That JOIN produces a row in the resultset for each row in [_MemberProfileLookupValues] -and I think that you only want one row per [Members] row.
Unless I don't have a complete picture of the data, you would be better off, as in my previous example, leaving the JOIN and WHERE clause out of the query. (I suspose you could still have the [m.Active = 1] filter though...)
|||As i said in my previous post, stupidity resides EVERYWHERE
Thanks Arnie (AGAIN!!!) he he
makes sense now to me why it would keep on inserting more and more duplicates.
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
I am just having this weird issue recently with the BULK INSERT command on one of our SQL2K servers which always returns the message "command(s) completed successfully" (Query Analyzer) but no rows get actually inserted into the bulk table.
BULK INSERT LOADTMP FROM 'D:\TEMP\MYFILE.CSV' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 20)
> The command(s) completed successfully.
The same BULK INSERT command used to work for the past year or so and still works just fine on the other servers but for some unknown reasons, it just stopped working on this one box while always returning the message: "the command(s) completed successfully"...
I did try to provide some bogus non-existent filename in the BULK INSERT command and it would also return "the command(s) completed successfully" as well - as long as the entire BULK INSERT command syntax and arguments are correct !!!
I thought that I found some kind of solution in the msdn knowledge database related to the issue by re-applying the SQL2K service pack 4 but it didn't fix it. Rebooting the instance and the server several times did not help either.
Have any of you had encountered similar issues before and how to resolve it? Would appreciate all you inputs...
Thanks,
JohnSince you have "MAXERRORS = 20" in your statement, it returns success unless 21 errors occur.
Try setting maxerrors = 1 and see what message you get.
Hello,
I have a question about bulk inserting text files. I have text files I import into a database, the files have alot of fields. The fields are terminated by ",", and the rows are terminated by /r. When I do the bulk insert, it puts each row into one row, I get no errors. Any help on this would be great.
BULK INSERT tblTracking
FROM 'C:\FileName.txt'
WITH
(
CHECK_CONSTRAINTS,
BATCHSIZE = 10000,
DATAFILETYPE = 'char',
KEEPNULLS,
FIELDTERMINATOR = '","',
ROWTERMINATOR='\r',
TABLOCK
)
hi,
please have a look at format files to b used with bluk operations..
regards
I need to bulk insert a excel sheet into a sql 2005 db datatable. I have to do this with three different excel files, inserting them into three different tables (each excel file has one sheet). This works like a charm for two of them, one excel file is causing troubles, as data types of the columns of the inserted data sheet are 'ntext'. This ntext declaration is causing problems within my app where I access that table.
So, any idea where I can set what datatype the columns of an inserted excel sheet should be within the sql datatable? I need the columns to be varchar(255) as it is by doing this with the two other excel files. The excel file causing troubles is being generated by another app.
Any help would be much appreciated!
t-sql code:
USE KOMAX
GO
EXEC sp_dboption Komax, 'select into/bulkcopy',True
EXEC sp_dboption Komax, 'ansi_nulls',True
EXEC sp_dboption Komax, 'ansi_warnings',True
GO
-- Delete existing Table
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Messages')
DROP TABLE Messages
-- Insert Excel Sheet
SELECT * INTO Messages FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\Messages.XLS', [Messages$])
GO
EXEC sp_dboption Komax, 'select into/bulkcopy',False
EXEC sp_dboption Komax, 'ansi_nulls',False
EXEC sp_dboption Komax, 'ansi_warnings',False
GO
Best Regards
Benjamin
Capone wrote:
The excel file causing troubles is being generated by another app.
I would say you've already found the problem.
Adamus
|||But it somehow has to be possible to define what datatype the excel columns are after inserted in sql!|||Of course there are many ways.
Traditionally:
ALTER TABLE MyTable
ALTER COLUMN MyField varchar(255)
Adamus
|||That's a way, right. I'm just trying to figure out where this ntext is coming from and where to change that behaviour. My concern is: what if one of the other fil starts causing this problem as well? For example: if the 3rd part app which generates this files changes just something? I prefer a safe solution...I don't wanna alter my whole database after a bulk insert.
But thanks a lot for your posts though!|||
Is the application creating the table (at runtime) before the insert?
If so, the datatype is hardcoded into the application. You can make the change there.
If the table already exists, change the datatype with the previous posted code and that should resolve the issue. There shouldnt' be a conversion problem frm ntext to varchar
Adamus
Hi,
for testing purposes I'm inserting a flat file into a sql-server table using BULK INSERT unsig the following code:
BULK INSERT rsk_staging
FROM 'c:\temp\bulk\rsk.txt'
WITH (
FIELDTERMINATOR = '\n',
ROWTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
DATAFILETYPE = 'char',
BATCHSIZE = 100000,
ROWS_PER_BATCH = 1925604,
TABLOCK
)
I have two versions of "rsk.txt" one with 1.9mill rows and one with the first 2000 rows only. The files have one column only with 115 characters that I'll split in to several columns later using SUBSTRING. The one with 2000 rows fires in to the database with no problems whatsoever using this exact code, the other one throws the following 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.
How can I resolve this problem?
EDIT: I tried several different row- and fieldterminators but this exact one works for the small data-file so I assume it should also work for the large one...the large one is however copyed directly using binary ftp from a unix-filesystem and the small one is manually copied into a new txt-file using UltraEdit.
Problem solved...when I used ascii-ftp instead of binary everything worked well. 1.9 mill records from a flat file on a network share into my local db WITH substring conversion in 2 mins. No need to say I'm a happy camper at the moment
I am inserting data from one table to another on the same DB. The
insert is pretty simple as in:
insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable...
This inserts about 4 millions rows in one go. And since I had the
'cannot obtain lock resources' problem, several methods were suggested
by some web sites:
1) one to split the insert into smaller chunks (I have no idea how I
can spit a insert to insert only n records at a time..)
2)to use waitfor - which I did but did not fix the error.
3)use bulk insert (in t-sql) - I dont know how to do this?
As I see I am simply trying to move data from one table to another
(ofcourse lots of data) in SQL Server 2000 and I dont see one simple
solution to the locking problem.
any ideas on how best I can do this will save my day!
thanks all.Hi,
Adding in smaller chunks doesn't solve the problem. Adding small chunk is
the same as adding big chunk. Size doesn't matter.
Looks like you are out of option.
How about adding the records one by one in the middle of the night instead?
"adi" <Adityanad@.gmail.com> wrote in message
news:1129063850.247250.54770@.g49g2000cwa.googlegro ups.com...
> Hi all,
> Hope there is a quick fix for this:
>
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable...
>
> This inserts about 4 millions rows in one go. And since I had the
> 'cannot obtain lock resources' problem, several methods were suggested
> by some web sites:
> 1) one to split the insert into smaller chunks (I have no idea how I
> can spit a insert to insert only n records at a time..)
>
> 2)to use waitfor - which I did but did not fix the error.
>
> 3)use bulk insert (in t-sql) - I dont know how to do this?
>
> As I see I am simply trying to move data from one table to another
> (ofcourse lots of data) in SQL Server 2000 and I dont see one simple
> solution to the locking problem.
>
> any ideas on how best I can do this will save my day!
>
> thanks all.|||adi (Adityanad@.gmail.com) writes:
> I am inserting data from one table to another on the same DB. The
> insert is pretty simple as in:
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable...
>
> This inserts about 4 millions rows in one go. And since I had the
> 'cannot obtain lock resources' problem, several methods were suggested
> by some web sites:
> 1) one to split the insert into smaller chunks (I have no idea how I
> can spit a insert to insert only n records at a time..)
There are a couple of techniques. But for best performance, you need
a clustered index in the table. This does not have to be unqiue though.
Say that you have a date column in the table that has a decent distribution.
Create a clustered index on tbat column:
CREATE CLUSTERED INDEX ON temptable(datecol)
Then you can do:
DECLARE @.this_date datetime,
@.next_date datetim
SELECT @.this_date = MIN(datecol) FROM temptable
WHILE @.this_date <= (SELECT datecol FROM temptable)
BEGIN
SELECT @.next_date = dateadd(MONTH, 1, @.this_date)
INSERT dataable (...)
SELECT ...
FROM temptable
WHERE datecol >= @.this_date
AND daetcol < @.next_date
SELECT @.this_date = @.next_date
END
It's important that the index is clustered to minimize seek times.
In the example I used month, but this can be changed as you see fit.
If you don't have a datetime column, maybe there is some id column or
similar. But you could even use a value like max temperature, as long
there is a decent disitribution.
You can also use SET ROWCOUNT to TOP to batch, but it's messier to
keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
with could have poor performance.)
> 2)to use waitfor - which I did but did not fix the error.
I will have to admit that I don't see the point with WAITFOR here.
> 3)use bulk insert (in t-sql) - I dont know how to do this?
This requires you to first unload the temptable to file with BCP, and
then load it to the target table with BCP or BULK INSERT. I skip
examples, as this would be a last resort for me.
Then again, your also try:
INSERT datatable (...) WITH (TABLOCKX)
SELECT ...
I have never tried it, but I expect it to lead to a single table lock
in the table, which which address the locking issue.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Tom (nospam@.yahoo.com) writes:
> Adding in smaller chunks doesn't solve the problem. Adding small chunk is
> the same as adding big chunk. Size doesn't matter.
Huh? Care to elaborate?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There is no guarantee that the record in a smaller chunk will not be locked
by another user.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96ED17409A33Yazorman@.127.0.0.1...
> Tom (nospam@.yahoo.com) writes:
>> Adding in smaller chunks doesn't solve the problem. Adding small chunk
>> is
>> the same as adding big chunk. Size doesn't matter.
> Huh? Care to elaborate?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Tom (nospam@.yahoo.com) writes:
> There is no guarantee that the record in a smaller chunk will not be
> locked by another user.
I did not really recognize the error messages that Adi got, but it seemed
to me that the problem was to get locks on the table he is inserting to,
because of resource constraints, not because he was locked by another
user.
And even in a blocking scenario, smaller batches help as it reduces the
risk for exposure for blocking.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Just a thought, its too early :). What would happen if he used NOLOCK
option? He doesnt really need locks around the place does he?
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96ECF2B98CD3FYazorman@.127.0.0.1...
> adi (Adityanad@.gmail.com) writes:
>> I am inserting data from one table to another on the same DB. The
>> insert is pretty simple as in:
>>
>> insert into datatable(field1, field2, field3)
>> select a1, a2, a3 from temptable...
>>
>>
>> This inserts about 4 millions rows in one go. And since I had the
>> 'cannot obtain lock resources' problem, several methods were suggested
>> by some web sites:
>> 1) one to split the insert into smaller chunks (I have no idea how I
>> can spit a insert to insert only n records at a time..)
> There are a couple of techniques. But for best performance, you need
> a clustered index in the table. This does not have to be unqiue though.
> Say that you have a date column in the table that has a decent
> distribution.
> Create a clustered index on tbat column:
> CREATE CLUSTERED INDEX ON temptable(datecol)
> Then you can do:
> DECLARE @.this_date datetime,
> @.next_date datetim
> SELECT @.this_date = MIN(datecol) FROM temptable
> WHILE @.this_date <= (SELECT datecol FROM temptable)
> BEGIN
> SELECT @.next_date = dateadd(MONTH, 1, @.this_date)
> INSERT dataable (...)
> SELECT ...
> FROM temptable
> WHERE datecol >= @.this_date
> AND daetcol < @.next_date
> SELECT @.this_date = @.next_date
> END
> It's important that the index is clustered to minimize seek times.
> In the example I used month, but this can be changed as you see fit.
> If you don't have a datetime column, maybe there is some id column or
> similar. But you could even use a value like max temperature, as long
> there is a decent disitribution.
> You can also use SET ROWCOUNT to TOP to batch, but it's messier to
> keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
> with could have poor performance.)
>> 2)to use waitfor - which I did but did not fix the error.
> I will have to admit that I don't see the point with WAITFOR here.
>> 3)use bulk insert (in t-sql) - I dont know how to do this?
> This requires you to first unload the temptable to file with BCP, and
> then load it to the target table with BCP or BULK INSERT. I skip
> examples, as this would be a last resort for me.
> Then again, your also try:
> INSERT datatable (...) WITH (TABLOCKX)
> SELECT ...
> I have never tried it, but I expect it to lead to a single table lock
> in the table, which which address the locking issue.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Just a thought, its too early :). What would happen if he used NOLOCK
> option? He doesnt really need locks around the place does he?
If the problem is on the table he is reading from yes. But I suspect that
it's on the table he is inserting to.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Having used this approach I still want to improve its performance.
My DBA says that splitting the insert statement into smaller chunks
will be very helpful - that is insert n number of records at a time.
so if I have 1 million rows to insert, he says its good to insert 50k
at a time.
Can someone help me on how to split the below insert to do that?
insert into datatable(field1, field2, field3)
select a1, a2, a3 from temptable.|||adi (Adityanad@.gmail.com) writes:
> Having used this approach I still want to improve its performance.
> My DBA says that splitting the insert statement into smaller chunks
> will be very helpful - that is insert n number of records at a time.
> so if I have 1 million rows to insert, he says its good to insert 50k
> at a time.
> Can someone help me on how to split the below insert to do that?
> insert into datatable(field1, field2, field3)
> select a1, a2, a3 from temptable.
Which approach?
I did suggest a method for batching earlier in the thread. For your con-
venience I repost this here:
........................
There are a couple of techniques. But for best performance, you need
a clustered index in the table. This does not have to be unqiue though.
Say that you have a date column in the table that has a decent distribution.
Create a clustered index on tbat column:
CREATE CLUSTERED INDEX ON temptable(datecol)
Then you can do:
DECLARE @.this_date datetime,
@.next_date datetim
SELECT @.this_date = MIN(datecol) FROM temptable
WHILE @.this_date <= (SELECT datecol FROM temptable)
BEGIN
SELECT @.next_date = dateadd(MONTH, 1, @.this_date)
INSERT dataable (...)
SELECT ...
FROM temptable
WHERE datecol >= @.this_date
AND daetcol < @.next_date
SELECT @.this_date = @.next_date
END
It's important that the index is clustered to minimize seek times.
In the example I used month, but this can be changed as you see fit.
If you don't have a datetime column, maybe there is some id column or
similar. But you could even use a value like max temperature, as long
there is a decent disitribution.
You can also use SET ROWCOUNT to TOP to batch, but it's messier to
keep track of what you already inserted. (Or you use a WHERE NOT EXISTS,
with could have poor performance.)
> 2)to use waitfor - which I did but did not fix the error.
I will have to admit that I don't see the point with WAITFOR here.
> 3)use bulk insert (in t-sql) - I dont know how to do this?
This requires you to first unload the temptable to file with BCP, and
then load it to the target table with BCP or BULK INSERT. I skip
examples, as this would be a last resort for me.
Then again, your also try:
INSERT datatable (...) WITH (TABLOCKX)
SELECT ...
I have never tried it, but I expect it to lead to a single table lock
in the table, which which address the locking issue.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Using SQL 2000 MSDE
I'm bulk inserting about 3.200.000 records into a table
unfortunately all memory dissapears and never returns the dirty buffers count goes up to 48000 approx.
any ideas on how to rectify this ..... ?sqlserver is designed to take up as much as memory and only releases it back if the system needs it. If you must, restarting sqlserver will force the release.
Normally, I wouldn't want to pump that much data into the db in one shot. Rather, I would do it in batches. Please take a look at -b option of bcp or ROWS_PER_BATCH of bulk insert.|||Thanks for that
I have already figured out that restarting the service will do the trick .....
but it is a live database with many users .... (they will not thank me for doing such things)
thanks anyway ... i'll keep searching
I have a ZIP table with Zip_Code, Zip_City, Zip_State and the text file has the fields in Zip_City, Zip_State, Zip_Code. The instructions were to keep the order as defined in the Entity Definition which would be the first order.
My code for the bulk insert is usually
Code: ( text )
Good Luck.|||Thanks, I figured I couldnt do it using bulk insert unless I created a temp. However, I'm now allowed to create the table with attributes of the same order as the data in the text file as many of my classmates were asking the professor about it. =D
