I"m trying to use a BULK INSERT command to insert data into a table from a file. There is a UNIQUE Index that is being violated and the BULK INSERT fails.
I do not want to drop or disable the index, however, i also do not want to load 'duplicate' records so i keep the CHECK_CONSTRAINTS parameter.
Is there a way to have the duplicate records outputed to the ERRORFILE ?
Hello,
My company is upgrading one of our products; this involves some data migration and a question about existing indexes with uniqueness constraints has been raised. Here is the issue:
We have a database table with two particular columns that that we index with a uniqueness constraint. My question is: is the uniqueness constraint case sensitive?
For instance, if I have a database that is using a collation of SQL_Latin1_General_CP1_CS_AS. (case sensitive) and I insert data in these two columns as follows:
test
col2test
Test
col2test
would this uniqueness constraint be violated even though I'm using a case sensitive collation? I've done tests on this issue and I can't seem to insert this 2nd column; I'm just wanting to be certain that this is the case 100% of the time.
Thanks for your assistance,
Aaron
Hi,
Can anyone tell me the order in which uniqueness constraints on indexes are enforced vs. when triggers are executed ? I have a unique constraint on an index and a trigger on the column on which the same index has been created. When a row is inserted, the trigger checks if the value for that column already exists in the table - if not, it inserts the row as is, else it gets the max() val of the column (based on another key column) and increments it by one, then does the insert. Creating an index across the two works fine, but if I set the Unique Values property for the index, subsequent inserts bomb out - yet there aren't any duplicates in the final table, as the trigger ensures this. Anyone got any ideas on this? My deduction is that the uniqueness constraint gets enforced before the trigger gets executed, but at the same time this *seems* illogical, as the row has not been inserted into the table at the point where the trigger is executed.
Regards,
Jon Reade.
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.
Im having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).
So what am I doing wrong ?
Greetings,I want to bulk load data into user defined SQL Servertables. For this i want to disable all the constraints on all the userdefined tables.I got solution in one of the thread and did the following:declare @tablename varchar(30)declare c1 cursor for select name from sysobjects where type = 'U'open c1fetch next from c1 into @tablenamewhile ( @@fetch_status <> -1 )beginexec ( 'alter table ' + @tablename + ' check constraint all ')fetch next from c1 into @tablenameenddeallocate c1goNow when i try to truncate one of the tables (say titles) it gives methe following error:Cannot truncate table 'titles' because it is being referenced by aFOREIGN KEY constraint.Can anyone show me the right path? I am working on ASE 12.5TIA
View 4 Replies View RelatedI have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.
Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?
Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!
Hi, folks:
View 18 Replies View RelatedI have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)
Updates and cursors just seem to be too slow.
So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.
This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert
Any comments are appreciated,
-Marc
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
Hi~,
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
- any other guide lines
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies View Related
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
Hi all,
I'm trying to use a custom check constraint to insert into a table. I have created a function that checks an item against a authorization date as well and I've coupled this into the constraint. So for example, my function starts:
Code Snippet
ALTER FUNCTION {function1} (@ItemID INT, @AuthDate DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @Ret INT
SET @Ret = 0
IF NOT EXISTS(SELECT top 1 ItemID FROM {table1}
Where b1.ItemID = @ItemID
AND @AuthDate <= ISNULL(b1.expirydate, @AuthDate))
BEGIN
SET @Ret = 1
END
ELSE
BEGIN
SET @Ret = 0
END
RETURN(@Ret)
END
GO
Now i couple that into a check constraint for the same table:
Code Snippet
ALTER TABLE {table1} ADD CONSTRAINT {contraint_name} CHECK (([function1]([ItemID], [AuthorizedDate])=(1)))
GO
Now, when i insert a record into this table with an Authorized Date greater than the ItemDate it should set the @ret value to 1. This is being passed back to the constraint should set 1=1 which is true and therefore should allow an insert, however, i cannot do this. I think this is because it inserts the record and THEN does the check which makes it fail. Is there anyway to do this check before the INSERT without having to use a trigger? I dont want to use a trigger because when we're doing an insert for bulk rows, performance decreases correct?
In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,
Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.
Sincerely,
-Lawrence
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
Hello Friends
How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like which is describe below:
Error:
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. at MyGeneration.dOOdads.BusinessEntity.LoadFromSql(String sp, ListDictionary Parameters, CommandType commandType) at MyGeneration.dOOdads.BusinessEntity.LoadFromSql(String sp, ListDictionary Parameters) at LCO.pname.DAL.BLL.AccountInfo.funName(Parameters)
In above mentioned error, foreign key is availabe in child table. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance.
Regards,
Hi, I am new to MS SQL Server; as I know Access, MYSQL. I made a form though which I want to insert data to SQL SERVER 2005 Database but i during submission I get the below problem, can any one help.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The INSERT statement conflicted with the CHECK constraint "SSMA_CC$Bcast$msgHTML$disallow_zero_length". The conflict occurred in database "x485i", table "dbo.Bcast", column 'msgHTML'.
/html/n_.asp, line 193
Best Regards,
Imran
hi friends i am trying for bulk insert using SQL server 2000using this codebulk insert xyzfrom 'D:authors.txt'WITH (FIELDTERMINATOR = ',') but it gve me error saying thatCould not bulk insert because file 'D:authors.txt' could not be opened. Operating system error code 21(error not found). i check file securityit has given full control to the file can any one give me idea about Operating System error code 21(error not found) thanks
View 1 Replies View RelatedHi,I've a SP that insert records in one table and then call another insert SP on a second table. The first table is like a master table and the second is like a child table. After inserting the right record in the master table, I've to insert some record in the child table. This records differ each other only by two of about ten field, so what I'd want is not to call the second SP X times, but only one time.. Is it possible??ExampleTable1: Id (identity), Desc;Table2: Id (identity), Id_table1, Id_TableX, Num, Field1, Field2, ... Field10.In Table2 only Id_TableX and Num change every time... the other are all the same (for one record in Table1). How can I do? Probably with a bulk insert and a bulk update?? But, can I make a bulk xxx without a file??
View 3 Replies View Relatedhi friends i am using bulk insert cmd using my table name but i am facing error.....SO
IS IT POSSIBLE TO USE BULK INSERT WITH TEMPRARY TABLE VARIABLE
PLZ HELP ME
How can I do a bulk insert without the transaction being logged?
View 1 Replies View RelatedHi,
I want to move data from a text file to a SQL table. After DTS creates the table, does it use Bulk Insert to copy the data from the file to the table, or BCP?
Thanks,
Judith
I am trying to do a bulk insert from a data file into a linked access database. When I run the query I get the error message:
'Server: Msg 4801, Level 16, State 81, Line 1
Bulk_main: The opentable system function on BULK INSERT table failed.
Not sure what the problem is because BOL just says to check Microsoft.com for updated error message information. However, when I went to the site there was no updated information. Has anyone else seen this error? If so, have you figured out the problem? Any help would be greatly appreciated. Thanks.
Jim
Hi,
Which is a faster method -- bulk insert or BCP
And I assume DTS has no problem handling either one? The input files are between 400 meg and 1 gig.
Thanks,
Judith
How does one perform a bulk insert?
View 1 Replies View RelatedWe've been using Bulk Insert to load our tables. But, recently we encountered this error message "There is insufficient system memory to run this query. [SQLSTATE 42000] (Error 701). The step failed."
Then, our DBA suggested that we use BCP. It seems to work fine until the file size exceeds 30MB. This is the message I get "Starting copy... 0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total 14984. Process Exit Code 0. The step succeeded." Is this a known problem?
Then, we decided to use DTS. DTS seems to be able to handle any file size but it's a slower process than the other 2. Any suggestions?
Can anyone help me on how to do a bulk insert from a text file into sql server , some facility similar to
sql loader of oracle .. thank you in advance