inserting 100 records

How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false

You mean like:

INSERT INTO t1(c1,c2)

SELECT '1','2'

UNION

SELECT '3','4'

UNION

...

?

|||

This will turn off the identity column for a table,

set identity_insert <tablename> on

[insert 100 records .. ]

set identity_insert <tablename> off

|||

No i mean if identity column is off i.e.., the we should explicitly insert ID column by fetching an XML having 100 records for example

Table1

ID StudRollNo StudName

Inserting into table1(Identity column for column ID is OFF) where i will get the XML of table having 100 records like

ID StudRollNo Studname

|||If you mean to read data from XML into datbase table,?I?suggest?you?learn?XQuery?in?SQL2005

insert/update timestamp in a SQL server 2000 db programatically

Hi,

How can i store the record insert/update timestamp in a SQL server 2000 db programacally. ? what are the date/time functions in ASP.NET 2.0 ? I know that this can be done by setting the default valut to getdate() function in SQL, but any other way on ASP page or code-behind page ?

Thanks,

Alex

You can use

string

s =DateTime.Now.ToString("dd/MMM/YYYY");

and then put it into the relevant parameter for your SqlCommand

|||Yes, that is correct if i assume i put that line of code in the code-behind page. But what will the syntax be if i need to use the same in the aspx page

My insert statement is as follows:

InsertCommand="INSERT INTO [StudentRegistration] ([RegDate], [FirstName], [SecondName], [FamilyName], [Photo], [CourseId], [MorningClass], [AfternoonClass], [Block], [Street], [HouseAptNo], [Area], [POBox], [PostalCode],, [HomePhone], [Mobile], [WorkPhone], [BirthDate], [Gender], [Nationality], [MaritalStatus], [CivilIdNo], [ExpiryDate], [ContactName], [ContactTel], [ContactMob], [ContactEmail], [MedicalCond], [CompleteHS], [CompYear], [ExpDate], [WhichSchool], [SchoolType], [Other], [QualTitle1], [QualInst1], [QualComp1], [QualTitle2], [QualInst2], [QualComp2], [QualTitle3], [QualInst3], [QualComp3], [QualTitle4], [QualInst4], [QualComp4], [Notes], [DateAdded], [AddedByFK]) VALUES (@.RegDate, @.FirstName, @.SecondName, @.FamilyName, @.Photo, @.CourseId, @.MorningClass, @.AfternoonClass, @.Block, @.Street, @.HouseAptNo, @.Area, @.POBox, @.PostalCode, @.Email, @.HomePhone, @.Mobile, @.WorkPhone, @.BirthDate, @.Gender, @.Nationality, @.MaritalStatus, @.CivilIdNo, @.ExpiryDate, @.ContactName, @.ContactTel, @.ContactMob, @.ContactEmail, @.MedicalCond, @.CompleteHS, @.CompYear, @.ExpDate, @.WhichSchool, @.SchoolType, @.Other, @.QualTitle1, @.QualInst1, @.QualComp1, @.QualTitle2, @.QualInst2, @.QualComp2, @.QualTitle3, @.QualInst3, @.QualComp3, @.QualTitle4, @.QualInst4, @.QualComp4, @.Notes, @.DateAdded , @.AddedByFK)"

<asp:ParameterName="DateAdded"Type="DateTime"/>

in this code, how do i retrieve the current date/time from SQL server while inserting a new record ?

i want to set the DateAdded field to default to the current date/time

Thanks,

Alex

|||I would personally use a stored procedure as then you can pass back the SQL datetime as the return value or an output parametersql

INSERT/UPDATE Date Format problem...

