How to create hash function for NTEXT

I have a table
create table t
(
val ntext(16),
id int not null identity,
hash nvarchar(32)
)
I'd like to avoid rows with double ntext values, thats why I want to compare
hast values only.
But how can I create hash for ntext ?
ThanksOleg,
You could try using checksum_agg after chunking the ntext column
into 8000 byte pieces. Here's an example using image (there is a long
image column in a Northwind table that makes it easier to write a repro).
checksum() and checksum_agg() are not good hash functions, but they
will help you identify rows that may be the same. You will still have to
check to see if they are the same, as you would with any hash function.
create table Integers (
chunk int primary key
)
insert into Integers select ProductID-1 from Northwind..Products
go
create table textVals (
pk int identity primary key,
t image,
hashVal int
)
insert into textVals select photo, null from Northwind..Employees
go
update textVals set
hashVal = (
select checksum_agg(ca) from (
select checksum(substring(tv.t,1+8000*chunk,8000)) as ca
from Integers, textVals as tv
where tv.pk = textVals.pk and chunk <= datalength(t)/8000
) T
)
select datalength(t) as t_len, hashVal from textVals
go
drop table Integers, textVals
-- Steve Kass
-- Drew University
Oleg Cherkasenko wrote:
>I have a table
>create table t
>(
>val ntext(16),
>id int not null identity,
>hash nvarchar(32)
>)
>I'd like to avoid rows with double ntext values, thats why I want to compare
>hast values only.
>But how can I create hash for ntext ?
>Thanks
>
>

how to create array column and how to retrive in sqlserver

hi

i am using database sqlserver,
i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server

1.how to create array column in a table using sqlserver

if possible how can i use select query for that

there is no array concept in sql server. you can however store the values as a concatenated string with a delimiter and use some custom function to parse through the string to split them up.|||

Try the link below for samples using the string functions in SQL Server and Oracle PL/SQL is closer to C++ than T-SQL. I would also look at Ken Henderson books at my local bookstore run a search for String functions in the BOL(books online). Hope this helps.

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||Why do you want to store multiple values in a single column? Thishas some pretty huge implications from a relational modelingstandpoint, not to mention the fact that it's going to kill performanceif you ever need to query the thing. If you can post someinformation about what business problem you're trying to solve, I'mcertian we can help you find a better solution.

|||

Sorry I forgot I have a link with SQL Server Arrays. Try the link below. Hope this helps.

http://www.sommarskog.se/arrays-in-sql.html

How to create an table with composite Key?

Hi all,

well i want to have an web-based database application in which in an single webform , i need to insert the values to 5 different tables.

here empid is primary key (PK) in one table and in the rest of the tables its an Foreign Key(FK)

my question is, how can i create these tables with composite key?

ie, i want to creat an table which will have FK and as well another key which both will uniquely idenitfies a row.

Well i am using SQL server 2000 and i wish to create these tables using the enterprise manager.

here are the tables

1) empidtable

empid,fname,lname

2)empcountrytable

empid,country

3)empvisatable

empid,visa

4)empdoctable

empid,doc

5)empfile

empid,filename,filecontenttype,filesize,filedata

Plz do help me

thanx in advance

If you're using Enterprise Manager, go to Design View of the table anduse control + click to select all the columns you want as part of thecomposite key, then right-click Set Primary Key.
|||The table with the composite key will have all the primary key columns of the five tables because a composite index must be on one table. You can have up to 16 columns and 900bytes so if you are using unicode you can only have NVARCHAR (450). The alternative which is used by Microsoft Consulting is to create a small Clustered index and add the column of the clustered index in all your nonclustered index on the table. You can use the index tuning Wizard which is part of the profiler to tell you the indexes you need on a table. Hope this helps.|||

Hey thanks for that.

will try to implement the same

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks a lot Jens.

How to create a trigger such that it can delete the rows whenever any other application such as

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

Hi,

actually Select has no trigger action, only DML operations are capable. You could use a stored procedure to read the rows rather than using a select statement (don′t know if BizTalk is able to do this through stored procedures).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to create a dimesnion for a range of values

