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)
Hello,I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_infoIn this Customer table had a primary key CustomerID , related with every other table with fkey.I want to insert data into four tables using one form having TABs .I created class and storedProcedures to insert row for each table.How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans. Thanking you,
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
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 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 dont 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 have created several global temp tables to cache some intermediate results ... However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?
Hello I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Question_SurveyTemplate". The conflict occurred in database "ankietyzacja", table "dbo.SurveyTemplate", column 'id'. The statement has been terminated. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ? Thanks a lot.
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
I currently insert into one table with: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable (UserName, Password, Email) VALUES (@person, @pass, @email)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text);but I realized that there's another table related to this table and I need to have something go in it so that the users data will be recorded at the same pace. So I tried: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable, FatherHistTable (UserName, Password, Email), (Father) VALUES (@person, @pass, @email), (@father)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text); comm.Parameters.AddWithValue("@father", fthrsNmeLbl.Text);Not working, so I am thinking I must do two inserts: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable (UserName, Password, Email) VALUES (@person, @pass, @email)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text); SqlCommand comm2 = new SqlCommand("INSERT INTO FatherHistTable (Father) VALUES (@father)", sqlConnection); comm2.Parameters.AddWithValue("@father", fthrsNmeLbl.Text); Is that the only way to go about it then? Thanks in advance for any explanations.
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 ?
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
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.
I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer. Similarly for all other rows in Customers.
HiLets say I have a user registration table and I want to know since when the member joined the club.How do I make the insert clause to include the date or date+time ?TIAGuy
I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?
in my database , the column timeRequest appear : 1/1/1900 11:59:05 AM I dont want the date by default to appear, i want only the time like : 11:59:05 AM in my database, Anyone can help me?
I would like to be able to insert a time value only into a SQL 7 table colum which has been set as a datetime datatype. When I insert '12:34:44 PM' into the colum it actually inserts '1900-1-1 12:34:44 PM'. An Access table will allow you to insert the time value without adding the '1/1/1900' date value. Can this be done in SQL7?
Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.
Here is what I have till now:
CREATE procedure Insert100RowsAtaTime AS SET NOCOUNT ON
declare @Col1 int declare @Col2 char(9) DECLARE @RETURNVALUE int DECLARE @ERRORMESSAGETXT varchar(510) DECLARE @ERRORNUM int DECLARE @LOCALROWCOUNT int
declare Insert_Cur cursor local fast_forward FOR SELECT top 100 Col1,Col2 from Table1 WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/ from Table2)
set @RETURNVALUE = 0 set @ERRORNUM = 0
BEGIN
open Insert_Cur fetch NEXT from Insert_Cur into @Col1, @Col2 while (@@FETCH_STATUS = 0) insert into Table2 (Col1,Col2) select @Col1,@Col2
SELECT @ERRORNUM = @@ERROR, @LOCALROWCOUNT = @@ROWCOUNT IF @ERRORNUM = 0 BEGIN IF @LOCALROWCOUNT >= 1 BEGIN SELECT @RETURNVALUE = 0 END ELSE BEGIN SELECT @RETURNVALUE = 1 RAISERROR ('INSERT FAILS',16, 1) END END ELSE BEGIN SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages] WHERE error = @@ERROR RAISERROR (@ERRORMESSAGETXT, 16, 1) SELECT @RETURNVALUE = 1 END
I'm trying to populate the sp_fact_sit_budget_test2 table with the employee_key from the base_employee_test2 table where the person_id's match and the the base_employee_test2 time_added_fis<= max sp_fact_sit_budget_test2 time_added_fis. So for the data shown below the sp_fact_sit_budget_test2 employee_key should get the value 312436.
CREATE TABLE [dbo].[SP_FACT_SIT_BUDGET_TEST2] ( [TIME_ADDED_FIS] datetime NULL, [PERSON_ID] int NULL, [EMPLOYEE_KEY] int NULL) ON [PRIMARY] GO
insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' CREATE TABLE [dbo].[BASE_EMPLOYEE_TEST2] ( [PERSON_ID] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TIME_ADDED_FIS] datetime NULL, [LAST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FIRST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EMPLOYEE_KEY] int NULL) ON [PRIMARY] GO
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-23 00:00:00','x','Guillermo',312436) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-13 00:00:00','x','Guillermo',332196) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-16 00:00:00','x','Guillermo',336180)
Hi all, I have a question about inserting records into sql server. Iam brand new to sql server, always using oracle previously. Beforetoday, I had always written statement such as this:INSERT INTO TABLE (COL1) VALUES SYSDATE;How is this accomplished in sql? I am using a datetime data type, Ihope that is correct . . .Thank you for your help.Ryanp.s. I tried getdate(), getdate, sysdate, and current_timestamp. Allto no avail :(
I have written a master proc which calls another proc (say proc1). This proc1 has insert-exec statements, for eg insert into #temp exec proc1. i.e. multiple times the proc would be nested.
This the err thrown : An INSERT EXEC statement cannot be nested.
