I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?
Sep 27, 2007
I 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!
View 1 Replies
ADVERTISEMENT
Apr 18, 2008
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.
View 8 Replies
View Related
Jan 17, 2008
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 ?
View 13 Replies
View Related
Oct 11, 2000
I 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
View 3 Replies
View Related
Nov 14, 2007
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
Aug 11, 2006
[EDIT #2]
Using this query:
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID)
select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}',
'{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID
From members
Inner Join groupLeaders ON members.memberID = groupLeaders.memberID
SELECT @@Identity AS UserID
How can I modify the portion that is inserting the '56' at the end of each username to do the following:
1) check to see if username already exists in the database (using a query with "LIKE %'")
2) if not, create the username "as-is" or how it should be without the number
3) if already exists, get a count of records matching your search criteria .... now make a new username + + (count + 1).ToString();
Any thoughts... I am struggling to put these two pieces together.
Thanks,
Zoop
[EDIT - original post below this]
I have modified my method to make this a bit easier. I added a memberID field to my [Users] table so that I can update my [Members] table in a difference statement after the insert takes place.
I have the following query, and it completes succesfully in query analyzer (though I haven't actually executed the SP, just testing the syntax...) anyway, here is what I have:
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2, UT_memberID)
select memberFirstName + '.' + memberLastName + '56' as userName, '{AxxxxxDE-6xx6-4xxD-Bxx9-3xxxx79xxxxE}',
'{4xxxxxx6-8xx5-6xxD-Cxx6-4xxxFxxx1xx9}', '{0xxx8xxE-Cxx4-6xx8-ExxB-Dxxxx4xxx2xC}', members.memberID
From members
Inner Join groupLeaders ON members.memberID = groupLeaders.memberID
SELECT @@Identity AS UserID
I am hoping this will create a user for all members whose 'memberID' can be found in the groupLeaders table... is this correct?
Also, notice the 56 being appended to the end of each username. I would like this to be a random number generated within a given range... can this be done? any advice?
Thanks,
Zoop
[Original post below - provide more background]
I have three tables involved with this insert/update:
[Members]
-memberID
-memberFirstName
-memberLastName
-UserID
[GroupLeaders]
-groupLeaderID
-memberID
[Users]
-UserID
-Username
-UserSalt
-UserHash1
-UserHash2
I want to insert into the [Users] table the memberFirstName.memberLastName + randomNum into the 'UserName' column from the [Members] table. Also, I want to make all passwords the same, in this case I know the Salt, Hash1, Hash2 I will be using and would like to pass these in for the 'UserHash1' 'UserHash2' fields.
Now, I only want to make this insert where the memberID is in the GroupLeaders table. and Finally, I need to Update my Members table with a UserID where the memberID matches the one used from the groupLeaders table.
Does anyone have any ideas on how I can accomplish this, even if it requires adding a temporary field to one of my tables... here is what I have so far, but am recieving errors and can't quite figure this one out. (btw - I also don't know how to gen the rand num and was using the literal 23 as a placeholder.) Thanks...
Code:
INSERT INTO Users (userName, UserSalt, UserHash1, UserHash2)
select a.memberFirstName + '.' + a.memberLastName + '23' + as userName, '{AA99FCDE-6E06-437D-B9E9-3E3D27955C3E}',
'{7xxxxxx2-4xx6-9xx1-7xx9-4x3xx4Axxx59}', '{0xx8xxE-Cxx4-6xxx-xxxx-Fxx3xxxx3xxF}', b.memberID as newMemID
From members a, groupLeaders b
Where a.memberID = b.memberID
SELECT @@Identity AS UserID
Update Members Set UserID = Ident_Current('Users')
where memberID = newMemID
Any help is appreciated!
View 2 Replies
View Related
Apr 8, 2004
I need a fresh set of eyes.
On a daily basis I need to perform a bulk update. Table totals about 50,000 records with approximately 5,000 changing (deletes, edits, and new records) per day. I'd like to push just the updates somehow, but VB is too slow and I haven't found a way in to handle it in DTS. Not much experience w/ DTS.
I'm transfering between two SQL 2000 servers w/ a VB app sitting in the middle.
Any ideas?
View 1 Replies
View Related
Nov 2, 2007
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.
View 6 Replies
View Related
Oct 30, 2015
I have Three tables Student,Daily_Attendance_Master and Daily_Attendence_Details.
I want to run sql of insert or update of student attendence(apsent or present) in Daily_Attendence_Details based on Daily_Attendance_Master_Id and Student_Id(from one roll number to another).
If Both are present in table Daily_Attendence_Details then i want to run Updating of attendance from one roll number to another roll number in Daily_Attendence_Details on the basis of Daily_Attendence_Details_Id
And if both or any one is not present i want to run insert of student attendense from one roll number to another roll number in Daily_Attendence_Details.
I give below the structure of three tables Student,Daily_Attendance_Master and Daily_Attendance_Details.
Student:-
CREATE TABLE [dbo].[Student](
[Student_Id] [bigint] IDENTITY(1,1) NOT NULL,
[Course_Id] [smallint] NULL,
[Class_Id] [int] NULL,
[Batch_Year] [varchar](20) NULL,
[Student_Initials] [varchar](20) NULL,
[Code] ....
View 13 Replies
View Related
Jun 29, 2015
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]...
View 5 Replies
View Related
Nov 18, 2015
I have a fundamental problem with how CDC works for bulk updates.When CDC enabled table is updated for single row - My CDC system tables its recording it as update (3 & 4) which is perfect and what it should be. No Complains!But when I do a bulk update in the same CDC enabled tables for the same columns - My CDC system tables its recording as delete and then insert (1 & 2). This is not correct and this is what my problem is. We used triggers before CDC we did not face this problem with triggers every thing was fine with triggers other than performance.The way how the CDC is handling the bulk update is a big problem for me because based on the output of CDC system tables we are doing some migration work to legacy system.
It will be impossible for me to go and change my migration logic scripts because we have 100's or procedures in it.Is it a know problem with CDC? Is there any solution in CDC when a bulk update happens on a table the CDC system tables record it as updates. I don't think CDC 'net changes' in this situation because the net change would show as single inserted row.If this can't be done with CDC then I have to completely abandon CDC and go back to triggers..
View 5 Replies
View Related
Jun 9, 2015
I am getting the below error message while performing Bulk Insert/Update operation.
Could not allocate space for object 'dbo.pros_mas_det'.'PK__pros_mas__3213E83F22401542' in database 'admin_mbjobslive' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
My Current SQL Server version :
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
My current database size crossed the limit size of 10 GB.
View 4 Replies
View Related
Feb 1, 2007
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
View 1 Replies
View Related
Apr 21, 2015
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
View 14 Replies
View Related
Apr 8, 2008
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
View 1 Replies
View Related
Feb 15, 2007
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;
}
View 6 Replies
View Related
Oct 12, 2007
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
View 5 Replies
View Related
Apr 25, 2008
I have an unusual question... I have a table that I have to select the top 300 records from, check the date on each record, and using an if... then... statement, loop through each record updating a field based on criteria.
The problem is that it takes so long to do this. Is there a way to populate an object and pass that object with the data to a SQL stored procedure to have the SQL server do all the updating as opposed to the application doing the record updating? Does that make sense? Here's a sample of the code and you'll see what I'm talking about.
Thanks in advance for any help or advice you can give.
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports CallTracker_AgingCheck
Module CallTracker
Sub Main()
GetWebAgingData()
GetWebCallsData()
End Sub
#Region "Get Data"
Public agingStatusNumber As New ArrayList
Public agingEscalationNumber As New ArrayList
Public callStatusNumber As New ArrayList
Public CallStatus As New ArrayList
Public Sub GetWebCallsData()
Dim dt As New DataTable
Dim Criteria As String = "SELECT TOP 300 CallID, TIMEOFCALL, Status, StatusNumber " & _
"FROM WebCalls " & _
"WHERE (Status = 'OPEN') OR " & _
"(Status = 'IN PROCESS') OR " & _
"(Status = 'WORKORDER') OR " & _
"(Status = 'PRIORITY') OR " & _
"(Status = 'PENDING') " '& _
'"ORDER BY TimeOfCall DESC"
Dim Fa As String = String.Empty
Dim fromDATE As String = String.Empty
Dim toDate As String = String.Empty
Dim ds As New DataSet()
Dim tbl As String = "WebCalls"
Dim x1 As Integer = 0
Try
Using cn As New SqlClient.SqlConnection(Database.SQLConnection)
cn.Open()
Using cm As SqlClient.SqlCommand = cn.CreateCommand()
cm.CommandText = Criteria
cm.CommandType = CommandType.Text
cm.Parameters.AddWithValue("@CALLID", Fa)
cm.Parameters.AddWithValue("@TIMEOFCALL ", Fa)
cm.Parameters.AddWithValue("@STATUS", Fa)
cm.Parameters.AddWithValue("@STATUSNUMBER", Fa)
Dim Age As TimeSpan = Nothing
Dim _totalHours As Integer = Nothing
Dim EscalateTime As Integer = agingEscalationNumber.Count
Dim DAUpdateCmd As SqlCommand
Using da As New SqlDataAdapter(Criteria, cn)
da.Fill(dt)
Dim callID As Integer = Nothing
Dim statNO As Integer = Nothing
Dim toc As Integer = dt.Rows.Count
Dim x As Integer = 0
For x = 0 To toc - 1
Dim y As Date = dt.Rows(x).Item(1) 'gets the date from the first row in WebCalls for comparison with WebAging
Age = Today.Subtract(y)
_totalHours = Age.TotalHours
If _totalHours > 744 Then
Console.WriteLine("This record is older than 30 days")
Else
callID = dt.Rows(x).Item(0)
DAUpdateCmd = New SqlCommand("Update WebCalls SET STATUSNUMBER = @STATUSNUMBER where CALLID = @CALLID", da.SelectCommand.Connection)
DAUpdateCmd.Parameters.Add(New SqlParameter("@STATUSNUMBER", SqlDbType.Int))
DAUpdateCmd.Parameters("@STATUSNUMBER").SourceVersion = DataRowVersion.Current
DAUpdateCmd.Parameters("@STATUSNUMBER").SourceColumn = "STATUSNUMBER"
DAUpdateCmd.Parameters.Add(New SqlParameter("@CALLID", SqlDbType.Int))
DAUpdateCmd.Parameters("@CALLID").SourceVersion = DataRowVersion.Original
DAUpdateCmd.Parameters("@CALLID").SourceColumn = "CALLID"
da.UpdateCommand = DAUpdateCmd
da.Fill(ds, tbl)
Dim z As Integer = 0
Dim _stat0 As Integer = agingEscalationNumber(0)
Dim _stat1 As Integer = agingEscalationNumber(1)
Dim _stat2 As Integer = agingEscalationNumber(2)
Dim _stat3 As Integer = agingEscalationNumber(3)
Dim StatusNo_0 As Integer = agingStatusNumber(0)
Dim StatusNo_1 As Integer = agingStatusNumber(1)
Dim StatusNo_2 As Integer = agingStatusNumber(2)
Dim StatusNo_3 As Integer = agingStatusNumber(3)
If _totalHours <= _stat0 Then
Try
ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_0
da.Update(ds, tbl)
Console.WriteLine("Status Number: " & Str(StatusNo_0) & " callID = " & callID)
Catch ex As Exception
Console.WriteLine(ex)
End Try
End If
If _totalHours >= (_stat0 + 1) And _totalHours <= _stat1 Then
Try
ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_1
da.Update(ds, tbl)
Console.WriteLine("Status Number: " & Str(StatusNo_1) & " callID = " & callID)
Catch ex As Exception
Console.WriteLine(ex)
End Try
End If
If _totalHours >= (_stat1 + 1) And _totalHours <= _stat2 Then
Try
ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_2
da.Update(ds, tbl)
Console.WriteLine("Status Number: " & Str(StatusNo_2) & " callID = " & callID)
Catch ex As Exception
Console.WriteLine(ex)
End Try
End If
If _totalHours >= (_stat2 + 1) And _totalHours <= _stat3 Then
Try
ds.Tables(tbl).Rows(3)("STATUSNUMBER") = StatusNo_3
da.Update(ds, tbl)
Console.WriteLine("Status Number: " & Str(StatusNo_3) & " callID = " & callID)
Catch ex As Exception
Console.WriteLine(ex)
End Try
Else
End If
End If
Next
End Using
End Using
End Using
Catch ex As Exception
Console.WriteLine(ex)
End Try
End Sub
Public Sub GetWebAgingData()
Dim dt As New DataTable
Dim Fa As String = String.Empty
Dim fromDATE As String = String.Empty
Dim toDate As String = String.Empty
Dim Criteria As String = "Select RecordID, StatusNumber, EscalationTime from WebAging" 'create a selection statement"
'don't forget the email addys for sending based on escalation rates.
Dim x1 As Integer = 0
Try
Using cn As New SqlClient.SqlConnection(Database.SQLConnection)
cn.Open()
Using cm As SqlClient.SqlCommand = cn.CreateCommand()
cm.CommandText = Criteria
cm.CommandType = CommandType.Text
cm.Parameters.AddWithValue("@RecordID", Fa)
cm.Parameters.AddWithValue("@StatusNumber ", Fa)
cm.Parameters.AddWithValue("@EscalationTime", Fa)
Using da As New SqlClient.SqlDataAdapter(cm)
da.Fill(dt)
Dim dtCnt As Integer = dt.Rows.Count
Dim x As Integer = Nothing
Dim escalateTime As Integer = Nothing
Dim _StatusNumber As Integer = Nothing
For x = 0 To dtCnt - 1
escalateTime = dt.Rows(x).Item(2)
_StatusNumber = dt.Rows(x).Item(1)
agingEscalationNumber.Add(escalateTime)
agingStatusNumber.Add(_StatusNumber)
Next x
End Using
End Using
End Using
Catch ex As Exception
Console.WriteLine(ex)
End Try
End Sub
#End Region
#Region "Get Status Information From WebCalls"
#End Region
End Module
View 4 Replies
View Related
Oct 27, 2006
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 Related
Aug 28, 2007
Hi,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 Related
Dec 28, 2007
hi 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
View 2 Replies
View Related
Dec 20, 2000
How can I do a bulk insert without the transaction being logged?
View 1 Replies
View Related
Sep 15, 2000
Hi,
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
View 2 Replies
View Related
May 19, 2000
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
View 1 Replies
View Related
Sep 12, 2000
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
View 3 Replies
View Related
Dec 5, 2000
How does one perform a bulk insert?
View 1 Replies
View Related
Dec 28, 1999
We'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?
View 1 Replies
View Related
Mar 14, 2000
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
View 1 Replies
View Related
Jun 13, 2000
howdy:
I keep getting the following error msg:
<<Bulk insert data conversion error (truncation) for row 17, column 6 (col6).>>
I created a test file of 100 rows in EXCEL and saved as tab-delimited and then I run the following from the query analyzer:
bulk insert ssdi..dmf FROM 'C:devpb6ccsssdiest.txt'
WITH
(DATAFILETYPE='char',
FIELDTERMINATOR='',
ROWTERMINATOR='',
KEEPNULLS,
MAXERRORS=10,
TABLOCK)
here's my table:
create table dmf
( col1 char(9) not null,
col2 char(12) null ,
col3 char(10) null ,
col4 char(8) null ,
col5 char(8) null ,
col6 char(12) null ,
col7 char(12) null ,
constraint pk_dmf primary key (ssn))
note:
I don't always have values in the last 2 columns
I installed SP2.
TIA
deanna
View 1 Replies
View Related
Jan 19, 2004
I often encountered to insert multiple records into a certain table, such as:
DESCRIPTION AMOUNT
SALARY 8000
ALLOWANCE 100
CASH GIFT 400
FOOD ALLOWANCE 460
SCENARIO:
I want to insert at one time the example written above into TBLcompensation table with the following fields,
ID(identity,1), DESCRIPTION ,AMOUNT
QUESTION:
Is there other ways to INSERT multiple records at one time?
MY SOLUTION:
I collected all records and concatenated it as one string with a special character separating between fields and rows. Then i do the looping on the STORE PROCEDURE. I believe this is not an effecient way.
View 1 Replies
View Related
May 26, 2004
I am trying to bulk insert every third row of a file into a table.Is there a way that I can do it?
View 5 Replies
View Related
Dec 8, 2004
Hi,
Does anyone know if it is possible when using SQL Bulk Insert or BCP will allow for a variable number of columns in the input csv file? Or is it a requirement for these two commands to have a fixed number of cols in the input file?
I'm using Bulk Insert with fieldterminator = '","' and rowterminator = '' for the delimited csv file, but my input file does not have a fixed number of cols, and is not padded out with the appropiate number of empty ",".
Is there a way around this?
Roger
View 2 Replies
View Related