I am new to OLAP and I was wondering if it is possible to create a dimension
in Analysis Services 2005 based on a range of values for a given field in a
Fact table.
Example:
Field Name: Quantity
Dimension Name: Quantity Range
Dimension Values: 0-100
101-200
201-1000
> 1000
The only solution I see now is to denormalize Quantity into a new field
QuantityRange that is computed based on the value of the quantity
1: 0-100
2: 101-200
3: 201-1000
4: > 1000
The values 1 to 4 would be my dimension.
Thanks al lot
I gave you some ideas on the microsoft.public.sqlserver.olap newsgroup.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"carlos" <carlos@.discussions.microsoft.com> wrote in message
news:6043ABBE-7ADA-4E9D-8AA7-D252F075DAEB@.microsoft.com...
>I am new to OLAP and I was wondering if it is possible to create a
>dimension
> in Analysis Services 2005 based on a range of values for a given field in
> a
> Fact table.
> Example:
> Field Name: Quantity
> Dimension Name: Quantity Range
> Dimension Values: 0-100
> 101-200
> 201-1000
> The only solution I see now is to denormalize Quantity into a new field
> QuantityRange that is computed based on the value of the quantity
> 1: 0-100
> 2: 101-200
> 3: 201-1000
> 4: > 1000
> The values 1 to 4 would be my dimension.
> Thanks al lot
|||You could drive this denormalisation using the data in the dimension
table.
1) Create a dimension table with max and min columns
ID MinValue MaxValue
1 0 100
2 101 200
3 201 1000
4 1000 2147483648 <-- max integer value
2) Create a view on the fact table like the following
Select
...
<list fact columns here>
...
,dim.ID
from fact f
inner join dimQuantityRange dim
on f.Quantity between dim.MinValue and dim.MaxValue
3) Alter you cube to use the newly created view as the fact "table".
then you can join the dimension back onto the id in the view.
The advantage of doing things this way is that everything is data
driven. If you need to add more ranges or change the ranges you just
update the max/min columns in the dim table and re-process.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

How to create a dimesnion for a range of values

I am new to OLAP and I was wondering if it is possible to create a dimension
in Analysis Services 2005 based on a range of values for a given field in a
Fact table.
Example:
Field Name: Quantity
Dimension Name: Quantity Range
Dimension Values: 0-100
101-200
201-1000
> 1000
The only solution I see now is to denormalize Quantity into a new field
QuantityRange that is computed based on the value of the quantity
1: 0-100
2: 101-200
3: 201-1000
4: > 1000
The values 1 to 4 would be my dimension.
Thanks al lotI gave you some ideas on the microsoft.public.sqlserver.olap newsgroup.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"carlos" <carlos@.discussions.microsoft.com> wrote in message
news:6043ABBE-7ADA-4E9D-8AA7-D252F075DAEB@.microsoft.com...
>I am new to OLAP and I was wondering if it is possible to create a
>dimension
> in Analysis Services 2005 based on a range of values for a given field in
> a
> Fact table.
> Example:
> Field Name: Quantity
> Dimension Name: Quantity Range
> Dimension Values: 0-100
> 101-200
> 201-1000
> The only solution I see now is to denormalize Quantity into a new field
> QuantityRange that is computed based on the value of the quantity
> 1: 0-100
> 2: 101-200
> 3: 201-1000
> 4: > 1000
> The values 1 to 4 would be my dimension.
> Thanks al lot|||You could drive this denormalisation using the data in the dimension
table.
1) Create a dimension table with max and min columns
ID MinValue MaxValue
1 0 100
2 101 200
3 201 1000
4 1000 2147483648 <-- max integer value
2) Create a view on the fact table like the following
Select
..
<list fact columns here>
..
,dim.ID
from fact f
inner join dimQuantityRange dim
on f.Quantity between dim.MinValue and dim.MaxValue
3) Alter you cube to use the newly created view as the fact "table".
then you can join the dimension back onto the id in the view.
The advantage of doing things this way is that everything is data
driven. If you need to add more ranges or change the ranges you just
update the max/min columns in the dim table and re-process.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

How to create a button in Reports