Hi,
This is a problem that everybody knows I guess: When you INSERT our UPDATE a
date in an Sql Server half of the time your date changes. For example: you
want to input two dates: 13th of May (13/05/2003) and 12th of May
(12/05/2003). The first one will always be in the database as "13/05/2003"
because the database knows 13 can't hbe a month. But for the second one you
need to get lucky: there's always a big chance (depending on the regional
settings?) that he will put it in the database as "05/12/2003" and thinks it
is 5th of December instead of 12th of May.
I used to have this problem in VB6, and now again I have it in VB.NET. In
VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
dd/MM/yyyy.
But still I think this isn't a 'nice' way. There should be a way which is
independed of regional settigns etc, and doens't force you to use 'trics'.
Does anybody here know how to do this?
Thanks a lot in advance!
PieterAny of the following formats are "safe" - they work independently of the
server's regional settings
'20031231'
'2003-12-31T17:59:00'
'2003-12-31T17:59:00.000'
Example:
UPDATE Sometable
SET datecol = '20031231'
WHERE ...
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks! I will try this!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Ws-dnW0JbYJ2fUiiRVn-jg@.giganews.com...
> Any of the following formats are "safe" - they work independently of the
> server's regional settings
> '20031231'
> '2003-12-31T17:59:00'
> '2003-12-31T17:59:00.000'
> Example:
> UPDATE Sometable
> SET datecol = '20031231'
> WHERE ...
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||<%
'This function recieves a date from text string in format dd/mm/yy or
dd/mm/ccyy
'And creates a string that is compatible with inserting into sql as a
datetime field
'If an empty string is passed it just passes back trimmed original
'Write Value Test value to sql database 'datetime' field
'Added 16/04/2003
'If a 2 digit year is passed then 20 is prepended onto year to build a
CCYY year
'--
'sValues = " NULLIF('" & convdate(sDate) & "','')"
'--
'pass a date as dd/mm/yy
Function convDate(theDate)
Dim Itemp
If TRIM(theDate) <> "" Then
sTemp = cdate(theDate)
dteArray = Split(sTemp,"/",-1,1)
If LenB(dteArray(2)) = 2 Then
dteArray(2) = "20" & dteArray(2)
End If
convDate =dteArray(2) & "/" & dteArray(1) & "/" & dteArray(0)
Else
convDate = Trim(theDate)
End If
End Function
%>
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:3fd5df92$0$289$ba620e4c@.reader5.news.skynet.be...
> Hi,
> This is a problem that everybody knows I guess: When you INSERT our UPDATE
a
> date in an Sql Server half of the time your date changes. For example: you
> want to input two dates: 13th of May (13/05/2003) and 12th of May
> (12/05/2003). The first one will always be in the database as "13/05/2003"
> because the database knows 13 can't hbe a month. But for the second one
you
> need to get lucky: there's always a big chance (depending on the regional
> settings?) that he will put it in the database as "05/12/2003" and thinks
it
> is 5th of December instead of 12th of May.
> I used to have this problem in VB6, and now again I have it in VB.NET. In
> VB6 I found solutions like inserting the date as MM/dd/yyyy instead of
> dd/MM/yyyy.
> But still I think this isn't a 'nice' way. There should be a way which is
> independed of regional settigns etc, and doens't force you to use 'trics'.
> Does anybody here know how to do this?
> Thanks a lot in advance!
> Pieter
>

insert values into both table at the same time using sql server 2005

hi all,

In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.

table 1:

tid--> int,identity,primary key

tname-->varchar(200)

table 2:

sid-->int,identity,primary key

tid-->fk (this tid is set as foreign key for the tid in table1)

now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..

pls help me..

thanks

swapna

Go For Stored Procedure for Insert...

SP Flow should be-

1. Start SQL Transaction

2. Insert into Table1

3. Get the Inserted INDENTITY value.

4.Insert into table 2

5. Commit Transaction Or Rollback transaction depending on the Error .

|||

You can create a stored procedure. There use will insert the record in the table 1 first and fetch the latest generated id value in table 1 using scope_identity and store it in a variable. Then you will insert the corresponding record in table 2 using the variable value.

For help on how to call a stored procedure from code visit http://forums.asp.net/t/1165758.aspx.

Feel free to ask for more help on this issue.

|||

Or you can create a trigger to insert the row into the second table.

It if must always happen the same way, the trigger would be a safer bet.

check out create trigger in the documentation.

sql

Insert two rows into two tables at the same time from a formview

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.

If you're passing all of the information into your Stored Procedure, then can't you simply retrieve the last ID inserted via SCOPE_IDENTITY? This assumes your using an identity column within tblPerson.

|||

Thanks for your reply. I'm not familiar with this command because I'm from a MySQL background. So I essentially I use the following

INSERT INTO tblPerson (name, username, password) VALUES (@.name, @.username, @.password);

INSERT INTO tblAddress (FK_tblPerson, address1, address2) VALUES (scope_identity(),@.address1, @.address2);

|||

Yes, except that I'd declare a variable and place the results of SCOPE_IDENTITY into it. Then I'd use that variable for my next INSERT.

Insert Triggers

I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?Within the trigger, you can access a view called INSERTED that shows only the rows that are being inserted by the statement that launched the trigger. You can use the INSERTED view (probably via a JOIN) to limit the number of rows you are affecting in your underlying table.

-PatP|||my telepathic usb port is clogged...can you post the trigger...

probably take us a few minutes...

