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 Data Ranges

Hello -

I'm trying to come up with a report that will separate a range of data into X slices. For example, the range might be from 0 through 100. I'd then want to slice it 10 ways, ending up with 10 groups. For each of those groups, I'd want a count of the number of records in my database that fall into each of those ranges. So, the number of records between 0 and 10 becomes the value of range 1. 11-20 becomes range 2, etc. After all that I'll be charting that data, but I think that'll be the start and then I can handle it from there.

Ideas?

Thanks,

Greg.You can do it one of two ways. Both ways require you to create a group - this can be done from the Insert/Group menu.

First way would be to use the "Specified Order" option in the order drop down list. this allows you to specify conditions that will group the data. This is fairly static in that you have to specify the conditions at design time, although there are ways to make this more dynamic using parameters - but that is a little more complex.

The second method is more flexible. You can create a group based on a Formula. Create a new Formula by right clicking on the Formula heading in the Field Explorer (assuming you are using V9 or above). You now have access to the complete formula language to define how you want to group your data - this can be very dynamic, as it can be conditioned by the data coming into the report itself. Once you have created your formula you can create a group on it.

Regards,
DS.

automated emails based on SQL Queries

Hello.
Does anyone know where there is an example of emails that are automatically
generated based on query results and have certain variables from the query
populate the email?
More Detail -
A query runs daily that identifies sales in certain locations. For each
location (row) an email is sent to the location manager with hard coded
verbiage and the sales figures pulled from the query for that specific
location.
I imagine this has been done somewhere, but I'm not sure where to start
looking.
Any suggestions are appreciated.
Thank you!
ChrisIf the query is done through a stored procedure, you can easily add SQL Mail
calls to issue emails.
Thomas
"chris" <chris@.discussions.microsoft.com> wrote in message
news:61C33544-A332-4DDB-90E1-11F2209CD396@.microsoft.com...
> Hello.
> Does anyone know where there is an example of emails that are automaticall
y
> generated based on query results and have certain variables from the query
> populate the email?
> More Detail -
> A query runs daily that identifies sales in certain locations. For each
> location (row) an email is sent to the location manager with hard coded
> verbiage and the sales figures pulled from the query for that specific
> location.
> I imagine this has been done somewhere, but I'm not sure where to start
> looking.
> Any suggestions are appreciated.
> Thank you!
> Chris
>|||Thank you! I will look into that.
"Thomas Coleman" wrote:

> If the query is done through a stored procedure, you can easily add SQL Ma
il
> calls to issue emails.
>
> Thomas
>
> "chris" <chris@.discussions.microsoft.com> wrote in message
> news:61C33544-A332-4DDB-90E1-11F2209CD396@.microsoft.com...
>
>|||You might want to take a look at microsofts new Notification Services. Comes
with SQL 2k5,
Download for sql 2k I believe.
http://www.microsoft.com/sql/ns/default.asp
"chris" wrote:

> Hello.
> Does anyone know where there is an example of emails that are automaticall
y
> generated based on query results and have certain variables from the query
> populate the email?
> More Detail -
> A query runs daily that identifies sales in certain locations. For each
> location (row) an email is sent to the location manager with hard coded
> verbiage and the sales figures pulled from the query for that specific
> location.
> I imagine this has been done somewhere, but I'm not sure where to start
> looking.
> Any suggestions are appreciated.
> Thank you!
> Chris
>

Automate partition creation

Hi,

Is there any example out there on how to automate the creation of time based partitions?
As an example, I would like to create 1 partition for each quarter and when a new quarter start have a new partition automatically added with the same attributes than the previous one.

I would also have the oldest 4 partitions automatically deleted as soon as the total number of partitions reaches 13.

Any thoughts or links?

Thanks,

Philippe

Hi Philippe,

The Project REAL Analysis Services Technical Drilldown discusses one implementation of such automation:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

>>

Project REAL: Analysis Services Technical Drilldown

By Dave Wickert, Microsoft Corporation

SQL Server Technical Article
Published: September 2005

Appendix A: Automating Partition Creation