Hello every one,
I am using reports 10g.In the Run time parameter form after given the
values for the parameters, i want to click a button so that the report
can run. we already have an option in the menu bar of the Run time
parameter form to run the report but our customers specfically needs
the button option. So how to create a Button in the Run time parameter
form?
Can anyone please help me on this,
Thanks
Balaji.On Nov 6, 12:27 am, rampal...@.gmail.com wrote:
> Hello every one,
> I am using reports 10g.In the Run time parameter form after given the
> values for the parameters, i want to click a button so that the report
> can run. we already have an option in the menu bar of the Run time
> parameter form to run the report but our customers specfically needs
> the button option. So how to create a Button in the Run time parameter
> form?
> Can anyone please help me on this,
> Thanks
> Balaji.
If you want to mix HTML Forms with ReportingServices Reports, I
suggest using ASP.NET. This way:
1. the page renders on your IIS server and is callable from a URL
2. you can have an HTML form with Inputs & Submit button
3. the Submit posts back to the IIS server which returns a page with
a RS Object rendered as an IFRAME in the HTML (ReportViewer I think it
is called)
4. The IIS takes the parameters from the URL using Request objects,
then passes those as Parameters to the ReportViewer object, which then
calls the Reporting Services RDL file, which renders in the web page.
Down side is that the report cannot be deployed to the Reporting
Services Server, but can be Navigated to via a URL to the ASP.NET
folder on your web server.
-- Scott

How to correctly update a table which values can be either inserted/updated/deleted on upd

Hi SQL fans,
I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:
________________________
__________________ | PortfolioTitle
|
| Portfolio |
+---+
+--+ | tfolio_id (int)
|
| folio_id (int) |<<-PK--FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK--PK->>[ Titles]
+--+ | tfolio_weight
(decimal(6,5)) |
+---+
Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).
My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)
For example, if the portfolio #2 would contain :
[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30
and I must update the PortfolioTitle based on these values :
idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40
then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio
For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio = 2), and then insert new values for each entry based on the new given
values.
Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?
And this applies to many situation :(
If you need other examples, I can give you.
thanks a lot!
ibizaerrr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\
here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:
>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio
Hi ibiza,
Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):
-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids = (SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids
-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL
This is just the basic outline - you should enclose it in a transaction
and add proper error handling.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.
If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?
Thanks again! :)
ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:
>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.
Hi ibiza,
Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!
If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.
>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?
If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.
(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).
--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.
Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Yes, you are both right, I will remove it then.
I thought it was always a good idea to have an identity primary key on
any table.
Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.
If you ask some people, they will tell you that it is never a good idea!
Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

How to correctly update a table which values can be either inserted/updated/deleted on upd

Hi SQL fans,

I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:

________________________
__________________ | PortfolioTitle
|
| Portfolio |
+------------+
+----------+ | tfolio_id (int)
|
| folio_id (int) |<<-PK--FK--| tfolio_idfolio (int)
|
| folio_name (varchar) | | tfolio_idtitle (int)
|--FK--PK->>[ Titles]
+----------+ | tfolio_weight
(decimal(6,5)) |

+-------------+

Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).

My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)

For example, if the portfolio #2 would contain :

[ PortfolioTitle ]
id | idFolio | idTitre | poids
1 2 1 10
2 2 2 20
3 2 3 30

and I must update the PortfolioTitle based on these values :

idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40

then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio

For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =
2), and then insert new values for each entry based on the new given
values.

Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?

And this applies to many situation :(

If you need other examples, I can give you.

thanks a lot!

ibizaerrr...I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||errr...I realized the diagram is all messed up :\

here it is : http://img156.imageshack.us/img156/5096/model8al.gif|||On 16 Feb 2006 10:39:45 -0800, ibiza wrote:

>Hi SQL fans,
(snip)
>My problem is : When I update a portfolio, I must update all the
>associated titles in it. That means that titles can be either removed
>from the portfolio (a folio does not support the title anymore), added
>to it (a new title is supported by the folio) or simply updated (a
>title stays in the portfolio, but has its weight changed)
>For example, if the portfolio #2 would contain :
>[ PortfolioTitle ]
>id | idFolio | idTitre | poids
>1 2 1 10
>2 2 2 20
>3 2 3 30
>and I must update the PortfolioTitle based on these values :
>idFolio | idTitre | poids
>2 2 20
>2 3 35
>2 4 40
>then I should
>1 ) remove the title #1 from the folio by deleting its entry in the
>PortfolioTitle table
>2 ) update the title #2 (weight from 30 to 35)
>3 ) add the title #4 to the folio

Hi ibiza,

