Automatically create rows

Is there a way to automatically insert a row into a table when a row is
created in another table?
For example, suppose a row is added to the "Current Data" table. I would
like another table, "Historical Data", to be automatically updated with data
from from the row added to "Current Data". Is this possible? If so how?
Thanks in advance for any help!Read-up on triggers in SQL Server Books Online. Triggers can be written to
respond to various DML statements and can do operations like inserting into
other tables etc.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>|||Matt
Lookup CREATE TRIGGER ... ON Table FOR INSERT,UPDATE in the BOL
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:AF9C3D3C-518F-47DE-BF9B-9F4A0C544449@.microsoft.com...
> Is there a way to automatically insert a row into a table when a row is
> created in another table?
> For example, suppose a row is added to the "Current Data" table. I would
> like another table, "Historical Data", to be automatically updated with
> data
> from from the row added to "Current Data". Is this possible? If so how?
> Thanks in advance for any help!
>sql

Automatic Date Field

Hi, I am in the process of building a table and would
like one of the rows to contain a date time field that
is automatically populated with date/time once the
submit button is hit. I can do this in access with
selecting Data Type = Date/Time and default value
= Date()
SQL ver 2K sp3.
TIA for any pointers
JohnUse GETDATE() OR CURRENT_TIMESTAMP
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Johnny" <use@.stamp.co.uk> schrieb im Newsbeitrag
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
>|||"Johnny" <use@.stamp.co.uk> wrote in message
news:429c1d1f$1_1@.mk-nntp-2.news.uk.tiscali.com...
> Hi, I am in the process of building a table and would
> like one of the rows to contain a date time field that
> is automatically populated with date/time once the
> submit button is hit. I can do this in access with
> selecting Data Type = Date/Time and default value
> = Date()
> SQL ver 2K sp3.
> TIA for any pointers
> John
Thanks Jens, Unfortunatley that makes the whole column into the same date.
John

Automate Row Update and send email?

I'd like to update rows matching a certain criteria each night automatically and then send an email.

I have a table with a Status column and an Expire Date column. I want to update rows where the Status = Open and the Expire Date < The current Date by changing the Status to Closed then send an HTML email. Do I have to write a program and schedule it to run or is there a way to do this in MS SQL? I looked at using a stored procedure but I don't see how to schedule one to run (other than at startup) and I'm not sure if the GETDATE function can be called.

Thanks for any help.

Joewindows service or a scheduled task.

sql -> a scheduled job.

It's not some procedure you can create or a function. You have to set it up on the main service to execute.|||create a SQL Server Agent Job to run the stored proc on a schedule...|||I have the same situation.
I am using Access, how can I do this?
Create a .vbs and schedule it with scheduled tasks?
I tried but i get an error in the first Dim statement.|||Thanks for the help. I ended up just creating another asp.net page to take care of it. Then I just run it as a scheduled task and kill it. I haven't learned enough about stored procedure to do it the otherway yet.

Joe

Auto Primary Key with fill in

I want a table to have an int auto primary key, that will fill in deleted rows.
EG
1
2
4
5
8

The next primary key should be 3 (NOT 9)

This table changes often, so just adding 1 to the last number used, will fast run out of numbers.
The table is accessed by many users, so it can not be manually generated.

Using the IDENTITY property will successfully and correctly manage autonumbering. However, as you noted, deletions will leave gaps. I suggest that you use a datatype sufficient to allow both growth and allow for the deletions. A bigint datatype is a very, very large number -up to 9,223,372,036,854,775,807, I couldn't imagine that you are adding so much data to the database that a bigint would be inadequate.

It would be possible to create a 'homegrown' solution to manage this, but in my experience, with many users, that is a mistake. It would require a lot of table locking and/or data contention, placing unneeded stress on the database.

There are occassionally, real business needs for managing a numbering sequence, for example check numbers. But in those cases, deletions are usually not allowed.

|||Yes and after about 1 year or so when it flips over to 1 again, and starts over writing data.

Its a very nasty bug.

Its happened before.|||

Hi:

Please refer to the following article, and see if it would help with your concern. :-)

http://www.sqlteam.com/item.asp?ItemID=765

Thanks.

|||That changes existing data.

but can be expanded upon, to use a multi sql statement to defrag a database.

but then how to reset the auto number to the next number.

So far the only way, i can do what i want, is one of two ways

1. Run all sql through a service, that can sync me a new auto fill in number.
2. Have unacceptable down time, and run a program to defrag the tables.

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me...
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>