The Project REAL design uses partitioning quite heavily. The production system has more than 220 extremely large partitions. The sample data uses over 125 partitions that are only tens of thousands of records per partition. The full production system has 180 to 200 million records per partition. With so many partitions, extensive typing was required to create each partition every time we generated a new schema.

So, as the saying goes, “When the going gets rough, a programmer writes a program.”

This appendix documents the BuildASPartition SQL Server 2005 Integration Services package that we created to automate the building of Analysis Services measure group partitions in SQL Server 2005 Analysis Services databases. This package synchronizes the relational partition scheme with the Analysis Services partition scheme. It loops through the relational database looking for a weekly fact table partition (by using a table naming convention). If a relational table is found, it looks to see if an Analysis Services measure group partition already exists (using the same naming convention). If not, it constructs and executes a XMLA script that creates it.

>>

|||

Philippe,

Add your comments here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=461211&SiteID=1

-Jamie

Auto Sizing the Page Header

Is there a way to auto-size the height of the page header depending on it's
contents?
For example, I have created a page header with a logo that appears on the
first page (1 inch x 1 1/2 inches) as well as my report title. But on
consecutive pages only the title shows and the image gets surpressed. I
would like to see the header contract in size on consecutive pages so that
only the report title fits at the top in an effort to conserve page real
estate.
Is this possible?
Thanks,
LisaNo. The page header height is set at design time and cannot be modified at
runtime.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:%23jr%23nYuWEHA.3476@.tk2msftngp13.phx.gbl...
> Is there a way to auto-size the height of the page header depending on
it's
> contents?
> For example, I have created a page header with a logo that appears on the
> first page (1 inch x 1 1/2 inches) as well as my report title. But on
> consecutive pages only the title shows and the image gets surpressed. I
> would like to see the header contract in size on consecutive pages so that
> only the report title fits at the top in an effort to conserve page real
> estate.
> Is this possible?
> Thanks,
> Lisa
>

auto number via a query

Hello,
I am wondering if somone could provide me with a sample SELECT for an
auto number query. For example I have a table called People with two columns
first_name, and last_name. There isn't a unique id to correspond with the
table but would like to dynamically make one during the return of the query.
So if there was 5 rows in the table it would return
1 John Alpha
2 John Beta
3 John Cat
4 John Delta
5 John Echo
Where the query was an order by last_name. Thanks in advance.
Jake"Jake Smythe" <someone@.microsoft.com> wrote in message
news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am wondering if somone could provide me with a sample SELECT for an
> auto number query. For example I have a table called People with two
> columns first_name, and last_name. There isn't a unique id to correspond
> with the table but would like to dynamically make one during the return of
> the query. So if there was 5 rows in the table it would return
> 1 John Alpha
> 2 John Beta
> 3 John Cat
> 4 John Delta
> 5 John Echo
> Where the query was an order by last_name. Thanks in advance.
Something like this will work, you'll need to add the first name to the
comparison also.
CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO People VALUES('John','Alpha')
INSERT INTO People VALUES('John','Beta')
INSERT INTO People VALUES('John','Cat')
INSERT INTO People VALUES('John','Delta')
INSERT INTO People VALUES('John','Echo')
SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
People.LastName) FROM People
ORDER BY LastName, FirstName
DROP TABLE People

> Jake
>|||While this kludge may work for the immediate need, you must be warned that t
here is no certainly that the order will be static. Each time the query exec
utes, the order may be different. And if will have problems with perfectly d
uplicate names. Try adding duplicate names and watch what happens...
SELECT
( SELECT sum(1)
FROM People p
WHERE ( p.LastName + p.FirstName ) <= ( p.LastName + p.FirstName )
) AS rownum
, p2.LastName
, p2.FirstName
FROM People p2
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Michael C" <nospam@.nospam.com> wrote in message news:%23j%23u8yZmGHA.4052@.TK2MSFTNGP05.phx
.gbl...
> "Jake Smythe" <someone@.microsoft.com> wrote in message
> news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
>
> Something like this will work, you'll need to add the first name to the
> comparison also.
>
> CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
> INSERT INTO People VALUES('John','Alpha')
> INSERT INTO People VALUES('John','Beta')
> INSERT INTO People VALUES('John','Cat')
> INSERT INTO People VALUES('John','Delta')
> INSERT INTO People VALUES('John','Echo')
> SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
> People.LastName) FROM People
> ORDER BY LastName, FirstName
> DROP TABLE People
>
>
>
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
While this kludge may work for the immediate need, you must be warned that
there is no certainly that the order will be static. Each time the query
executes, the order may be different. And if will have problems with
perfectly duplicate names. Try adding duplicate names and watch what
happens...
I was going to add a warning that this method wasn't perfect but I had to
race off so just hit send. Of course the value will change if the order
changes :-) As for duplicates you'd just need to use the fields that make up
the primary key, if rows are duplicated then maybe they should have the same
values anyway. Maybe performance of this method might be a problem?
Michael|||Guys thanks for the responses. It's fine if the order changes each time I am
just looking for a identifier at run time.
"Michael C" <nospam@.nospam.com> wrote in message
news:e0x5fZbmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
> While this kludge may work for the immediate need, you must be warned that
> there is no certainly that the order will be static. Each time the query
> executes, the order may be different. And if will have problems with
> perfectly duplicate names. Try adding duplicate names and watch what
> happens...
> I was going to add a warning that this method wasn't perfect but I had to
> race off so just hit send. Of course the value will change if the order
> changes :-) As for duplicates you'd just need to use the fields that make
> up the primary key, if rows are duplicated then maybe they should have the
> same values anyway. Maybe performance of this method might be a problem?
> Michael
>

Auto Number Sequence

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)

Whats the easiest way to do this?

--1.

Alter TABLE Incident

DROP COLUMN RecordId

--2.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 3000

--or 3.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 1

You can look up this infomation Alter Table and Alter Column from Books Online.

|||

hi,

you can use the identity function

make use use of its parameter seed=3000 to start from 3000

here's the syntaxt

identity(seed, increament)

here's your sample code

use northwind

select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employees

select * from #temp

regards,

joey

|||A potentially more expressive function you could use in place of identity is the row_number.

select row_number over( partiton by ... order by ...) + startin_number,

One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||

hi if any body can tell me the solutions of my prob.

i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc

|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||

You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....

-- create a simple test table
create table testcounter (thefld int)
go

-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go

-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0

-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go

-- list the results
select * from testcounter

Auto Number Sequence

How can I create a number sequence starting at a certain number and continue on for the number of records I have.

For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).

In my pseudo SQL code Im guessing it would be something like...

select * from Incident

update Incident
set RecordId( i=9000; i<=Number of Records in Table; i++)

Whats the easiest way to do this?

--1.

Alter TABLE Incident

DROP COLUMN RecordId

--2.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 3000

--or 3.

Alter TABLE Incident

ADD RecordId int IDENTITY(3000,1)--if you want start at 1

You can look up this infomation Alter Table and Alter Column from Books Online.

|||

hi,

you can use the identity function

make use use of its parameter seed=3000 to start from 3000

here's the syntaxt

identity(seed, increament)

here's your sample code

use northwind

select IDENTITY(int, 3000,1) AS ID_Num,
lastname,firstname into #temp from employees

select * from #temp

regards,

joey

|||A potentially more expressive function you could use in place of identity is the row_number.

select row_number over( partiton by ... order by ...) + startin_number,

One caveat of Row_Number implementation on SqlServer is that SS05 doesn't allow an empty order by, nor does it allow to sort on a constant.|||

hi if any body can tell me the solutions of my prob.

i have a table in Access in which prid is auto number , but the prob is that these prid is not in sequnce i.e, some number missing, like that after 8 its 12 , how will i arange it with delete data etc

|||If you have issues with this column only within this table, you can delete this column and recreate another Auto number field for your prid column in Access.|||

You can do this by combining a variable with an update statement.
Here's an example. If you run this whole set of code, the records in the table end up with values 1,2,3,4.....

-- create a simple test table
create table testcounter (thefld int)
go

-- add four rows, all with the same value
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
insert into testcounter values (1)
go

-- Declare and initialize an int variable
DECLARE @.thecount int
set @.thecount = 0

-- update the table using the variable.
update testcounter SET
@.thecount = @.thecount + 1,
thefld = @.thecount
from testcounter
go

-- list the results
select * from testcounter

Auto Number

May I have an auto number to reset to one if primary key change?
For example,
Key Field Auto number field
1 1
1 2
2 1
I want to auto number reset to one every time Key field change value.
Any information is great appreciated.
SourisPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Next, a column is not anything like a field. Your attempt at sample
data does not have a single column key as you tried to show -- it has a
two column key. Finally, autonumbering is a concept from a sequential
file system and has no place in an RDBMS. You might want to learn some
of the basics before you try to code in SQL.
Based on the specs you did not post, shoudl the DDL you did not post
look like this?
CREATE TABLE Foobar
(key_1 INTEGER NOT NULL,
key_2 INTEGER NOT NULL,
PRIMARY KEY(key_1, key_2));
Now you will want to add a constraint to keep sequential number in each
key_1 group. In T-SQL this will require a trigger, but in Standard
SQL, you could write:
CHECK
(NOT EXISTS
(SELECT *
FROM Foobar
GROUP BY key_1
HAVING MIN(key_2) - MAX(key_2) +1
<> COUNT(*)))|||On Sat, 29 Jan 2005 17:03:21 -0500, souris wrote:

>May I have an auto number to reset to one if primary key change?
>For example,
>Key Field Auto number field
> 1 1
> 1 2
> 2 1
>
>I want to auto number reset to one every time Key field change value.
>Any information is great appreciated.
>Souris
>
Hi Souris,
I assume that by auto number, you mean IDENTITY? The answer, then, is NO.
The IDENTITY property is intended to result in an incrementing value, to
be used as a meaningless surrogate key. The magic word here is
"meaningless" - are you aware that a series of identity values might
contain gaps? You are trying to put some meaning into the identity values;
you shouldn't.
You don't write why you want to do this. If you want to assign some kind
of ranking that restarts in a new group, the you should not store this
information, but compute it in the appropriate select statements.
See http://www.aspfaq.com/show.asp?id=2427; especially the part after the
last subheading ("Grouping within groups").
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you just want to generate this list of pairs, you can do it with a
improper table structure like this.
CREATE TABLE Foobar
(key_1 INTEGER NOT NULL);
INSERT INTO Foobar (key_1) VALUES (1);
INSERT INTO Foobar (key_1) VALUES (1);
INSERT INTO Foobar (key_1) VALUES (1);
etc.
I will do this in steps so you can see it easier. We need the count of
each key_1 group.
CREATE VIEW FoobarTallies (key_1, tally)
AS
SELECT key_1, COUNT(*)
FROM Foobar
GROUP BY key_1;
The Sequence table is a standard programming trick you can Google. Get
key_2 from the Sequence table
CREATE VIEW FoobarTwoKey (key_1, key_2)
AS
SELECT T1.key_1, S1.seq
FROM FoobarTallies AS T1, Sequence AS S1
WHERE S1.seq <= T1.tally;
Now put both views together:
CREATE VIEW FoobarTwoKey (key_1, key_2)
AS
SELECT F1.key_1, S1.seq
FROM (SELECT key_1, COUNT(*)
FROM Foobar
GROUP BY key_1)
AS F1 (key_1, tally)
CROSS JOIN
(SELECT seq FROM Sequence)
AS S1(seq)
WHERE F1.tally >= S1.seq;
of course this is not a table, not updatable and would seem to be
pretty useless in a valid data model. You can use it to insert into a
properly constructed table to get started, however.

auto increment in SServer 2005

I want to create a table under sqlserver 2005 with a primary key field as: auto increment concatenated with with the current year.

example:

For year 2007, the primary key field must be like:

02007

12007

22007
.
.
.
3652007
For year 2008, the primary key field must be like:
02008
12008
32008
.
.
.
3652008
etc

Can you help to do it.

Thanks.

HI!,

Sorry for the blank post, my suggestion is to do it from front end. Since you can't use auto number, If you are using stored procedure for inserting then you can keep the logic inside it also.

Hope this will help