Deleting all rows, then re-inserting (as you do now) is one of the two
popular ways to acheive this. The second is (assuming that the new
values are stored in the table NewFolioTitles):

-- Step 1: DELETE rows that are no longer needed
DELETE FROM FolioTitles
WHERE NOT EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)

-- Step 2: UPDATE rows that have been changed
UPDATE FolioTitles
SET poids =
(SELECT poids
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre)
WHERE EXISTS
(SELECT *
FROM NewFolioTitles AS n
WHERE n.idFolio = FolioTitles.idFolio
AND n.idTitre = FolioTitles.idTitre
AND n.poids <> FolioTitles.poids)
-- Or, as an alternative, use the version below;
-- this is shorter and often faster, but uses
-- proprietary code and is therefor less portable
-- UPDATE f
-- SET poids = n.poids
-- FROM FolioTitles AS f
-- INNER JOIN NewFolioTitles AS n
-- ON n.idFolio = f.idFolio
-- AND n.idTitre = f.idTitre
-- WHERE n.poids <> f.poids

-- Step 3: INSERT new rows
INSERT INTO FolioTitles (idFolio, idTitre, poids)
SELECT n.idFolio, n.idTitre, i.poids
FROM NewFolioTitles AS n
LEFT JOIN FolioTitles AS f
ON f.idFolio = n.idFolio
AND f.idTitre = n.idTitre
WHERE f.idFolio IS NULL

This is just the basic outline - you should enclose it in a transaction
and add proper error handling.

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

thank you very much for your excellent reply.
Is there a big performance difference between the two methods? I guess
the only difference is that the identity numbers increment a lot
quicker with the first method of deleting a batch then reinserting a
batch, even if only a few records differ.

If I have to run this kind of update quite frequently (once/twice a
day, with thousands of records), what method would you suggest me?

Thanks again! :)

ibiza|||On 16 Feb 2006 12:54:19 -0800, ibiza wrote:

>Hi Hugo,
>thank you very much for your excellent reply.
>Is there a big performance difference between the two methods? I guess
>the only difference is that the identity numbers increment a lot
>quicker with the first method of deleting a batch then reinserting a
>batch, even if only a few records differ.

Hi ibiza,

Why would you even want to have an identity on the linking table
FolioTitles? Are there many other tables that refer to rows in this
table? I don't think so, since that would make deleting and re-inserting
rows a very bad idea!

If not, then stick to using only the natural key (FolioID + TitreID) and
leave the identity column out.

>If I have to run this kind of update quite frequently (once/twice a
>day, with thousands of records), what method would you suggest me?

If performance matters, than you should test both versions on your
hardware, with your data. That's the only way to get relevant data.

(My recommended test procedure is: clear cache; run one method a few
times, then clear cache again and run second method the same number of
times. Do thin on a test system that is as much as possible like your
production system. Compare average execution times).

--
Hugo Kornelis, SQL Server MVP|||ibiza (lambertb@.gmail.com) writes:
> thank you very much for your excellent reply.
> Is there a big performance difference between the two methods? I guess
> the only difference is that the identity numbers increment a lot
> quicker with the first method of deleting a batch then reinserting a
> batch, even if only a few records differ.

Why is there an IDENTITY columns at all? Surely a pair of
(foliotitle_idfolio, foliotitle_idtable) can only appear once in table?
Then this should be the primary key, and not any identity column.

--
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|||Yes, you are both right, I will remove it then.

I thought it was always a good idea to have an identity primary key on
any table.

Thanks for the tip!|||ibiza (lambertb@.gmail.com) writes:
> Yes, you are both right, I will remove it then.
> I thought it was always a good idea to have an identity primary key on
> any table.

If you ask some people, they will tell you that it is never a good idea!

Personally, I say it is a good idea when there is no useful natural key,
which often is the case for base entities: customers, financial
instruments, addresses etc. However for connection table, or tables
describing composed entities, articifical keys are usually not needed.

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

How to copy new values to a table

I have a table that I would like to add new values to only if those values are not already in the DB.

The table has one Name column which has a unique key, so I only want to insert records if the new value for the name is not already in the database.

I could send everything to a loading table and then delete the duplicate records on that loading table and then copy the remaining records to my final destination, but I think that there should be a cleaner way.

Johnhttp://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx