HI
I have table with 20 columns. One of the column(col1) in the table has value like below
Col1 -----------Col2
123------------- 2
123--------------4
123--------------6
453--------------2
898--------------2
723--------------2
723--------------4
How to do insert or update increment of 2 in a column (col2) for every same value in the Col1?
I am using Access. Is it possible to with the query ?
I want to update a field and in this field insert a increment count, for example:
When I make, "Select * from Users order by User" displays:
User1 | NULL
User1 | NULL
User1 | NULL
User2 | NULL
User2 | NULL
and I want to do this:
User1 | 1
User1 | 2
User1 | 3
User2 | 1
User2 | 2
how to do this?
I'm populating a table (TMP_SQL) with data from several other tables in our database.
I need to create through SQL commands a counter variable to fill TMP_SQL's KEY1 field. The data I'm pulling in from other tables does not have a field that is always 100% gaurenteed unique, so I need to create a counter for a key field.
Pseudo code:
insert into TMP_SQL(tmp_key1, tmp_general1, tmp_general2, tmp_number1, ....)
select @counter, someColumn, someColumn2, (select column3 from other table returning 1 row),...
from table1 where things = things.
How can I implement @counter = @counter + 1? Is this possible? I tried:
DECLARE @COUNTER INT
SET @COUNTER = 0
and then had @COUNTER = @COUNTER + 1 in my select statement; of course, I got the error about not being able to set variables in the same select as data-retrieval
Computers never do what I tell them to...
This is on SQL 2000 and 2005. I need to increment a variable on insert, but am having a hard time getting a workable solution:
declare @var int
set @var = (select max(id) from table1)
insert into table1
select val1,
@var +1 as val2
from table2
where blah and blah and blah
If I were trying to insert val1's of (5, 10, 20) with @var having an initial value of 100, the added rows should be:
val1 val2
5 101
10 102
20 103
Does anyone know how to do this without temp tables (or table variables)?
I have a table with 4 fields,
Startdate, rec_num , recursive_value, recursive_date
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/02/2014
I will like to update recursive_date to emulate the recursive_number and recursive_value fields which specify every 3 days. The recursive_value field can also be w to specify weeks, m to specify month or y to specify years.
So my value in the recursive_date field should be updated as followed
Startdate, rec_number , recursive_value, recursive_date
04/02/2014 3 d 04/02/2014
04/02/2014 3 d 04/05/2014
04/02/2014 3 d 04/08/2014
Hi is there away of doing both my select and update of a field in my database (m not using store procedures)
Basically all I need to do is get the value of DownloadTimes then add 1 and re update it.
Also to stop missing increments what the best way to lock the database while an update is in progress?
This is basically firing after a user clicks a file to download. I bring up the dialogue for them to save it is it possible to get which button they press and what is the best way to deliver it to them eg response.writeFile()??
Thanks sorry about all questions.
Just when I though I knew what I was doing. I ran into a roadblock.I have two tables, organizations & usersI am building a form online for orgs to input their own information.How do I insert into both tables when the org table needs to know what theuser_id is, but the user_id hasn't been created until the form is submitted?Any help or request for additional info would be more than great.Thanks!
View 1 Replies View RelatedThe reason why cust_id started at #4 and not #1 is because I failed to insert property three times in a row for having "Tatoine" instead of "WI" or a state less than 5chars nchar(5) correct? Then when I did a valid statement, the row was created at the starting number of four. I imagine this prevents users from having duplicate cust_ids. This however is also where rollback and similar commands could be handy correct or is there something more obvious I'm missing on a failed "insert into" to not increment the cust_id. The three rows 1,2 and 3 do not exist I believe and are not null. Having null values would of contradicted the table where two columns "not null" are a requirement.
CREATE TABLE customersnew
(
cust_idINTNOT NULL IDENTITY(1,1),
cust_nameNCHAR(50)NOT NULL,
cust_addressNCHAR(50)NULL ,
cust_cityNCHAR(50)NULL ,
cust_stateNCHAR(5)NULL ,
[code]...
Hi guys,I followed the ASP.net official tutorial to create a DAL & Business Logic Layer (http://www.asp.net/learn/dataaccess/tutorial02cs.aspx). I have a table with a int ID field. I wish to write a function to add a new entry into this table but have the ID field auto-increment.The ID field is set as the Identity Column and has a Identity Increment & Seed of "1". If I manually go to the table and insert a new record leaving the ID value null it automatically increments. But if I create a C# function to add a new entry I get an error saying that the ID field can't be Null. Is there any way to use the Update method as shown on line 14 below to add a new entry but with it automatically incrementing? I did create a function called InsertDevice that simply inserts the other fields using a SQL INSERT and it auto-increments fine, just wondering if there is a way to do it using the DataTable and the Update method? Thanks for any help!!! 1 public bool AddDevice(string make, string model)
2 {
3 //cannot have the same device entered twice!
4 if (Adapter.FillDeviceCountByMakeModel(make, model) == 1)
5 return false;
6
7 RepositoryDataSet.DevicesDataTable devices = new RepositoryDataSet.DevicesDataTable();
8 RepositoryDataSet.DevicesRow device = devices.NewDevicesRow();
9
10 device.make = make;
11 device.model = model;
12
13 devices.AddDevicesRow(device); << Error thrown Here!
14 int rows_affected = Adapter.Update(devices);
15
16 return rows_affected == 1;
17 }
Hello,
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
Thanks,
Justin
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 RelatedI'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.
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
Hi,
Can anyone explain what the difference is and the advantages or disadvantages of using the below statements in my SQL paramaters please. Is there a performance hit if I use the second option?
If myCustomer.Phone2 IsNot Nothing Then
versus
If myCustomer.Phone2.Length > 0 Then
Thanks
Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.
I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.
I have a page where the user can update stock records. it has 5 x 5 text boxes. If the user has already entered stock before that stock will show up and they can change it and clicking the button it will update, however if they have just entered new data i would assume they would need to insert it, so how do i go about doing this? do i need to use both insert and update in the same sql string?
View 7 Replies View Relatedgreetings
I am developing an application for the marketing dept at my company.
Basically users can build the content of an email to be sent to our
subscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command:
Dim SqlMethod As String =
"INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)
VALUES ('" & txtCampaignName.Text & "','" &
calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &
DestinationsSelected & "','" & FreeTextBox2.Text & "', '"
& txtBannerPath.Text & "')SELECT @@IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String =
"UPDATE CZC_email SET SendDate = '" &
calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"
& txtCampaignName.Text & "',Destinations = '" &
DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text
& "' WHERE CZ_ID = @@IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
Cheers
I have a GridView on a page, It contains data from 2 joined tables and a command column to Edit/Update. I want to update the data in only one of the tables. If the data exists in the table to be updated there seems to be no problem (obviously). But I get an error when trying to update a record that does not exist (I should think so). Is there a way of Inserting the record before the update is fired? I have tried to do an insert contained in a 'Try' in the GridView1_RowUpdating, but this does not seem to work. as I still get the same error. Object cannot be cast from DBNull to other types. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Object cannot be cast from DBNull to other types.] System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider) +54 System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293 System.Web.UI.WebControls.SqlDataSourceView.AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString) +577 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +400 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1173 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1084 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +88 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +83 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +136 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +172 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4839
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
Try
Dim eventid As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(7).ToString)
Dim userid As Integer = CInt(hfID.Value)
Dim guests As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(4).ToString)
Dim attending As Boolean = CBool(GridView1.Rows(GridView1.EditIndex).Cells(5).ToString)
Dim extra As String = Server.HtmlEncode(GridView1.Rows(GridView1.EditIndex).Cells(6).ToString)
InsertRecord(eventid, userid, guests, attending, extra)
Catch ex As Exception
End Try
End Sub
Function InsertRecord(ByVal eventID As Integer, ByVal userID As Integer, ByVal guests As Integer, ByVal attending As Boolean, ByVal extra As String) As Integer
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='AFRA'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [AFRAAttendance] ([EventID], [UserID], [Guests], [Attending], [Extra]) VALUES (@EventID, @UserID, @Guests, @Attending, @Extra)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@EventID", System.Data.SqlDbType.Int).Value = eventID
sqlCommand.Parameters.Add("@UserID", System.Data.SqlDbType.Int).Value = userID
sqlCommand.Parameters.Add("@Guests", System.Data.SqlDbType.Int).Value = guests
sqlCommand.Parameters.Add("@Attending", System.Data.SqlDbType.Bit).Value = attending
sqlCommand.Parameters.Add("@Extra", System.Data.SqlDbType.VarChar).Value = extra
Dim rowsAffected As Integer = 0
sqlConnection.Open()
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
sqlConnection.Close()
End Try
Return rowsAffected
End FunctionThank you
Is it possible to run a DTS package so that it just updates existing rows rather than a full on insert?
I have an HR db as a source and a health and safety db as destination. records of peoples H+S acheivments and assessments are maintained in the H+S db but i need to keep this topped up with the newly recruited staff details and the existing staff change of details. The DTS need to know to insert where its a new record and to update where its just a change of name or work location etc.
if DTS wont do this, what are my options?
TIA
I have a form that pulls existing information from a database and allows users to edit it. Every field can be left blank if the user wishes. I am having an issue with determining which SQL statement to run. There has to be an easier way to do this then what I am doing. For each filed I process the data this way:
SQL2="select * from 1985ClassList where MailingListID =" & Session("EID")
set aData = oConn.execute(SQL2)
SQL = "Update 1985ClassList SET "
if aData.Fields("FirstName") > " " and Request.QueryString("FirstName") > " " then
SQL = SQL & "'FirstName' = '" & Request.QueryString("FirstName") & "', "
end if
if aData.Fields("FirstName") = " " and Request.QueryString("FirstName") > " " then
strFSQL = strFSQL & "FirstName, "
strVSQL = strVSQL & Request.QueryString("FirstName") & ", "
end if
SQL = SQL & "where MailingListID =" & Session("EID")
set mData = oConn.execute(SQL)
if strFSQL > " " then
SQL1 = "Insert into 1985ClassList " & strFSQL & "VALUES " & strVSQL
end if
set mData = oConn.execute(SQL1)
It works fine for the UPDATE, but not for the INSERT.
Hi,
Is it possible to Read data From a Table while Insertion/Update is happening on the table??
In case if this is not possible,Is there anyway to do that??
Thanks,
Karthik
Is there a way to insert / update in SQL Server 2000 with one command..
I know with Oracle there is a merge command....
I need a query to make an insert or and update in the same query
thanks
Scenario :
MS SQL 2005
Tables:
DETAIL --Here are all the detailed transactions
Customer
Item
Amount
...MoreFields
TOTAL --Here are the accumulated values (1 record by Customer-Item
Customer
Item
Accumulated
...MoreFields
What I want to know is the general logic to
-Read all records from table DETAIL
-If relationship Customer-Item exists in TOTAL table then UPDATE the TOTAL.Accumulated field adding the DETAIL.Amount field
-If relationship Customer-Item doesn't exists in TOTAL table then INSERT a new ecord with the values from DETAIL table
Because of my experience in VB and ADO my first try is to use a Cursor and loop record by record
But I know that is not the only and better solution
May You post some hint or some sample querie on how could be done
Thanks
JG
I'm new to DTS. I read some docs before adventuring into this matter.I still haven't found in all the docs I read if there is some "built-in" DTStask or function or wathever, to do a mixed "insert/update" import from asource, giving a unique field as primary key.I'll try to be more specific. The problem I would like to solve is this:I have a source file (csv) but it could be any source. I must check thisfile for all the records and compare them with the ones in the db (giving aunique field as a way of checking), so that all the records that alreadyexist, are UPDATED, and the others are INSERTED.I guess this is one most common task to accomplish, when you have a localbased application that you regularly update and then you want to export thedata to another "slave" application without using replication. But even ifthis sounds to me like a common task, I found no "buil-in" function for thatin DTS. I read something about "lookups" but it don't know if it's relatedsomehow.. it wasn't very clean.Thanks in advance for suggestions.--:: Massimiliano Mattei:: Project Leader:: E.xtranet V.irtual A.pplication:: www.evagroup.net :: www.commy.biz
View 3 Replies View Related
Hi Team,
I would like to import a .csv file into my existing table. On the DTS, I would like to check if the record already there then updated, on the other hand, if the record wasn't there then add it. How can I do that using DTS. Please help and thanks.
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
I am having an issue with this SQL Statement i am trying to write. i want to insert the values (Test Facility) from CCID table INTO CCFD table where CCID.id = CCFD.id this is what i have.UPDATE CCFD.id SET TEST_FACILITY = (SELECT CCID.id.TEST_FACILITYFROM CCID.idWHERE CCID.id = CCFD.id)orINSERT INTO CCFD.id(Test_Facility)SELECT TEST_FACILITYFROM CCID.idWHERE (CCFD.id.INDEX_ID = CCID.id.INDEX_ID) thanks in advanced
View 1 Replies View RelatedIs there a way I can stop a form from inserting or updating a record when there is an error. I have an sql 2000 DB. I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox. The record is not saved. None of the fields are saved. I do notice the autonumber generated is skipped by the db. That is, the next autonumber for a successful insert skips the logical next number. How do I capture this error, or any save error and return the user back to the form? Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors. I use asp.net 2.0 and VB. I don't know C#. thanksMilton
View 5 Replies View RelatedHi,
Is it possible to group all my update/insert (one table) ?
Cause I hope to have all the update/insert in one Trigger execution. I need to sum up some figures here.
I am using ASP.NET 2.0, SQLOLEDB and SQL 2005.
Please Help and Thank you.