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

How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor

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

Bulk Insert - Bulk Load Data Conversion Error

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

Cannot Fetch A Row From OLE DB Provider BULK With Bulk Insert Task

Nov 23, 2005

Hi, folks:

View 18 Replies View Related

Pros: How To Bulk Delete And Bulk Insert?

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

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

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

Bulk Insert W/Update (urgent)

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

Bulk Insert/Update Ideas

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

Bulk Insert Using Script And Not Bulk Insert Task

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

How To Do Bulk Update / Insert In A Table With Conditions

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

BULK INSERT ERROR Using Format File - Bulk Load Data Conversion Error

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

DB Engine :: Bulk Update Is Recorded As Delete And Insert In CDC Tables?

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

Bulk Insert / Update Operation - PRIMARY Filegroup Is Full

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

Questions About Bulk Copy Insert Using 'Memory Based Bulk Copy Operations'

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

Data Access :: Bulk Fetch Records And Insert / Update Same In Other Table With Some Business Logic

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

Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: Cannot Fetch A Row

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

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)

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

How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

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

Bulk Update HELP!

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

Bulk Insert

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

Something Like A Bulk Insert...

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

BULK INSERT

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

Bulk Insert ?

Dec 20, 2000

How can I do a bulk insert without the transaction being logged?

View 1 Replies View Related

Does DTS Do Bulk Insert?

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

Bulk Insert

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

Bulk Insert Vs BCP

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

Bulk Insert ?

Dec 5, 2000

How does one perform a bulk insert?

View 1 Replies View Related

BCP-vs-Bulk Insert-vs-DTS

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

Bulk Insert

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

Bulk Insert

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

Bulk Insert

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

Bulk Insert Every 3rd Row

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

SQL Bulk Insert Or BCP

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







Copyrights 2005-15 www.BigResource.com, All rights reserved