Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?
Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:

> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:

> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>
|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:

> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
>> This may seem like a stupid question but ...
>> say if I have a table called Gender with only two records
>> ID DESC
>> 1 MALE
>> 2 FEMALE
>> is it worth building an index by making ID the primary key?|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:
> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> > This may seem like a stupid question but ...
> > say if I have a table called Gender with only two records
> > ID DESC
> > 1 MALE
> > 2 FEMALE
> > is it worth building an index by making ID the primary key?
>
>|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:
> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> > This may seem like a stupid question but ...
> > say if I have a table called Gender with only two records
> > ID DESC
> > 1 MALE
> > 2 FEMALE
> > is it worth building an index by making ID the primary key?
>

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:

> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
>|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:

> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>|||Why not? Make the PK a clustered index and you don't waste space. It won't i
mprove perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||another thing too (contributing an answer to my own question here) I couldn'
t
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:

> Why not? Make the PK a clustered index and you don't waste space. It won't
improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>

adding a new record takes longer and longer archive? (was "Table help")

Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be greatThat's not really a lot of records

And 4,000 a day is not a lot of transactions

How do you "add" the rows?

And what the Table DDL look like...post that, and don't forget the indexes, keys, contraints and triggers if you have them|||Here's the info on the table that's causing the probs. We are updateing this table using an Access front end with odbc linked tables. Any help on how to maintain a table would be great.
Cheers