DDL would be nice as well

and pat's correct(what again? say it ain't so...)|||CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select tblGroup.Id as DataRecordId,
1 as TaskNum,
"Ready" as Status,
tblUsers.Id as UserId,
getdate() as StartDateTime
from tblGroup, tblUsers, tblVendors where (tblGroup.I_Field3=tblVendors.OdissVendorId)
And (tblGroup.I_Field6 Is Null OR tblGroup.I_Field6='0')
And (tblUsers.WFID=1)

..a little complex. the check for tblGroup.I_Field6 is necessitated because all records are being checked - this where clause could be stripped off if only new records were being checked.|||Something like this would do it:

CREATE TRIGGER CheckWorkflow ON [dbo].[tblGroup]
FOR INSERT
AS
if exists (select 1 from inserted)
insert into WFTasks (DataRecordId, TaskNum, Status, UserId, StartDateTime)
select i.Id, 1, 'Ready', u.Id, getdate()
from inserted i
inner join tblVendors v
on i.I_Field3=v.OdissVendorId
inner join tblUsers u
on (u.WFID=1)|||thanx..will try this.

Insert Trigger - How To

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

Maybe you can try CLR integration in SQL2005 Create a stored procedure from an assembly?which?generate?the?password,?and?then?call?the?stored?procedure?in?the?insert?trigger?on?the?table.?Take?a?look?at?this?article:Using CLR Integration in SQL Server 2005

But if you just want some random string, why not try?T-SQL new_id() funciton?sql

Insert Trigger

I would like to have the value of a field to be set the return value of

System.Web.Security.Membership.GeneratePassword(12,4)

every time a a row is inserted.

Can you guide with this?

Do you have some similar sample code?

Thank you very much

You can create a dll which contains a method to call System.Web.Security.Membership.GeneratePassword(12,4) and return the generated password. Then add the dll to SQL assemblies and create a CLR reference function for that assembly.?For?more?information?you?can?refer?to:
Using CLR Integration in SQL Server 2005

Insert TIME only in DateTime field

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?That is how datetime fields work. You need to address this using formatting on the client side. DateTime has a number of useful ToString() overloads.|||Thanks for your help. It is too bad Access doesn't function in datatypes more similarly to SQL, especially since they are products of the same company. This migration would go alot easier. I am migrating some VBScript applications from Access to SQL, then from VBScript to VB.Net. It has been quite fun so far. Hehehe...

Insert Time into SQL server

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)

Values with thedatetimedata type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after thebase date, January 1, 1900. The base date is the system reference date. Values fordatetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

So in your case the first 4 bytes are not used. By default the value of Jan 1 1900 is displayed.

I do suggest that you continue to use the datetime value to store your time for filtering and sorting purposes but when you display the data to the end user you simply format the data to a time format.

Hope this helps!

|||

Hi,

you can convert your datetime with the CONVERT command, e.g. (in Query Analyzer):

Declare @.input As DateTime
Set @.input = Getdate()

Select Convert(nvarchar,@.input,108)

Regards
Marc André

|||

Hi,

thanks for your reply.

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les |||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les
Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les
|||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les :)Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les :)|||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les :)

Insert system time in database

Dear Friends,

I want to insert in my database, in a table field the system time value on that moment.

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

I want to save in my database th system time...

Thanks!!

UPDATE TABLE1 SET MyFieldTime=getdate() WHERE MyFieldID=@.ID|||

it always good practice to use the UTC time instead System's local time. In future if you transfer the data from one server (time zone) to another you need not to applay any changes..

Another benifit on UI you can convert to any Local time from UTC with out any overhead...

use the following query..

Update Table1 Set MyFiedlTime = GetUTCDate() Where MyFieldId = @.ID

|||

You should do this using a DEFAULT on the column and then use DEFAULT keyword in the SET clause of whatever UPDATE statement that modifies the data. Using a separate SP is not really a good idea since you will decouple the actual update and the time when it was done. It is also costly to perform multiple updates on the same row when you can do it once.

You can make below changes:

alter table TABLE1 add default( CURRENT_TIMESTAMP ) for MyFieldTime

After that when you do the actual UPDATE then do:

UPDATE TABLE1

SET col1 = ...

, col2 = ...

, MyFieldTime = DEFAULT

WHERE ...

INSERT Statement

I was wondering if any SQL people out there know if i can INSERT INTO 2 tables at the same time? I havent found a yes to the question so i thought i would throw it out there and see what everyone has to say.

word

