INSERT - One Record At A Time
May 2, 2007
Hi everyone:
Using Sql Server SQL 8
I'm trying to INSERT records into a "can software package" batch
table. I have a work-table that mimics the batch table. After
manipulating the records in the work-file I want to INSERT them into
the batch table.
The problem is the batch table in the can software has a trigger on
the batch table which is going to force me to INSERT one record at a
time. I've always been able to do an INSERT with no problem.
The batch table has pretty basic columns:
BatchID
BatchDate
SeqNumber
These three fields are the key and then just some miscellaneous
columns. Any easy way to loop thru my work-file to insert these
records. Never done a loop in SQL so an example would be really
really appreciated. I have a sequence number so I was hoping to do a
While loop but I really don't know enough about creating a loop to
make that call. Thanks in advance for any help.
View 6 Replies
ADVERTISEMENT
Dec 15, 2006
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)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
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!!!
View 1 Replies
View Related
Mar 1, 2006
Hi
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks
View 6 Replies
View Related
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Mar 26, 2008
Hi All,
I am trying to create package something like that..
1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I will be appreciated if you can help me...
View 3 Replies
View Related
Nov 12, 2007
Hi,
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).
Regards,
Mark
View 8 Replies
View Related
Feb 13, 2015
I would like to return the latest time stamp as well as the earliest time stamp out of a series of records.The key would be the same ID so i need a function or query that can search through each record and find the earliest time for that ID and the latest time.
ID LatestTime EarliestTime
1 05:00:00 02:00:30
I have achieved the latest time by using a RowNumber function and returning the latest record. The stickler is how to find the earliest record as well.
View 2 Replies
View Related
Dec 4, 2013
Code:
CREATE TABLE [dbo].[DigiTracker](
[DigitrackID] [int] IDENTITY(1,1) NOT NULL,
[contact_id] [int] NOT NULL,
[Code]....
-- not accurate b/c it returns 0 for all TimePerPageSec
SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec
FROM DigiTracker
GROUP BY contact_id, ZinePageNumber
time spent (in seconds) between each record
View 9 Replies
View Related
Sep 11, 2013
I have about 20 scripts to run as
exec script1 ...
exec script20
I want to know how long does each of the script piece take. So how can I keep the records of time spent and save them as a file or save them into a table?
View 4 Replies
View Related
Mar 19, 2008
I have a table with several datetime fields but they all list just the correct date. The time is always 12:00:00 AM. I don't have a field that records the actual time a record was created. I have a timestamp column, but it appears to get updated everytime the DB backs up. Is there a way using the date fields I have or otherwise, to determine the time a record was created?
View 1 Replies
View Related
Jul 1, 2001
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.
Thanks for your help
Jason
View 1 Replies
View Related
Mar 7, 2001
hello everyone
I have table that look like
CREATE TABLE GuestAccount
(
Id_Guest smallint,
SeqNo int IDENTITY(1,1),
account money Null
PRIMARY KEY(Id_Guest,SeqNo)
)
Q?
I want my SeqNo start from 1 for new Id_Guest
is it possible ?
tnanks
View 3 Replies
View Related
Aug 23, 2012
I am not sure how to go about doing this. I have a record that has a start time of 1 am and a stop time of 9pm (same day for simplicity) and I want to know how many hours during a peek time and how many were not.
For example, the application starts and inserts into the data base the start time of 1am and then the user stops the app at 9pm. Lets say the peek hours are 1pm to 7pm. I know i can do a date diff function to get how long the app ran for but how can I get the amount of hours it ran during the peek time? I know there has to be some mathematical solution to this but it is escaping me at the moment. I want to do this over many records so a a cte or pivot table is the end solution for performance.
View 11 Replies
View Related
Jan 5, 2014
I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.
I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)
On query run;
Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)
if none
Load the first record
View 6 Replies
View Related
Dec 11, 2007
Dear all,
This is a beginner's question so bear with me - I have a SSIS package that builds an Analysis Services fact table - depending on user input, this table is either re-built from scratch or any additional records since it was last run are created.
Where is the best place to store the time a package was last run - in a seperate table in the database or somewhere in the fact table?
Sam
View 3 Replies
View Related
Sep 28, 2007
Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!
View 6 Replies
View Related
Oct 4, 2000
Hi Fellow Listers:
Working with SQL 7 and Visual Basic 5.0:
We have experienced a lock situation and we do not know how it happened nor how to solve it:
When trying to update a record we get the following message.
Run time Error 3197:
The Microsoft Jet Database engine stopped the process because you and another user are attempting to change the same data at the same time.
And for sure, ONLY one user is connected at the time.
Apparently, the record is marked and can be read but CAN NOT be updated.
Fortunately the record CAN be deleted !!!
The only way we found was using the VISDATA.exe that comes with Vbasic, to delete the record and add a new record with the same information.
Since this condition makes any updating program to be aborted, it's a big problem.
How this happened?
Is there any way to prevent this?
Is there a way to detect this in advance?
Is there a way to correct this situation automatically, such as rebuild or check the Data Base?
Your advise will be greatly appretiated
TIA
Gerardo Alvarez
asaca@asaven.com
asaca@telcel.net.ve
Bienvenido a nuestro Site en http://www.asaven.com
View 1 Replies
View Related
Feb 2, 2002
Hi, I'm newbie in SQL, could somebody tell me how do I walk through a table one record at a time without using a cursor please.
Greatly appreciated.
Ann
View 4 Replies
View Related
Jul 11, 2013
I want to check a record in my table which has time ins and time outs feilds. I want to check on insertionof any new record that a new record should not be inserted on same timings and before 45 mins of timein and after 45 mins of timeout. How do i do it sql query ?
View 1 Replies
View Related
Jul 23, 2005
I'm writing a classic ASP application that records all logging of userlogins on our support site. The logging is a rolling window of how manypeople have logged in for a given month, i.e. tracked by a 'lastlogin'field so the tracking is done in a date range. So, for the month ofJanuary I would record lastlogin dates between January 1 and January31. My question is this...on the last day of the month (example beingJanauary)at 23:59:59PM I want to write the total number of users thathad a lastlogin date within the month of January and write that totalnumber to another table so I have a point-in-time figure for Januarylogins and how many users logged in in the month of January. How do Ido that? How can I have the sql server dynamically check the last dayof each month at 23:59:59PM and then run that query to obtain the totalnumbers users that logged in and then record that value to anothertable?? Do I use a SQL Job to do that?Any help would be greatly appreciated! Thanks!
View 1 Replies
View Related
Nov 6, 2015
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
View 6 Replies
View Related
May 26, 2015
How to delete a single record from 2 tables at a time.
View 3 Replies
View Related
Jan 11, 2007
hello,
I am trying to insert date and time into my table.
insert into <table_name> values('12/12/2006','12:23:04');
but it displays error at " ; "
can anyone help me to figure out the problem
Thanks a lot in advance.
Regards,
Sweety
View 3 Replies
View Related
Feb 24, 2006
Ok this is a simple question but i cant seem to find out how since i had the access of a query builder for web matrix.I have the following:A Table called Customer, with Cust_Name, Cust_phoneI have modified my CreateUserWizard to accomodate mroe text boxes which would finally store to vales for Cust_Name, Cust_phone.Question is how can i perform an INSERT query? or rather i noticed that the query builder doesnt allow me to, instead the query builder is towards inserting new records thur GridView and FormView.I've designed a query to handle wildcards i.e @Cust_Nameso i'm thinking if i can have a working insert statment i can do something liketbCust_name.txt = @Cust_Namethen finally perform the insert queryAny ideas? thanks
View 2 Replies
View Related
Dec 29, 2006
Hi,
I would like to know is it possible to combine these 2 statements into 1, as I want to insert a single record set in tblRefLockers.
statement 1:
INSERT INTO tblRefLockers (Locker_No)
SELECT Locker_No from tblLockersInfo
WHERE MemberNo = 1234
statment 2:
UPDATE tblRefLockers
SET Locker_Condition = 'BAD'
WHERE MemberNo = 1234
Can I do that?
Thanks,
KNVB
View 6 Replies
View Related
Sep 9, 2015
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)Â
Note: Both col1 columns are of type nvarcharÂ
View 6 Replies
View Related
Aug 4, 2007
Hello I’m using express edition to create my trail test
application. Below is the code that I have and I’m trying to insert data to the
database table named “Coin�.
Protected Sub
btnSave_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnview.Click
Dim s As
String = txtCname.Text
'Dim myConnection As New
SqlConnection(myConnString)
Dim desc As
String = txtCDesc.Text
Dim ConStr As
New SqlClient.SqlConnection
ConStr.ConnectionString = "server=testsqlinstance;
Integrated Security=True"
Response.Write("Connection string: " & ConStr.ConnectionString)
Try
Dim SelectQuery As
String = "SELECT
max(coinid) from coin"
Dim idval As
Integer = 0
Dim command As
New SqlCommand(SelectQuery, ConStr)
ConStr.Open()
idval = command.ExecuteScalar()
Console.WriteLine(idval)
idval = idval + 1
Dim InsertQuery As
String = "INSERT
INTO COIN(coinname, coinid, ebayid, ebaymember, ebaymemid , amount , coindesc)
VALUES('1992-Proof'," & idval & ",'eewerwer','sp6937','serwryana',67.70,'Matt
Proff of 1992- Mint Set')"
Dim command1 As
New SqlCommand(InsertQuery, ConStr)
command1.ExecuteScalar()
Dim S1 As
String = "Record
insert - Successful!"
Console.WriteLine(S1)
Catch ex As
Exception
Label2.Text = ex.ToString
Finally
ConStr.Close()
End Try
End Sub
This program is
throwing an exception (mentioned below)
Exception ---> System.Data.SqlClient.SqlException:
Invalid object name 'coin'. at
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection) at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj) at
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at
System.Data.SqlClient.SqlDataReader.get_MetaData() at
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,
RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult
result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at
System.Data.SqlClient.SqlCommand.ExecuteScalar() at _Default.btnview_Click(Object
sender, EventArgs e) in C:Documents and SettingsarshaMy DocumentsVisual
Studio 2005WebSitesWebSite1Default.aspx.vb:line 92
“Coin� is the table name – which is in SQL server. Kindly
help me to handle and overcome this exception.
Thanks
View 2 Replies
View Related
Jan 12, 2008
Hi
I have a table in sql server with a numeric field as Primary Key. When i insert a new record i need that primary key increments automatic (like access auto increment) because i want avoid the possibility of duplicate Primary Keys.
Is that possible?
Thank you
View 2 Replies
View Related
Jan 29, 2006
hi all,i have a question that how to get id afer insert a new record.i just read from the following article, but still can't get the meaning:http://forums.asp.net/1008337/ShowPost.aspx
and my code Dim sql_3 As String sql_3 = "insert into paper_type (name, enable,creation_date,lastupdate,user_id) values " sql_3 += "('" & name & "','" & enable & "', getdate() ,getdate ," & user_id & ")" Call OpenRs(sql_3) ' I want to get the id immediately, but how?? (i am using sql server) can somebody help me to solve the problem??thanks a lot
View 9 Replies
View Related
May 29, 2006
I'm trying to make a website that people can upload file to the server, then the webpage will automatically insert the username and the file name into the database. I have thought about this for couple days but still got nothing. can some one help me?
can some one post a very simple aspx file that can insert a record to the sql database with C#? Not the code that generated by ASP.net 2.0!
I want to see how to access the database manually.
Thank you.
View 2 Replies
View Related
Dec 9, 2002
I am trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the IF statement) but i need it to create multiple new records based on the IF statement (or something similar) i have in the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code.
the idea is, the hierachy structure in the tables are:
Location1
--> Part1
--> part2 etc
Location2
--> part1 etc
i want it so that when i input a new part it updates to all the locations. The locations all have the same parentguid (the one specified in the code)
With regards to the stored procedure it executes, this stored procedure creates the entry. I don't really want to change the stored procedure because other functions reference and use it.
My code:
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID2 VARCHAR(50)
select @parentguid2 = parentguid from dsdba.itemgroups
if @PARENTGUID2 = '8CF850AD-2026-411B-AABE-BF1584624EB3'
BEGIN
SET @PREFIX = 'JUST'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX
SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1
END
:confused:
View 1 Replies
View Related
Oct 3, 2003
Given the following code, why won't the information that I have at the end of my WHILE loop insert at the end of the table?
For some reason (there are over 7000) records being written to this table, the row that I am trying to insert is always included at row 73.
Any ideas? All I want to do is have it insert at the end of this script at the end of the table.
Thanks,
Steve
ALTER procedure sp_Cigna_build_export
as
Declare CignaExport Cursor
For
Select
exprectype,
expssn,
expfiller,
expbody
From cigna_employee_sort
Declare
@RecordType char(2),
@SSN char(11),
@Filler char(20),
@Body char(867),
@Counter int,
@CountLength int,
@RecordCount char(9)
select @counter = 0
Open CignaExport
Fetch Next from CignaExport
Into
@RecordType,
@SSN,
@Filler,
@Body
While @@fetch_status = 0
Begin
select @counter = @counter + 1
insert into cigna_export_table
(exportfield)
select
@recordtype + @SSN + @Filler + @Body
Fetch next from CignaExport
Into
@RecordType,
@SSN,
@Filler,
@Body
End
-- Adding two here accounts for the header/trailer records
select @recordtype = '99'
select @counter = @counter + 2
select @RecordCount = '000000000'
select @CountLength = len(@counter)
print @counter
insert into cigna_export_table
(exportfield)
select
@recordtype + stuff(@recordcount,10-@countlength,@countlength,@counter)
View 5 Replies
View Related