CREATE TABLE [dbo].[calls] (
[CallID] [int] IDENTITY (1, 1) NOT NULL ,
[ContactID] [int] NOT NULL ,
[CallDate] [datetime] NULL ,
[CallTime] [datetime] NULL ,
[Callername] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[CallerCompany] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ActionTakenID] [int] NULL ,
[Confirmed] [bit] NULL ,
[EmployeesID] [int] NULL ,
[ShortMessage] [varchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[CallerNumber] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Charged] [bit] NULL ,
[OperatorID] [int] NULL ,
[DDI] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[EmployeeName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OperatorName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[calls] WITH NOCHECK ADD
CONSTRAINT [PK_calls] PRIMARY KEY CLUSTERED
(
[CallID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[calls] ADD
CONSTRAINT [DF_calls_Confirmed] DEFAULT (0) FOR [Confirmed],
CONSTRAINT [DF_calls_Charged] DEFAULT (0) FOR [Charged]
GO|||My guess is that you're using a bound form to this table. And this is an MDB not an Access Data Project.

I would make it unbound, use sql to populate the unbound controls, then when they need to do an update or insert, perform them as regular sql calls using the values from the controls.

As for the table...there's not really much wrong.

I would do the following though

1. Combine the CallDate and CallTime Fields. It's redundant.
2. Whats the largest value in the ntext column. I'd use varchar if possible
3. A caller name of 255? This has got to be an Access default. Make it shorter. Better yet, make 2 columns. Last Name and First Name. Add a Third for Title, Mr/Mrs/Ms, ect
4. Bit...I never liked bit...I don't think it's ANSI and does not cross platforms well
5. varchar(4)...anything less than ...say 15 should be char...
6. Employee Name, Operator Name...same thing...you should use first and last
7. And identity as a PK? There's got to be a better one. CallerLastName, CallDate, CallerFirstName perhaps. Just plave a unique index un the id.

Now None of these things should be impacting your performance...maybe ntext (and why unicode, why not just text?). ALL of my money is on the Access ODBC nound form.|||As you guesed the sql database is an bit of a bodged upgrade from a access database, so needs cleaning up, am waiting for my boss to send me on some sort of course though.
As you also guesed we are using access bound forms, think I might have to bite the bullet a have been considering upgrading to a vb.net front end for a while, but don't think my programing skills are really up to it, is much more work than going to an access project, but may as well go the full hog and dump access.
Have just dome some testing and it updates like lightning from vb.net.

Any more general advice would be great (if people have nothing to says will mark of a resolved in a few days)

Thanks again

adding a new key; planning for millions of records

Hello all,
i'm going to be adding a new column (1 byte) that will become a non-unique
indexed value into a table that i'm adding records into. i know for a fact
that this table will grow from the current thousands to millions of records
over the span of a few ws and have allocated the database file
accordingly.
since i already have the primary key as clustered is there something i can
do so that adding this key as part of insert operations will be done
efficiently? I'm adding tens of thousands of records a day. In a previous
incarnation of this database i had had problems with having to rebuild the
keys occasionally or insert operations would timeout. I know that part of
the problem was fragmentation of the database, but i'd like to give it a
'heads up' to prepare for a large B-tree for this key.
is there a knob that will control this when i create the index?
thanks,
johnJohn,
I'm assuming you're refering to index fragmentation for the 1 byte index.
Might try creating the index with a lower FILLFACTOR to help minimize
fragmentation.
HTH
Jerry
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:usOEf6$0FHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> i'm going to be adding a new column (1 byte) that will become a non-unique
> indexed value into a table that i'm adding records into. i know for a fact
> that this table will grow from the current thousands to millions of
> records
> over the span of a few ws and have allocated the database file
> accordingly.
> since i already have the primary key as clustered is there something i can
> do so that adding this key as part of insert operations will be done
> efficiently? I'm adding tens of thousands of records a day. In a previous
> incarnation of this database i had had problems with having to rebuild the
> keys occasionally or insert operations would timeout. I know that part of
> the problem was fragmentation of the database, but i'd like to give it a
> 'heads up' to prepare for a large B-tree for this key.
> is there a knob that will control this when i create the index?
> thanks,
> john
>sql

adding a new column

Dear all,
What would be the fastest way to add a new column (a timestamp column
to be exact) to a table with about 15M records?
We actually tried doing that earlier, but it was already running for 6
hours and the script has not yet finished executing. We had to kill
the transaction as it was already severely prolonging the downtime.
The rollback process, of course, incurred almost 4.5 hours in itself.
:(
Since we will be doing this again (as we need the timestamp table),
any tips would be appreciated.
Thanks.
AramidHow did you do it the first time? I assume you used Enterprise Manager
which is a real no-no when it comes to modifying tables. The alter table
command from Query analyzer would have been much better. It's hard to say
how long it should take without knowing a lot more of your hardware and
configurations. Another option is to BCP out all the data from that table.
Truncate it. Set the recovery mode to Simple. Alter the table to add the
column. Then BCP it back in. You may need a format file to BCP back in
since the structure will change. In any case make sure you do a full backup
first and set the recovery mode back to what it was if changed.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:vod5m11210l0l0v8568nik7nvlfsjf9gno@.4ax.com...
> Dear all,
> What would be the fastest way to add a new column (a timestamp column
> to be exact) to a table with about 15M records?
> We actually tried doing that earlier, but it was already running for 6
> hours and the script has not yet finished executing. We had to kill
> the transaction as it was already severely prolonging the downtime.
> The rollback process, of course, incurred almost 4.5 hours in itself.
> :(
> Since we will be doing this again (as we need the timestamp table),
> any tips would be appreciated.
> Thanks.
> Aramid|||On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>How did you do it the first time? I assume you used Enterprise Manager
>which is a real no-no when it comes to modifying tables. The alter table
>command from Query analyzer would have been much better. It's hard to say
>how long it should take without knowing a lot more of your hardware and
>configurations. Another option is to BCP out all the data from that table.
>Truncate it. Set the recovery mode to Simple. Alter the table to add the
>column. Then BCP it back in. You may need a format file to BCP back in
>since the structure will change. In any case make sure you do a full backup
>first and set the recovery mode back to what it was if changed.
Hi Andrew,
I actually used the Alter Table command via QA. The database is
running on a virtual instance of a SQL cluster. The machine is a
dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
drives. Based on perfmon output, the bottleneck is disk IO, and in
fact, the CPU stayed almost less than 10% the whole time.
Actually, we executed the same script on a recently restored copy of
the same database on the other node/virtual instance of the
active/active cluster (so the hardware would be the same). The script
only took about 30 minutes to finish. I am thinking that the large
difference in run time may be caused by physical fragmentation on the
production copy of the database. Will this be possible given the
large time difference?
I will do tests using your recommended BCP approach and see how it
performs on my machine.
Thanks.
Aramid|||Best is backup the DB , empty the table and then use alter script to
change the column name and load it back.
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||A 3 disk Raid 5 is the worst Raid you can have when it comes to Writes.
This is especially true f the log file is also on the Raid 5 along with the
data files. Adding a raid 1 for the log files will dramatically speed up
this process. Is the other node using the same Raid array with a different
logical drive or does it have it's own Raid 5? If they are really two
different ones then you may have a bad disk which will dramatically decrease
both read and write performance. Fragmentation can account for some of the
issues but I wouldn't expect it to be that much.
--
Andrew J. Kelly SQL MVP
"Aramid" <aramid@.hotmail.com> wrote in message
news:4si5m114t13uq7tauetj881ol4vamkoo7b@.4ax.com...
> On Fri, 28 Oct 2005 20:58:36 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>How did you do it the first time? I assume you used Enterprise Manager
>>which is a real no-no when it comes to modifying tables. The alter table
>>command from Query analyzer would have been much better. It's hard to say
>>how long it should take without knowing a lot more of your hardware and
>>configurations. Another option is to BCP out all the data from that
>>table.
>>Truncate it. Set the recovery mode to Simple. Alter the table to add the
>>column. Then BCP it back in. You may need a format file to BCP back in
>>since the structure will change. In any case make sure you do a full
>>backup
>>first and set the recovery mode back to what it was if changed.
> Hi Andrew,
> I actually used the Alter Table command via QA. The database is
> running on a virtual instance of a SQL cluster. The machine is a
> dual-Xeon 2.8Ghz, and the shared array is a RAID-5 of 3 x 76Gb U320
> drives. Based on perfmon output, the bottleneck is disk IO, and in
> fact, the CPU stayed almost less than 10% the whole time.
> Actually, we executed the same script on a recently restored copy of
> the same database on the other node/virtual instance of the
> active/active cluster (so the hardware would be the same). The script
> only took about 30 minutes to finish. I am thinking that the large
> difference in run time may be caused by physical fragmentation on the
> production copy of the database. Will this be possible given the
> large time difference?
> I will do tests using your recommended BCP approach and see how it
> performs on my machine.
> Thanks.
> Aramid

Adding a BIT column - 100+ hours!

I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.

Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?

Any ideas?ALTER TABLE ADD...NOT NULL DEFAULT... is a fully logged operation. The logs that contain internal page updates are small but numerous (and I use this word loosely). There is nothing you can do at this point because as you mentioned correctly, - the rollback will take even longer. And as I stated in one of my replies to you previous similar post, - if you kill the service the database recovery will take forever (weeks possibly). There is however a rather drastic approach that may (!!!) yield a quick return your database to pre-ALTER state...I would not recommend you trying it simply because it may or may not succeed, and if it doesn't, - someone is gonna be fired...|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......
I'd hope so...Did they?|||I still don't know why you made a column NOT Nullable for 400 million rows...

And the last thread was filled with a lot of good advice...

why didn't you heed any of it?

From a design perspective, the column should be non-nullable. I'm sure we could deal with it as a NULLable column if absolutely necessary. That's a sacrifice I'd easily be willing to make but at this point it's too late.

As I said, I didn't do this and it's frustrating from my perspective. We really didn't need the column on this table in the first place and it was a without question a bad move. If he started both column add operations at the same time before he realized what a drastic operation this would be then I can understand his perspective. Believe me, I am using the info I learn here, and trying to convey what I learn to my team.

Thanks you both for the feedback! I will not cancel, let it run, just cross my fingers that it finishes in the next week, and I will try to convince my boss to give me yet another database server so that we can start over (not fun) as a parallel strategy. Thankfully, this was done on a secondary system where we are trying to reconstruct our primary database with a slightly improved process (bug fixes and better data gathering).|||Why would someone get fired? I mean, of course they made a backup of the database before running such a drastic operation. Right?......

LOL! No, no backups.

For some reason I feel way more stressed about this than either my boss or the worker who is assisting on this project.|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?|||Then definitely I'm not telling anything about "the other" way...Does you DBA have any say in all this?

"The other" way? :) Mysterious...

I just emailed our part time DBA but I'm not counting on much. Unfortunately, we are mostly programmers.|||Threads like this just make me speechless.

It's hard to talk when you're rolling on the floor laughing.sql

adding 2 records

Hello,
I've been converting/normalizing data and inserting it to other tables.
What i would like to do is to create 2 records for a particular value if
it occurs in the denormalized table.
Any idea how i should do this?I assume you mean something like:
CREATE TABLE DenormTable
(
DenormID int PRIMARY KEY
, MainVal int
, Year2005Value int
, Year2004Value int
)
If so, you merely have to create an insert like so:
CREATE TABLE NormTable1
(
Norm1ID int PRIMARY KEY
, MainVal int
)
CREATE TABLE NormTable2
(
Norm2ID int IDENTITY(1,1) PRIMARY KEY
, Norm1ID int NOT NULL
, Val int
, Year int
)
INSERT INTO NormTable1 (Norm1ID, MainVal)
SELECT DenormID, MainVal FROM DenormTable
INSERT INTO NormTable2 (Norm1ID, Val, Year)
SELECT DenormID, Year2005Val, 2005 FROM DenormTable
INSERT INTO NormTable2 (Norm1ID, Val, Year)
SELECT DenormID, Year2004Val, 2002 FROM DenormTable
You can combine the two in a single statement, if necessary, with some CASE
statements or creating a temporary VIEW with a UNION.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think Outside the Box!
****************************************
*******
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u$%23PCme6FHA.564@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been converting/normalizing data and inserting it to other tables.
> What i would like to do is to create 2 records for a particular value if
> it occurs in the denormalized table.
> Any idea how i should do this?|||In my Delphi app connecting to Sql Server using ODBC.. When 3 or more users
updating the same table at the same time (Not the same record) there is a
delay for approx 10 sec, I tried to use Enterprise manager from another
machine to update the same table and found the same problem so the problem
is not in my program code..
Finally after trying to solve this i noticed that 1 pc is the reason of the
delay, and after changing this Client IP.. it work now great and if i change
the IP back to it's old value the problem raise again!!.
Could i know what is the relation between Sql server or ODBC and client
machine IPs'
any information will appreciated
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u$%23PCme6FHA.564@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been converting/normalizing data and inserting it to other tables.
> What i would like to do is to create 2 records for a particular value if
> it occurs in the denormalized table.
> Any idea how i should do this?

adding 140,000 records requires index rebuild. make sense?

hello all,
i have a table thats getting about 140,000 new records a day from a program
reading data from a socket (about 300 every 3 minutes). in addition to the
primary key it has an 8 byte date/time field thats indexed. there are also
two full-text indexed columns with change tracking and update in background
enabled.
as we started to load the database we noticed after some hours 'insert'
operations began to fail with a timeout and queries began timing out.
When i rebuild the index based on date/time it cleared up and queries were
fast again. This cycle has been going on for a couple of ws. I haven't
had to do anything to the full-text portion, these problem orbit around the
date/time index.
Does this make sense? It really sounds like the indexes have exceeded some
internal resource and are being added to in an inefficient manner causing
the timeout.
is there a body of knowledge about defining indexes for tables that grow
like this that i should be aware of?
thanks,
john mottJohn,
I have not heard of any issues like this. Maybe a blocking issue?
That said...you might consider decreasing the fillfactor for the index (note
will require more space) to see if that helps. Also, you might consider
scheduling a rebuild of the index as a nightly job (off peak hours or after
hours).
HTH
Jerry
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of ws. I haven't
> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Is the table more empty than full? Perhaps the statistics are out of whack.
140,000 rows is a considerable number, but it is not a tremendous amount.
Then again, that statement is relative to your environment. If your table
only has 300,000 rows you are adding 50% to the table.
How large is your database? How much room for growth do you have? SQL
Server might be expanding (growing) the database size during the mass
insert. This could cause reduced performance which would lead to query
timeouts.
How are you adding the rows? Are you performing 140,000 INSERT INTO
statements? Can you look into using BCP/Bulk Insert/DTS to get the data in
to the database? You might find that this option is faster.
Keith
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of ws. I haven't
> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Hey John,
Is the date-time index a clustered index? If not, do you have one, and
if so, what datatype is the index associated with? Tables of this size
really ned a clustered index to help in the retrieval process; a
clustered index should be built on a monotonically increasing value
(like a datetime, assuming that the INSERT is inserting data in a
relatively sequential manner). A common mistake is to use a
uniqueidentifier for a primary key, and using that as a clustered
index, which will lead to fragmentation (because the data is being
inserted out of order).
Sorry; not feeling well today, so my answers may be less than cogent.
However, your first step is to determine if your clustered index is on
thewrong column.
HTH,
Stu|||There is a good body of knowledge on www.msdn.com
Here is what a few minutes of searching churned up:
Try to discover the root cause of why your queries are timing out:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Planning and Creating Indexes
http://www.microsoft.com/technet/pr...s/c0618260.mspx
DBCC SHOWCONTIG will reveal if index fragmentation is an issue and DBCC
INDEXDEFRAG can be periodically issued to help minimize it. Defragmenting is
faster than re-indexing, and based on your description, this application
sounds like it is running in an environment where you don't want the system
down for extended periods of time.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Rather than performing 300 inserts per minute, you can initally write the
records to a tab delimited text file and then bulk insert perhaps every 5
minutes
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...calability.mspx
"John Mott" <johnmott59@.hotmail.com> wrote in message
news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
> hello all,
> i have a table thats getting about 140,000 new records a day from a
> program
> reading data from a socket (about 300 every 3 minutes). in addition to the
> primary key it has an 8 byte date/time field thats indexed. there are also
> two full-text indexed columns with change tracking and update in
> background
> enabled.
> as we started to load the database we noticed after some hours 'insert'
> operations began to fail with a timeout and queries began timing out.
> When i rebuild the index based on date/time it cleared up and queries were
> fast again. This cycle has been going on for a couple of ws. I haven't
> had to do anything to the full-text portion, these problem orbit around
> the
> date/time index.
> Does this make sense? It really sounds like the indexes have exceeded some
> internal resource and are being added to in an inefficient manner causing
> the timeout.
> is there a body of knowledge about defining indexes for tables that grow
> like this that i should be aware of?
> thanks,
> john mott
>
>|||Thank you for your response.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eit6m%23QxFHA.3740@.tk2msftngp13.phx.gbl...
> Is the table more empty than full? Perhaps the statistics are out of
whack.
> 140,000 rows is a considerable number, but it is not a tremendous amount.
> Then again, that statement is relative to your environment. If your table
> only has 300,000 rows you are adding 50% to the table.
At this point there about 2,300,000 records.

> How large is your database? How much room for growth do you have? SQL
> Server might be expanding (growing) the database size during the mass
> insert. This could cause reduced performance which would lead to query
> timeouts.
Currently its about 23 Meg, but its fair to say that when i created it it
created with a default size with instructions to grow at 10% at a time.
Should i re-allocate this? would that lead to bad organization?

> How are you adding the rows? Are you performing 140,000 INSERT INTO
> statements? Can you look into using BCP/Bulk Insert/DTS to get the data
in
> to the database? You might find that this option is faster.
i am indeed doing a set of inserts, but the speed of insertion is
satisfactory. I'd heard about BCP before; would that have a different impact
on how the indexes were managed?

> --
> Keith
>|||Thank you for responding.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1128010124.506187.10780@.g49g2000cwa.googlegroups.com...
> Hey John,
> Is the date-time index a clustered index? If not, do you have one, and
> if so, what datatype is the index associated with? Tables of this size
> really ned a clustered index to help in the retrieval process; a
> clustered index should be built on a monotonically increasing value
> (like a datetime, assuming that the INSERT is inserting data in a
> relatively sequential manner). A common mistake is to use a
> uniqueidentifier for a primary key, and using that as a clustered
> index, which will lead to fragmentation (because the data is being
> inserted out of order).
The date/time is not clustered, and i did exactly what you indicated was a
common mistake, created a generic primary key that is clustered. Since the
date/time is really how the data is retrieved (always searching last 'n'
units of time) it should be 'appending' the data as it add it. Of course, in
this case newer data goes 'at the end' so its not dissimilar to having it be
that way in a large sense; there is a correlation between the order as
defined by the normal primary key and the order defined by the date/time. I
can see, however, that i have essentially a wasted key since i really only
care about the date/time, i've used two keys where one would have done.|||Thank you for responding. These look like the kind of meaty stuff i should
have read before hand :-) I've always been able to treat the SQL Server as a
black box but this is the largest database app i've created and i'm flying
solo on the tuning and configuration front...
john
"JT" <someone@.microsoft.com> wrote in message
news:u7y2ImRxFHA.1028@.TK2MSFTNGP12.phx.gbl...
> There is a good body of knowledge on www.msdn.com
> Here is what a few minutes of searching churned up:
> Try to discover the root cause of why your queries are timing out:
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/defaul...kb;EN-US;224453
> Planning and Creating Indexes
> http://www.microsoft.com/technet/pr...s/c0618260.mspx
> DBCC SHOWCONTIG will reveal if index fragmentation is an issue and DBCC
> INDEXDEFRAG can be periodically issued to help minimize it. Defragmenting
is
> faster than re-indexing, and based on your description, this application
> sounds like it is running in an environment where you don't want the
system
> down for extended periods of time.
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> Rather than performing 300 inserts per minute, you can initally write the
> records to a tab delimited text file and then bulk insert perhaps every 5
> minutes
> SQL Server 2000 Incremental Bulk Load Case Study
>
http://www.microsoft.com/technet/pr...ncbulkload.mspxed">
> With 140k inserts per day, I don't know what the total row count is, but
> consider vertical paritioning of rows into multiple tables and perhaps
> partitioned views.
> Scalability and Very Large Database (VLDB) Resources
> http://www.microsoft.com/sql/techin...calability.mspx
>
> "John Mott" <johnmott59@.hotmail.com> wrote in message
> news:OzWjq1QxFHA.2652@.TK2MSFTNGP14.phx.gbl...
the
also
were
haven't
some
causing
>|||Sorry John, let me try to clarify (hang on, it's gonna be rough):
First, a key is not an index; a key is used to identify and enforce
relational validity between tables. The primary key uniquely
identifies a row, and can be used as a foreign key to enforce a
relationship with another table. When you create a primary key on a
table, SQL Server will create an index associated with that key; by
default, that index is clustered (if there is not already a clustered
index on the table).
So what's an index? An index is simply a collection of pointers to the
location of a row within a table. Indexes are used to improve
retreival time, because the SQL Server optimizer should use the index
to quickly identify rows that meet the query requirements. Indexes may
be clustered or nonclustered; only one clustered index can exist on a
table. The cluster status refers to the physical ordering of data
within a page; a simple way of understanding it is that a clustered
index sorts the data by the indexed value and physically remembers that
sort (not really, but the analogy is close).
On a large table with a lot of inserts, you should always use a
clustered index on a monotonically increasing value (like an IDENTITY
integer or a date/time value). This will greatly reduce performance
problems because as data comes in, it always gets appended to the end
of the table, rather than having to be inserted somewhere in the
middle. For example, if you have a clustered index on a LastName
column, and you already have some data like so:
LastName
Ainsworth
Smith
Thomas
West
and you want to insert a row with the name Bice in it, SQL Server has
to split the page holding the data to insert that one record.
Continued page splits can lead to fragmentation. If your clustered
index is on a increasing value (like a datetime variable), fewer page
splits occur.
So, using your scenario, I would create a PRIMARY KEY constraint on the
unique row identifier, and created a clustered index on the datetime
column. Just to be clear, you can have as many nonclustered indexes on
a table as you need, but you can only have one clustered index.
Hope that clarifies.
Stu

Add values to a table based on values in another table

When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated.

Thanks

You could do it in a trigger. They're really not hard. It's just SQL you want to run whenever a certain action happens on your table. Pretty straightforward. Another option is to use stored procedures to do all of your inserts and updates. Then, just code what could go in the trigger directly into your stored procedure.

Pete

Add times

SQL Statement.

Adding times together.

I am trying to add a list of times together retrieved from my database.

They are from seperate records in the same table.

They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?

Below is the SQL statement of getting times from my database if its any help.

SQL Statement

SELECT LENGTH_OF_VISIT FROM NOV.MAIN;

Returned Results

30-Apr-2007 02:00:00 AM

30-Apr-2007 01:00:00 AM

01-Jan-2006 02:30:00 PM

01-Jan-2006 03:10:00 PM

30-Apr-2007 01:00:00 AM

Hi Ric

This forum is specific to SQL Server Data Mining. You'll probably get a better answer by posting to the T-SQL forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

You can extract the parts of your datetime field using the DATEPART function as:

SELECT DATEPART(LENGTH_OF_VISIT, hh) as Hour, DATEPART(LENGTH_OF_VISIT, mm) as AS Minute from Nov.Main.

Then you can wrap an aggregate ADD function to add the values from different records.

SELECT ADD(DATEPART(LENGTH_OF_VISIT, hh) * 60 + DATEPART(LENGTH_OF_VISIT, mm)) from Nov.Main

Please look at the documentation of DATEPART for more usage examples

Hope this helps

Add the end date to record

I have records with a start date. I would like to add a column that is the
end date, or the next record's start date.
So if I had
Store, startdate, retail
1, 10/15/2005, 2.50
1, 10/30/2005, 3.00
1, 11/19/2006, 2.00
The new records would be:
Store, startdate, retail, end date
1, 10/15/2005, 2.50, 10/30/2005
1, 10/30/2005, 3.00, 11/19/2005
1, 11/19/2006, 2.00,
Thanks for any suggestions.
Alter Table MyTable
Add EndDate Datetime
Go
I haven't a clue what your table is called let anyone any Primary Keys
etc but the update statement would look something similar to :
Update MyTable
Set EndDate = '2005-10/-0'
Where Store = 1
And StartDate = '2005-10-15'
HTH
Barry|||I understand your query, but my table have 2 million rows with many differen
t
stores and start dates.
So I need to add the startdate value from the next (in order of startdate)
record that matches on store and add that to my end date column.
Thank you for your assistance.
"Barry" wrote:

>
> Alter Table MyTable
> Add EndDate Datetime
> Go
> I haven't a clue what your table is called let anyone any Primary Keys
> etc but the update statement would look something similar to :
> Update MyTable
> Set EndDate = '2005-10/-0'
> Where Store = 1
> And StartDate = '2005-10-15'
> HTH
> Barry
>|||please try this:
update tbl1
set enddate=(select min(startdate) from tbl1 t where
t.startdate>tbl1.startdate and t.store=tbl1.store)
dean
"AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
news:666F7AD4-1868-476F-8D37-A1ECF3C8DF76@.microsoft.com...
>I understand your query, but my table have 2 million rows with many
>different
> stores and start dates.
> So I need to add the startdate value from the next (in order of startdate)
> record that matches on store and add that to my end date column.
> Thank you for your assistance.
> "Barry" wrote:
>|||Without any DDL or Sample Data its very difficult to try to help you.
Please see http://www.aspfaq.com/etiquette.asp?id=5006 for more info
and help
Thanks
Barry|||this answered it perfectly. thanks much!
"Dean" wrote:

> please try this:
> update tbl1
> set enddate=(select min(startdate) from tbl1 t where
> t.startdate>tbl1.startdate and t.store=tbl1.store)
> dean
> "AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
> news:666F7AD4-1868-476F-8D37-A1ECF3C8DF76@.microsoft.com...
>
>

Add rows to a DataSet without updating the MS SQL Server?

I am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way?

DataSet myDS =newDataSet();
DataTable myTable =newDataTable("table1");
myTable.Columns.Add("col1",typeof(string));
myDS.Tables.Add(myTable);
myTable.Rows.Add("MyValue");

Thanks.

Yes it is possible.

Add records in a table

Hi:
I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx."Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

Hi:
I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx.
>

A strange design. If the only key is random then how do you hope to retrieve
the information? If the rest of the data is to be identical then why bother
copying it?

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));

INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

--
David Portas|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

>I need to add some records in a table called location(primary key:
>loc_id). What I want to do is for each location in the table, I add
>the same record but with a different loc_id, which can be a random
>string. All the other column should contain the same value. Can anyone
>give me a hint on how to do this in SQL server 2000 enterprise
>manager?
>thx.
>>

>
A strange design. If the only key is random then how do you hope to
retrieve the information? If the rest of the data is to be identical
then why bother copying it?

Maybe he is generating test data?

Quote:

Originally Posted by

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>
INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

That does not look like it would work out. :-)

As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:

INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location

Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99A56D13F4F0DYazorman@.127.0.0.1...

Quote:

Originally Posted by

David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

>"Hamilton sucks" <caof@.mcmaster.cawrote in message
>news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

>>I need to add some records in a table called location(primary key:
>>loc_id). What I want to do is for each location in the table, I add
>>the same record but with a different loc_id, which can be a random
>>string. All the other column should contain the same value. Can anyone
>>give me a hint on how to do this in SQL server 2000 enterprise
>>manager?
>>thx.
>>>

>>
>A strange design. If the only key is random then how do you hope to
>retrieve the information? If the rest of the data is to be identical
>then why bother copying it?

>
Maybe he is generating test data?
>

Quote:

Originally Posted by

>DECLARE @.loc_id VARCHAR(36);
>SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>>
>INSERT INTO location (@.loc_id, col1, col2, ...)
> SELECT col1, col2, ...
> FROM location ;

>
That does not look like it would work out. :-)
>
As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:
>
INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location
>
>
Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks Erland. My mistake.