I would say no. (Insert syntax ishere.) However, if you want to insert twice with one database call, simply separate your SQL statements with semicolons, i.e.

Insert [statement 1]; Insert [statement 2]; Insert [statement 3];

|||Asps2goatmentioned, they'd have to be 2 separate transactions. You can put the 2 INSERTs in a stored proc and call the proc from your application, so you are not making multiple calls from your front end.|||Thanks for the help guys, it solved the situation i was in. Much appreciation and hopefully.....oneday......i myself will be a sql guru haha

Insert SQL table into AS400 Alias

I have a SQL 2000 table that I want to insert into an AS400 alias. The 400 is a linked server to SQL. I can insert 1 record at a time with Insert OpenQuery.

insert openquery(AS400bck, 'select * from netfil.sp')
VALUES(1,6,2493,1,30,180,1,-8.00,0,-100.00,0,0,30,180,180,'',2,'X','','')

One of the tables is quite large so I don't want to have to use the values clause.

How can I insert the entire table at once?

Does this work?

I only have read only access to my AS400 environment...

insert openquery(AS400bck, 'select * from netfil.sp')
select [columns] from table where xxx=yyy
|||The statement did work correctly. Thanks for your help.

Insert small time into SQL Server 2000 table

Im having a lot of trouble inserting a small time value into a table cell. I gave the cell column the data type 'DateTime', i found i couldnt manually insert a time only value such as '12:30 PM' into a column with 'SmallDateTime'. Something about a "SmallDateTime Overflow Error". However if i enter a similar time value into a table column with the data type 'DateTime' it will happily accept it and leave it as entered.

The real problem seems to be when i try to send a time value to that column with my ASP.NET application. Because it inserts the time value and todays date. So that if i send:

12:30 PM

It will be stored as:

15/11/2003 12:30:00 PM

I only want to store the short time, not the date especially not the date that row was created on because thats useless for the purposes of what my application is trying to achieve and just creates problems down the track when selecting rows.

How can i correct this?Both DateTime and SmallDateTime always have date and time components. Both store the value as a decimal number with the whole number part being the date and the fractional part the time. If you only want the time component set the Date part to 1/1/1900.

When I ran '12:30PM' using datetime and smalldatetime it created the value as '1/1/1900 12:30 PM'. Not sure why you're getting today's date or why it complains entering a smalldatetime with '12:30 PM'

Select Top 1
Cast('12:30 PM' as datetime) as DateTime1230,
Cast('12:30 PM' as smalldatetime) as SmallDateTime1230,
Cast(Cast( '12:30 PM' as datetime) as integer) as IntOfDateTime1230,
Cast(Cast( '12:30 PM' as smalldatetime) as integer) as IntOfSmallDateTime1230,
Cast(Cast( '12:30 PM' as datetime) as float) as FracOfDateTime1230,
Cast(Cast( '12:30 PM' as smalldatetime) as float) as FracOfSmallDateTime1230
From SomeTable

returns

DateTime1230SmallDateTime1230IntOfDateTime1230IntOfSmallDateTime1230FracOfDateTime1230FracOfSmallDateTime1230
1900-01-01 12:30:00.0001900-01-01 12:30:00110.520833333333333370.52083333333333337

|||Ok no worries thats a good explanation. Thanks very much.

Insert Rows into four tables at one time

Hello,

I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_info

In 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,

Hi

I am not sure if you have created one stored procedure or four stored procedures (your post mentions "stored procedures")..

If you want to go the stored procedure way, It is ideal to create one stored procedure on which you can use a SQL level BEGIN TRAN and ROLLBACK. These are keywords written inside the SQL stored procedure.

Another way is to execute the queries or stored procedures usingSQLCommand's ExecuteNonQuery method, but before execution create aSQLTransaction object and associate that with the SQLconnection. If the transaction (Insert statements in SQL or SP) works fine, thencommit the transaction.

HTH

VJ

Insert query takes lot of time

Hello
I have these tables:
CREATE TABLE [dbo].[COREAttribute] (
[oid] [uniqueidentifier] NOT NULL ,
[CLSID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [COREAttributeOidIndex] ON
[dbo].[COREAttribute]([oid], [CLSID]) WITH FILLFACTOR = 90 ON
[PRIMARY]

CREATE TABLE [dbo].[COREBstrAttribute] (
[oid] [uniqueidentifier] NOT NULL ,
[iid] [uniqueidentifier] NOT NULL ,
[dispid] [int] NOT NULL ,
[value] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

CREATE CLUSTERED INDEX [COREBstrAttributeOidIndex] ON
[dbo].[COREBstrAttribute]([oid]) WITH FILLFACTOR = 90 ON [PRIMARY]

Now when I try this query, it's taking 8-10mins.

Declare @.t TABLE (oid uniqueidentifier primary key,
[Description] nvarchar(1024) NULL,
[Name] nvarchar(1024) NULL,
[UID] nvarchar(1024) NULL)

DECLARE @.COREBSTRAttribute TABLE (oid uniqueidentifier, dispid int
NULL, value nvarchar(1024) NULL)
INSERT INTO @.COREBSTRAttribute select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid ='{1449DB20-DB97-11D6-A551-00B0D021E10A}'
INSERT @.t
SELECT distinct
c0.oid,
c1.Value,
c2.Value,
c3.Value
FROM(
SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}')
) AS c0
LEFT JOIN @.COREBSTRAttribute AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN @.COREBSTRAttribute AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN @.COREBSTRAttribute AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

Any help is greatly appreciated.

thanks
SunitI admit I haven't read your post in detail, but clustering on a GUID
probably isn't a good idea. Since the GUID values are random, but the
index is ordered, that means a lot of fragmentation as the index has to
be constantly rebuilt and reordered. You might try changing to a
nonclustered index instead, and keep the clustered index for columns
which you often use in GROUP BY or ORDER BY clauses.

Simon|||I changed to this and now I get 'Invalid object name 'bstr' during
runtime. It parses successfully though !

Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From
(SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,

(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
) As bstr

LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

thanks
Sunit|||Simon Hayes (sql@.hayes.ch) writes:
> I admit I haven't read your post in detail, but clustering on a GUID
> probably isn't a good idea. Since the GUID values are random, but the
> index is ordered, that means a lot of fragmentation as the index has to
> be constantly rebuilt and reordered. You might try changing to a
> nonclustered index instead, and keep the clustered index for columns
> which you often use in GROUP BY or ORDER BY clauses.

As always in the database world, it depends!

SQL Server MVP Greg Linwood taught me a great way to use GUID for a
clustered index. You start off with a fairly low fill factor, say 50%.
Now, new guids will most cases find empty holes to jump in, so there
will not be any page split. Instead, fragmentation will decrease by
time.

Until, that is, pages starts to become full, and there will be page
splitting galore. That's when you need a maintenance window so yuu
can reindex with your fill factor. Obviously, you need a good feeling
for which fill factor you need to the next maintenance window. This is
definitely not a method for the DBA on auto-pilot.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||sjoshi (sjoshi@.ingr.com) writes:
> Now when I try this query, it's taking 8-10mins.

While we got the tables and the query, we did not get any details on
the amount of data involved. For instance, how many rows could we
expect in the involved tables?

Here are some assorted thoughts:

o Replace table variable with temp tables. Temp tables have statistics,
and this may the optimizer a second chance for a better job.
o Add a WHERE clause to insert only the dispid of interest into
@.COREBSTRAttribute
o Leave out Value from @.COREBSTRAttribute. That serves to make the
table slower.
o Add a clustered index on dispid. (Possible for temp table.)
o Long IN clauses are takes long time to optimize. Could be a better
idea to put the guids into a temp table instead.
o You insert into a table variable @.t. When you insert into temp
variables, you kill parallelism. Someties parallelism is good for
your queries. (Sometimes it is not!)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Insert query firing Insert & Update trigger at the same time.

Hello All,
I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp.

Insert and Update trigger work fine when i have only one of them defined.

However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and has the same timestamp in the audit table.

Insert trigger goes as
CREATE TRIGGER InsRecord ON [dbo].[tableA]
AFTER INSERT
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Insert',GetDate() from inserted

Update trigger goes as
CREATE TRIGGER UpdRecord ON [dbo].[tableA]
FOR UPDATE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Update',GetDate() from inserted

Delete Trigger goes as
CREATE TRIGGER delRecord ON [dbo].[tableA]
FOR DELETE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Delete',GetDate() from deleted

Note:This tableA has relations with 2 other tables on 1 field each from each table but i don't think it should matter.

Please advise how to prevent it.CREATE TRIGGER alteredRecord ON [dbo].[tableA]
FOR INSERT, UPDATE, DELETE
AS
BEGIN

...declare lngIns & lngDel

SELECT lngIns=count(col1)
from inserted

select lngDel=count(col1)
from deleted

IF lngIns>0 and lngDel=0
...inserted
else if lngIns>0 and lngDel>0
...updated
else if lngIns=0 and lngDel>0
...deleted
end

END