Hello guys,
I have several rows I need to update OR insert. Right now, the code deletes all the "possibles" rows and then inserts.
I don't like this way because the index would be recreated each time it does do a DELETE/INSERT action.
Is there any "good practice" when inserting/updating rows? Any better way to do it?
Thanks.
I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.
Hello,
I seem to have a strange problem in that my code runs fine, but I am trying to INSERT rows into a database, and the rows affected by the INSERT command is alwsy zero, even though no error is thrown. Hence no data gets inserted.
Here is the code:
void recordTick(string pair, DateTime time, decimal sell, decimal buy)
{
SqlConnection thisConnection = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\FOREX.mdf;Integrated Security=True;User Instance=True");
insert = "INSERT INTO " + pair + " VALUES(" +
"'" + time.ToString("yyyy-MM-dd HH:mms") + "'," +
"'" + sell.ToString() + "'," +
"'" + buy.ToString() + "'" + ")";
thisConnection.Open();
SqlCommand command = new SqlCommand(insert, thisConnection);
Int32 success = command.ExecuteNonQuery(); // success always = 0!
thisConnection.Close();
}
The database is type .MDF that I created with the Project/Add Component/SQL Database menu. I can inspect it easily in Server Exporer and add data manually with no problem.
Can any one suggest how to get the INSERT to actually add the data?
Any help much appreciated!
Anding
Hello,
Is it possible to insert rows into a view, update rows in a view and delete rows in a view?
If so, how does T-SQL handle it when a VIEW is the result of following query?
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 ON table_name1.col = table_name2.col
WHERE condition
Thanks in advance,
Erke.
I am using SQLDMO to implement a tool to do basic management/script running for a MSDE database. The problem i am having is getting the number of rows affected from insert,update,delete statements. Help and MSDN seem to imply that this info is returned in a QueryResults object, but it is always empty unless the query is a select statement. I tried using ExecuteImmediate, ExecuteWithResults, and ExecuteWithResultsAndMessages methods and the info is not in any resultset, message, or property.
mikem
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
Hi All
I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.
I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.
I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.
Details:
VB2008 Pro
.NET Frameworks 2.0
SQL Compact Edition V3.5
Encryption = Engine Default
Database Size = 128Mb (But needs to be changes to 999Mbs)
Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are nvarchar and size 10 to 30 expect for Full Folder Address size 260
TRY1
Direct Insert Using Data Adapter.
Me.BackupDatabaseTableAdapter1.Insert(Group_Name1, Full_Folder_Address1, File1, File_Size_KB1, Schedule_To_Run1, Backup_Time1, Last_Run1, Result1, Last_Modfied1, Last_Modfied1, Backup_On_Next_Run1, Total_Backup_Times1, Server_File_Number1, Server_Number1, File_Break_Down1, No_Of_Servers1, Full_File_Address1, OverWriteQuick, CompressAns)
14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)
TRY 2
Using Record Sets
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM BackupDatabase"
cmd.ExecuteNonQuery()
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
rec.SetString(1, Group_Name1)
rec.SetString(2, Full_Folder_Address1)
rec.SetString(3, File1)
rec.SetSqlString(4, File_Size_KB1)
rec.SetSqlString(5, Schedule_To_Run1)
rec.SetSqlString(6, Backup_Time1)
rec.SetSqlString(7, Last_Run1)
rec.SetSqlString(8, Result1)
rec.SetSqlString(9, Last_Modfied1)
rec.SetSqlString(10, Latest_Modfied1)
rec.SetSqlBoolean(11, Backup_On_Next_Run1)
rec.SetSqlString(12, Total_Backup_Times1)
rec.SetSqlString(13, Server_File_Number1)
rec.SetSqlString(14, Server_Number1)
rec.SetSqlString(15, File_Break_Down1)
rec.SetSqlString(16, No_Of_Servers1)
rec.SetSqlString(17, Full_File_Address1)
rec.SetSqlBoolean(18, OverWriteQuick)
rec.SetSqlBoolean(19, CompressAns)
rs.Insert(rec)
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€™20 to 24 rows per sec
TRY 3
Using SQL Commands Direct
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = CommandText1
'cmd.CommandText = "INSERT INTO BackupDatabase (€¦"
cmd.ExecuteNonQuery()
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€˜ 25 to 30 but mostly holds around 27 rows per sec I
Is this the best you can get or is there a better way. Any help would be greatly appericated
Kind Regards
John Galvin
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
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 RelatedHi All,
From what I know, when you execute an
INSERT INTO table1 (field0)
SELECT field0 FROM table 2 ORDER BY field0
the rows are inserted in whatever order the server engine thinks is better at that moment. Is it any way I can have field0 in table1 inserted in the order I need?
My problem is that I can not touch table1, it is used by a legacy application which I am not allowed to modify. I was thinking about creating a clustered index, adding an id field, etc, but I can not know how this will affect the front end application.
table1 and table2 have only a few hundred records.
table1 =
CREATE TABLE [FinalPlasma] ([name] [varchar] (2000) NULL )
table2 =
CREATE TABLE [Test_FinalPlasma] ([nameID] [int] NOT NULL ,
[name] [varchar] (2000) NULL
)
The update that is not giving the "good" order =
DELETE FROM FinalPlasma
INSERT INTO FinalPlasma SELECT name from dbo.Test_FinalPlasma
Unfortunately I can not order the [name] field after the update, because it looks something like
Donald McQ. Shaver
Mark Sheilds
R.J. Shirley
W.E. Sills
Kenneth A. Smee
A. Britton Smith
LCol Edward W. Smith
Harry V. Smith
M. E. Southern
Timothy A. Sparling
Spectrum Investment Management Limited
Thank you,
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
View 4 Replies View RelatedCan I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
Hi,
I have a table called Past with 4 columns(Number,WW,Goal,Eng) and in Number column has 53 rows, 1,2,3 - 53. Now i need to update the 53rd row with the following code and it works. Since i dont know how to combine this i update them separately.
Update Past
set WW = ('WW'+convert(varchar(10),datepart(wk,getdate())-1))
WHERE Number = '53'
Update Past
set Goal = (Select Eng_Goal from AverageEngTime)
WHERE Number = '53'
Update Past
set Eng = (SELECT ((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
WHERE Number = '53'
Now, whenever i execute, I want my row 52 to be updated with data from row 53 and row 51 with data from row 52 and so on until row 1 with data from row 2. Meaning i just want to update new data for row 53 and other rows should be updated with the data from next line. How do i do that? Please help..Thanks
Hi,I'm using table it has 100 rows, how can i update 10 or 15 rows at thesame time with single query. In real case, if user enters "5" intextbox it means that the "5" rows will be updated.In SAS i was using 'outobs' {outobs = n} which means n rows will beaffected.Thanx.
View 7 Replies View RelatedHey guys, i need to insert 10,000 rows into the database and I am wondering what the best way to do that is. Should I create a StringBuilder and build the 10,000 insert statements and execute it in 1 connection. Or should I be looking into creating a dataset and use an adapter to insert? Any ideas? Please only answer from experience and provide a sample in c# Thanks.
View 4 Replies View Relatedi have a table with about 40 columns in access 97( terrible i knw, butits not mine and im not allowed normalize or change it) . i have addeda column , which is a number , called year. it will contain 1997,1998, etc. there are about 1600 rows in the table. is there anythinglike a correlated sub query that will allow me to insert this into thedate column of each row??
View 2 Replies View RelatedHi
I'm using the SQL Server 2005 Express edition.
I've manually created some rows in a table "Books".
If I right-click on "dbo.Books" and choose "Script Table As --> Create To --> New Query Editor Window", I get the SQL for creating that table.
But how do I get the INSERT-SQL for the rows already inserted?
Kind Regards
Hello,I ended up with two identical rows in one table. They should have differences but I cannot update one, as it tries to update both of them, or throws an error. How to update only one row, and leave other as is?
View 10 Replies View RelatedAny idea how to fix my code. I am getting this error message below....
Server: Msg 512, Level 16, State 1, Procedure TrigRetReqRecIDP1, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Set @intRowCount = (select count(*) from RequestRecords where REID = @REID)
While (select @REID from RequestRecords) != @intRowCount
Begin
select @intRRID = (select REID from RequestRecords where REID=@REID and RRStatus = 'PE')
Exec TrigAssignImpTaskNewP1 @intRRID, @REID
End
Hi
I would like update the rows of table 1 with the values in table2 see below based on a matching ID. I don’t want to update the ID field as this would be a violation of the primary key I assume as ID is a primary key, just the FirstName and SecondName fields need updating.
Table1
(ID, FirstName, Surname)
Table 2
(ID, FirstName, Surname)
Any ideas?
Cheers
ok my data base has 4 columns id,fname,lname,email and 3 rows 1,2,3
I made a simple update form that accesses the data and displays all three rows with the data in them:
<cfquery name="QUIZ" datasource="test">
SELECT id,fname,lname,email
FROM info
order by id
/cfquery
<HTML>
<HEAD>
<TITLE>Update an Employee</TITLE>
</HEAD>
<BODY>
<H1>Update an Employee</H1>
<FORM ACTION="databaseupdater.cfm" METHOD="POST">
<cfoutput query="QUIZ">
<INPUT TYPE="hidden" NAME="id" VALUE="#id#">
<P>
First name:
<INPUT TYPE="text" NAME="fname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(fname)#">
<BR>
Last name:
<INPUT TYPE="text" NAME="lname" SIZE="15" MAXLENGTH="30" VALUE="#Trim(lname)#">
<BR>
E-Mail:
<INPUT TYPE="text" NAME="email" SIZE="15" MAXLENGTH="30" VALUE="#Trim(email)#">
<P>
</cfoutput>
<INPUT TYPE="submit" VALUE="update">
<INPUT TYPE="reset" VALUE="Clear">
</FORM>
</BODY>
</HTML>
you can use this form to send the updated data to databaseupdater.cfm:
<CFQUERY
DATASOURCE="test"
>
UPDATE info
SET fname='#fname#',
lname='#lname#',
email='#email#'
</CFQUERY>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
Thank You
</body>
</html>
the problem Grieg RN is each column in the database gets updated with all 3 rows of data in each column in stead of seperating the data in its correct row and column. I don't know much about the problem im just starting out please help. Thanks
Hi all,
I need to write an SQL statement to update multiple row (all) as follws and can just not get it right please help.
I have two fileds - "Tilte" and "Title1" in my "notes" table.
I would like to run through the entire table and replace the information in "Title1" with that of "Title", also I need to change a character whilst doing so , ie change a "=" to a "-"
Example
Title - "Will is coming to town = Paul"
need to copy this to Title1 and it must change to...
Title1 - "Will is coming to town - Paul"
Running MS SQL2000 SP4
Help appriciated
Thanks
I have to update the domain name on our email database. Is there a simple update I can use to change firstname.secondname@olddomain.com to firstname.secondname@newdomain.com. I've got about 2800 records to update and I don't fancy either loosing them all by doing a dogy update query or doing it one at a time.
Any help would be greatly appreciated................
We have a table and it is having records with for example “Chase“, now we don’t want those “ “ and we want just Chase instead, so how can we update the records? Is there any other easy way to do than cursor?
Here I have got code for Cursor, but I am not getting the logic what to keep. And the code is here
declare @tbl varchar(200)
declare cur cursor for select name from sysobjects where type='U' and name like 'NS%'
open cur
fetch next from cur into @tbl
while @@FETCH_STATUS = 0
begin
print @tbl
--exec sp_spaceused @tbl
fetch next from cur into @tbl
end
close cur
deallocate cur
Can you please send me the logic?
It will be more appreciable.
-Thanks N Regards,
Kanthi.
My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.
I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.
SQL Server 2005
Table (many columns left out for simplicity)
ID - unique.
HdrID - key to the header record.
PTtimestamp - date, date/time the row was written to DB.
PType - integer, representing various states of the row.
etc
etc
etc (another 15 columns of data)....
Typically there is approximately 250 records per HdrID.
I am trying to do an SQL UPDATE without using my usual solution of writing vb code!
I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.
ie;
ID HdrID PTimestamp
001 001 01/01/2008 09:00:00
002 001 01/01/2008 09:10:00
003 001 01/01/2008 09:20:00
004 002 01/01/2008 09:00:00
005 002 01/01/2008 09:10:00
006 002 01/01/2008 09:20:00
In the above example I want to update the PType field = -9999 for record ID=003 & ID=006. In reality the table has tens of thousands of rows.
Jake
IUpdating existing rows in a SQL server 2005 destination table
--I have a CSV file and every row needs to perform an update on a production table
The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ???
That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow??
thanks in advance
Dave
We have a table and it is having records with for example €œChase€œ, now we don€™t want those €œ €œ and we want just Chase instead, so how can we update the records? Is there any other easy way to do than cursor?
Here I have got code for Cursor, but I am not getting the logic what to keep. And the code is here
declare @tbl varchar(200)
declare cur cursor for select name from sysobjects where type='U' and name like 'NS%'
open cur
fetch next from cur into @tbl
while @@FETCH_STATUS = 0
begin
print @tbl
--exec sp_spaceused @tbl
fetch next from cur into @tbl
end
close cur
deallocate cur
Hi All,
In SQL Server Database I have a table, which DO NOT have any primary Key field.
How can I update rows distinctly?
There is a possibility of having duplicate rows.
Do we have any alternate in SQL Server like ROWID (in ORACLE).
Regards
Sidheshwar.