--
David Portas|||On Sep 7, 6:08 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

"Hamilton sucks" <c...@.mcmaster.cawrote in message
>
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
>

Quote:

Originally Posted by

Hi:
I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx.

>
A strange design. If the only key is random then how do you hope to retrieve
the information? If the rest of the data is to be identical then why bother
copying it?
>
DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>
INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;
>
--
David Portas

Thanks, david for your help. The reason for copying records is that I
need to change them to new records, which are exactly the same as the
old records except one column value. The loc_id is the primary key but
it's not really used for searching information. So basically I don't
care about the loc_id as long as they are unique, that 's why i want
it to be random.|||On Sep 8, 4:38 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

David Portas (REMOVE_BEFORE_REPLYING_dpor...@.acm.org) writes:

Quote:

Originally Posted by

"Hamilton sucks" <c...@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx.

>

Quote:

Originally Posted by

A strange design. If the only key is random then how do you hope to
retrieve the information? If the rest of the data is to be identical
then why bother copying it?

>
Maybe he is generating test data?
>

Quote:

Originally Posted by

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));

>

Quote:

Originally Posted by

INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

>
That does not look like it would work out. :-)
>
As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:
>
INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location
>
Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Maybe I should consult about the design. The table location holds the
location info about the local schools, each of them is linked to a
test group and a set of students. If I want to add a new test group,
which contains the same set of locations except that they are linked
to the new group. Should I create a new table or add new records into
tbl_location? The same problem holds for tbl_students as well since in
the new group, all students' status must be reset.|||"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189273920.752746.25660@.o80g2000hse.googlegro ups.com...

