i've read the transact-sql command, i known that the select command use to retrieve many fields from many tables with one command select * from table1,table2 yes, but i ' ve not seen the way to add,delete or update those fields from those tables with one command...
Is it possible? why? I don't have any idea , can u help me I want to know the sql commands , if it's possible
I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE] @QuePoolID int=null ,@ApptTime time(7) ,@SendOnDate datetime
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open() sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table scb = New SqlCeCommandBuilder(sda) sda.Update(dataset) cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open() Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */ /* SELECT GETDATE() GO SELECT CURRENT_TIMESTAMP GO SELECT {fn Now()} GO */ /* Use these statements to delete the tables to allow recreate of the tables */ /* DROP TABLE COMMIT_TEST DROP TABLE COMMIT_TEST_UPDATE */ /* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */ CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */ GO CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */ GO /* Use these statements to delete the triggers to allow reinsert */ /* drop trigger LOG_COMMIT_TEST_INSERT drop trigger LOG_COMMIT_TEST_UPDATE drop trigger LOG_COMMIT_TEST_DELETE */ /* Create insert, update and delete triggers */ create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as begin declare @time datetime select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE) select PKEY, getdate() from inserted end GO create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as begin declare @time datetime select @time = getdate()
update COMMIT_TEST_UPDATE set LAST_UPDATE = getdate() from COMMIT_TEST_UPDATE, deleted, inserted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end GO /* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */ create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as begin if ( select count(*) from deleted ) > 0 begin delete COMMIT_TEST_UPDATE from COMMIT_TEST_UPDATE, deleted where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY end end GO /* Delete any previous inserted record to avoid errors when inserting */ DELETE COMMIT_TEST WHERE PKEY = 1 GO /* What is the current date/time */ SELECT GETDATE() GO BEGIN TRANSACTION GO /* Insert a record into the primary table */ INSERT COMMIT_TEST (PKEY) VALUES (1) GO /* Simulate additional processing within this transaction */ WAITFOR DELAY '00:00:10' GO /* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */ COMMIT TRANSACTION GO /* get the current date to show us what date/time should have been committed to the database */ SELECT GETDATE() GO /* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */ /* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */ SELECT * FROM COMMIT_TEST GO SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
I do realize that his could be posted in a few spots but I think the answer is in the SQL. I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting. ex: <asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox> I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together. I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters. Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update. There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it. wbochar
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.
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.
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.
Can 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 using ASP to insert/delete/update rows into a very simple SQLServer database (2000).When a certain amount of text (as little as 1000 chars) is inserted tothe table (the insert works fine) ANY update call to that row will timeout.I can set the time out for 5 minutes and it still times out udatingeach field to to null.Woudl this have anything to do with row locking?Has any one ran into this problem and solved it?Thank for your help,MB
hi.. i want to store value of time in the database i.e. the 'time in' and 'time out' value for a particular entry of an employee. i have take datetime as a datatype in MS SQL 2000 database and my language is vb.net. How can i store time value in my database?
Is there any way in SQL Server 2005 to store only time and not date ? Even if there is no datatype which supports it, is there a round about way of doing things ?
hii,,i am using asp.net 2005 and sql server 2005,,i have a page called as demo.aspx..in that i have used sqldatasource and gridveiw to display the data from more than one table,,i have also given the edit,update and cancel commands thr gridview,,prob is how can i update data in more than one table whn i click on update link,,heres the select code so tht u can get an idea what all tables i have used ,,how to write the update code for the same,,need help,,thnks in advance SELECT SME_Master.FirstName, SME_Master.LastName, Agency_Master.Agency_Name, Certification_Master.Cert_Name, Certification_Details.Cert_Date, Certification_Details.Percentage, SME_Master.SME_Id FROM SME_Master INNER JOIN Certification_Details ON SME_Master.SME_Id = Certification_Details.Sme_Id INNER JOIN Certification_Master ON Certification_Details.Cert_Id = Certification_Master.Cert_Id LEFT OUTER JOIN Agency_Master ON SME_Master.Agency_id = Agency_Master.Agency_Id WHERE (Certification_Master.Cert_Id = @Cert_Id) _____________________________ pls reply asap...
I would like to limit the number of pages a user can view on my website each day. The users logs in and I can count the number of pages viewd in a field but i want to know how i can set the page count field to reset to 0 at the end of the day (ie midnight). Is it possible to do this? and if so how? Thanks.
Hi, I'm trying to use the Update command to change a field value. I only know the field name at run-time, how do you write this command. This is what I have done which just sets the variable @cFieldName to the value and not the field within the table.
UPDATE [Atable] SET @cFieldName = @aValue WHERE ([Id] = @Id_1)
We are using a java codebase which writes data to a SQL Server 2000 databaseusing the JTurbo (3.0) JDBC driver. We are finding that a record updatetakes longer and longer as the table grows. For example, when the table has~200,000 records, the update will be around 1 second. Once table is up to~800,000 records, an update starts taking 4-5 seconds.However, if I run the same update using SQL Query Analyzer, it runs insub-second time.Could this be something to do with the way the driver handles the update? Iwould expect the results to be the same as if I ran updates with SQA.Any input would be appreciated.Thanks,Frank
Hi,I have created a wizard form to collect user information.When use click Finish button all the details added to the SQL server database.That part is ok.But my problem is this Through my interface I am giving user to select the date and time.(I have used AJAX datepicker and MaskedEdit control)After user type the date (Normal format is - HH:MM:SS) and click finish button I check the database.It automatically Inserted the date also (Jan 1 1900)So I dont want this date part and I just want Time only.How do I do this ??(I have used datatype as : nvarchar instead of datetime .Because of if i use datatime it automatically inserts both data and time values)
Hello, I have a textbox on my formview which brings in a time from an sqldatasource. The time is displayed as 09:00:00 in the sql select statement i have a convert function - CONVERT (varchar, Time, 8) I am trying to alter the text e.g change to 09:30:00 and send it back to the database however i am having a lot of problems with that. Anyone know what i have to do to sort this. I think it might be something to do with the sql insert or the insert parameters as i dont know how to change the time String back to DateTime. Any help appreciated. Thanks in advance Mike.
my problem is that while i insert Time into datetime coloumn , it shows only 01/01/1900.it doesn't not shows '01/01/1900 12:00:00 AM' I run the following query insert into table1 values('1900/01/01 12:00:00 AM','1900/01/01 11:59:59 PM')result column1 Column2 01/01/1900 01/01/1900 11:59:59 PMhow can i solve this problem
i need to insert data into 2 tables. first in one, and the id of the register i just inserted is a field from the register in the other table (+ other data). inserting in this 2 tables should be invisible to the user so it has to be done automatically. the dumb way i guess would be using 2 ADODB.recordsets (rs,rs1). first insert in one store the id in a var (after rs.update, rs.movelast, var=rs.fields("id")) and after this inserting a register in the new recordset (rs1)
Hi everyone:Using Sql Server SQL 8I'm trying to INSERT records into a "can software package" batchtable. I have a work-table that mimics the batch table. Aftermanipulating the records in the work-file I want to INSERT them intothe batch table.The problem is the batch table in the can software has a trigger onthe batch table which is going to force me to INSERT one record at atime. I've always been able to do an INSERT with no problem.The batch table has pretty basic columns:BatchIDBatchDateSeqNumberThese three fields are the key and then just some miscellaneouscolumns. Any easy way to loop thru my work-file to insert theserecords. Never done a loop in SQL so an example would be reallyreally appreciated. I have a sequence number so I was hoping to do aWhile loop but I really don't know enough about creating a loop tomake that call. Thanks in advance for any help.
I need a little help. I want to load data to sdf database, with Visual Studio 2008. The database is on my PC. I want to copy to PPC, when it has the data. I thought it is easy... First I created a windows form application, and the database. I tried with TableAdapter.Insert(..), no exception, or error, but the database was still empty. DataSet.table.Rows.Add(newtableRow)+tableAdapter.Update works the same. My second attempt was to create a dataGridview. It can show data from the database, but can't insert. No error, but when I restart the application, the new row disappear. (Of course insert function is enabled.)
I was not able to realize the problem. The db is not read-only, and the conncection string works fine, and dataTableAdapter's insert method is also good.
We have a website that allows people to enter tasks and they are then given a stageID. ie 1 which is review for when they are first entered into our database. If they have not been reviewed in 5 minutes I want them to update automatically and move onto the next stage. I have tried using a stored procedure that adds the reocrd with a waitfor command but that doesn't work because it locks the connection ofcourse until it's finished the stored procedure which means the webpage doesn't come back for 5 minutes which we don't need. I'm asking if there is any other way I can do this without a waitfor command or somehow use it so that it doesn't hold the current connection.