Quote:

Originally Posted by

>
The reason for copying records is that I
need to change them to new records, which are exactly the same as the
old records except one column value.

Then they are not exactly the same. Insert the new rows together including
the NEW column value(s). That way you can add any relevent candidate key
constraints to your table - something that wouldn't be possible if the table
had to support transitional "copies" of the old data. Example:

INSERT INTO tbl (col1, col2, col3)
SELECT col1, @.new_col2, col3
FROM tbl
WHERE ... ? ;

Quote:

Originally Posted by

Maybe I should consult about the design. The table location holds the
location info about the local schools, each of them is linked to a
test group and a set of students. If I want to add a new test group,
which contains the same set of locations except that they are linked
to the new group. Should I create a new table or add new records into
tbl_location? The same problem holds for tbl_students as well since in
the new group, all students' status must be reset.
>

This sounds very like a multi-valued or join-dependency situation. Are you
familiar with the Fourth and Fifth Normal Forms? If not then look up some
examples. You should satisfy yourself about the design based on your own
understanding of the business rules. It's notoriously difficult to give
detailed design advice in an online discussion. (Easy to spot potential
problems but hard to suggest the right solutions).

--
David Portas

add new row to existing table

I have about 600 records that I want to add to an existing table in a DB. T
he table row contains about 48 columns. I have data for 4 of those columns
in an excel spreadsheet. All other columns except 3 can be null. If I put
a value in the non-null col
umns, then I assume I can use the DTS import wizard to import the data from
the spread sheet to the desired table in the DB. Am I correct?
Thanks
JoelJoel,
You should be able to do that. You can also try something like this:
insert into
yourTable(a,b,c,d,other_not_nullA,other_
not_nullB,other_not_nullC)
select A,B,C,D,'whatever', 'whatever', 'whatever'
from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;
HDR=YES;
IMEX=1;
Database=c:\path_to\yourSpreadsheet.xls',
'select * from [Sheet1$]'
)
If the other not null columns are not strings, use constants of the
appropriate type. The DTS wizard might be easier - there are some
issues importing Excel with OpenRowset if the column types aren't
obvious from the data, and with blank values, but with only 600 rows,
you can probably just run the select from the insert above and look to
see if what you see is right.
A quick and dirty way to get data from Excel to SQL Server is to use
string functions and concatenation in Excel to create a column something
like
="insert into
yourTable(a,b,c,d,other_not_nullA,other_
not_nullB,other_not_nullC)
values('"&A1&"','"&A2 ... and so on,
then copy and paste the insert statements into Query Analyzer and run them.
Steve Kass
Drew University
joel wrote:

>I have about 600 records that I want to add to an existing table in a DB. The tabl
e row contains about 48 columns. I have data for 4 of those columns in an excel spr
eadsheet. All other columns except 3 can be null. If I put a value in the non-null
co
lumns, then I assume I can use the DTS import wizard to import the data from the spread she
et to the desired table in the DB. Am I correct?
>Thanks
>Joel
>

add new record - weird results

I have written a generic script in asp to add records to a table. The
script works fine with one table but in the other tables it updates
the first record in the table with the values for the new record and
adds a new record with all null values?!? Here is the script:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
'INSERT******************************************* ******************
'Open connection to sub-table
if action = "insert" then
cnnFormToDB.Open "SELECT top 1 * FROM " &subtable,
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta",
adOpenKeyset,adLockOptimistic
cnnFormToDB.AddNew
else
cnnFormToDB.Open "SELECT top 1 * FROM " & subtable & " WHERE ID = " &
ID, "DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
End If

if not cnnFormToDB.eof then
cnnFormToDB.MoveFirst
end if
'DELETE******************************************* ********************
if action = "delete" then
cnnFormToDB.Delete
cnnFormToDB.Close
else
'Build 2nd SQL String
For i=0 To Ubound(aFields)
cnnFormToDB(aFields(i)) = aValues(i)
Next

'Insert record into sub-table
cnnFormToDB.Update

The even weirder thing is I know that values in aFields and aValues
are OK because this test script I wrote for one of the tables works
just fine:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
cnnFormToDB.Open "SELECT top 1 * FROM FlightsDirect",
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
cnnFormToDB.AddNew

cnnFormToDB("fkCity") = 198
cnnFormToDB("fkDepartureAirport") = 159
cnnFormToDB("ValidFrom") = "17/09/2003"
cnnFormToDB("ValidTo") = "15/10/2003"
cnnFormToDB("fkType") = 1
cnnFormToDB("ReturnFlight") = 1
cnnFormToDB("fkReturnAirport") = 184
cnnFormToDB("Price") = yyyyyy
cnnFormToDB("fkATOL") = 5346

cnnFormToDB.Update

Any suggestions appreciated

Thanks

AlisonButtercup (alison_clark20@.hotmail.com) writes:
> I have written a generic script in asp to add records to a table. The
> script works fine with one table but in the other tables it updates
> the first record in the table with the values for the new record and
> adds a new record with all null values?!? Here is the script:

I cannot really say what is going on. The problem with ADO is that
while it tries to hides to the SQL from you, it does actually makes
you more confused, because you don't know what is going on under the
covers.

You can use the Profiler to see what ADO submits to SQL Server.

However, rather than relying on ADO doing things right by chance, I
would encourage you to use stored procedures instead. Then you don't
use these .AddNew or .Update methods.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Add fields vs. Join new table

Is there any sort of rule of thumb for this situation?
I have a fairly large table in my database (currently 21 fields with
over 800,000 records and growing) that is selected from, inserted to and
updated fairly often. I am adding some new features and cant decide if
it would be more efficient to add 2 fields to this table (of which 97%
of records would be null in these 2 fields) or create a new table that
would need 4 fields but only need records for the 3% and join to 1st
table for queries and reports.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!>> Is there any sort of rule of thumb for this situation?
Yes, determine whether these potential columns are really attributes of the
entity type, which the table represents. Based on your narrative, since you
have a mostly NULLs, it is reasonable to consider them as inapplicable
attributes. So they must be moved to another table. This would give you the
logical solution.
Btw, in some cases certain designers trivialize logical consequences and pay
more attention to space and performance related considerations. The myopic
justification is such cases is generally focused on the reduction in the
number of joins as an aid in performance improvements.
Anith|||I think you should have a separate table for those fields and join the 1st
table for reports and queries.
"Debbie" wrote:

> Is there any sort of rule of thumb for this situation?
> I have a fairly large table in my database (currently 21 fields with
> over 800,000 records and growing) that is selected from, inserted to and
> updated fairly often. I am adding some new features and cant decide if
> it would be more efficient to add 2 fields to this table (of which 97%
> of records would be null in these 2 fields) or create a new table that
> would need 4 fields but only need records for the 3% and join to 1st
> table for queries and reports.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

Add a where condition only if a parameter exists

I'm pretty new to TSQL. I'm trying to write a generic proc that returns
either ALL records from a table or (if a parameter is set to 1) or only a
sub-set of that table (if parameter is set to 0).
Here's an example of what I'm aiming for:
Create table contact (
contactID int,
FullName varchar (40),
IsActive bit)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
-- if @.ShowAll is null, return all records
--otherwise limit records to records where isActive = True
How can I conditionally add a WHERE clause based on the value of @.ShowAll?
Thanks!
- JohnnyI presume that ContactId is the prinary key of Contacts. If this is the case
,
skip the @.ShowAll parameter.
If @.ContactId is null then have it return all, otherwise have it return only
that contact:
SELECT *
FROM Contacts
WHERE ContactId = COALESCE(@.ContactId,ContactId)
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"JohnnyMagz" wrote:

> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||You need dynamic sql in this case
something like (untested)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
declare @.sql varchar(8000)
set @.sql = 'select * from contact'
if @.showall is not null
set @.sql = @.sql + ' where isactive = 1'
exec (@.sql)
go
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||Assuming isActive is never NULL and @.ShowAll is either NULL or 1.
...where isActive = isnull(@.ShowAll, 0) or isActive = @.ShowAll
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny