Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts

Thursday, March 29, 2012

concatenate a string within a loop from a temp table

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

An easy way (but slow if you have a big table) to do this is to use a cursor. Bellow is a fully functional example.

Hope it helps!

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

DECLARE @.MyResult TABLE (PK Int, SumOfPockets Varchar(4000))

DECLARE @.PK Int

DECLARE @.MyString Varchar(4000)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR

SELECT PK FROM @.MyTable GROUP BY PK

OPEN cMyTable

FETCH NEXT FROM cMyTable INTO @.PK

WHILE @.@.FETCH_STATUS=0

BEGIN

SET @.MyString = ''

SELECT @.MyString = @.MyString + pocket FROM @.MyTable WHERE PK = @.PK

INSERT INTO @.MyResult(PK, SumOfPockets) VALUES (@.PK, @.MyString)

FETCH NEXT FROM cMyTable INTO @.PK

END

CLOSE cMyTable

DEALLOCATE cMyTable

SELECT * FROM @.MyResult

|||

Doru,

This looks like a great solution although i have a very huge table and i ran out of resources when i tried to implement your solution. I was hopping i can do it with some loops and temp tables. do you have any other ideas.

Emad

|||i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.|||

Emadkb wrote:

i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.

If you're running on SQL Server 2005, you can use VARCHAR(MAX) and NVARCHAR(MAX) variables in your string concatenation.

|||we are still on SQL 2000|||

Hi Emad,

If Varchar(8000) is not big enough then you'll have to use Text datatype in your destination table. This will slow down the whole thing even more :-(.

A solution that should work, but which is very slow, is:


DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')


CREATE TABLE #MyResult (PK Int, SumOfPockets Text)
DECLARE @.PK Int, @.pocket Varchar(10), @.prev_PK Int
DECLARE @.ptrColText Varbinary(16)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR
SELECT PK, pocket FROM @.MyTable

OPEN cMyTable
FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.PK = @.prev_PK
UPDATETEXT #MyResult.SumOfPockets @.ptrColText NULL 0 @.pocket
ELSE
BEGIN
INSERT INTO #MyResult(PK, SumOfPockets)
VALUES (@.PK, @.pocket)

SELECT @.ptrColText = TEXTPTR(SumOfPockets)
FROM #MyResult WHERE PK = @.PK

SET @.prev_PK = @.PK
END

FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

END
CLOSE cMyTable
DEALLOCATE cMyTable

SELECT * FROM #MyResult


|||

This looks exactly like i want. Excellent work. I will try it and let you know how it goes.

Emad

Sunday, March 11, 2012

compond index and key faster/better?

In a situation where you have two tables in a hierarchy like this:

create table authors (authorid int identity (1,1))

create table books (
authorid int,
bookid int identity (1,1)
)

Is there any disadvantage to having the primary key and the clustered
index as a compound key, like this:

alter table books add constraint PK_books primary key clustered
(authored, bookid)

Normally, I would make bookid the key, but then I got to thinking, most
of the queries are going to be "select * from books where authorid =
@.@.some_authorID"

So, wouldn't a compound key and index make this a little faster?(eric.nave@.gmail.com) writes:
> create table books (
> authorid int,
> bookid int identity (1,1)
> )
> Is there any disadvantage to having the primary key and the clustered
> index as a compound key, like this:
> alter table books add constraint PK_books primary key clustered
> (authored, bookid)

That looks a little funny. Since bookid is unique, why add authorid
to the PK?

Then again, PK of a books table should probably be the ISBN, as that
is a natural key.

And there should probably be a relation table, as a book can more than
one author.

> Normally, I would make bookid the key, but then I got to thinking, most
> of the queries are going to be "select * from books where authorid =
> @.@.some_authorID"
> So, wouldn't a compound key and index make this a little faster?

Having the clustered index on authorid is probably better than clustering
on ISBN, yes. But there not really any reason to add it to the PK of
books. (In an bookauthors table that covers the many-to-many relation
between books and authors, the key would make sense.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> That looks a little funny. Since bookid is unique, why add authorid
> to the PK?

Only for the purpose of having the clustered index that way. I think
my example would have been better if I'd made it a case of, "should I
have a simple or a compond index" and left the key out of it. My
mistake.

> Then again, PK of a books table should probably be the ISBN, as that
> is a natural key.

Well, this is just an example off the top of my head. Books and
authors is just the first think I came up with.

> And there should probably be a relation table, as a book can more than
> one author.

Same response as above.

> Having the clustered index on authorid is probably better than clustering
> on ISBN, yes. But there not really any reason to add it to the PK of
> books.

I concede that there's no reason to have it in the PK. But, having a
compond clustered index seems like a good idea if you constantly want
to find all books by a given author. The books will then be returned
in bookid order.|||> But, having a
> compond clustered index seems like a good idea if you constantly want
> to find all books by a given author. The books will then be returned
> in bookid order.

Although it is likely that data will be returned in sequence by bookid, the
order is guaranteed only when bookid is specified in an ORDER BY clause. A
clustered index including bookid will facilitate efficient ordering in this
case.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<eric.nave@.gmail.com> wrote in message
news:1125527844.570677.118750@.z14g2000cwz.googlegr oups.com...
> Erland Sommarskog wrote:
>> That looks a little funny. Since bookid is unique, why add authorid
>> to the PK?
> Only for the purpose of having the clustered index that way. I think
> my example would have been better if I'd made it a case of, "should I
> have a simple or a compond index" and left the key out of it. My
> mistake.
>> Then again, PK of a books table should probably be the ISBN, as that
>> is a natural key.
> Well, this is just an example off the top of my head. Books and
> authors is just the first think I came up with.
>> And there should probably be a relation table, as a book can more than
>> one author.
> Same response as above.
>> Having the clustered index on authorid is probably better than clustering
>> on ISBN, yes. But there not really any reason to add it to the PK of
>> books.
> I concede that there's no reason to have it in the PK. But, having a
> compond clustered index seems like a good idea if you constantly want
> to find all books by a given author. The books will then be returned
> in bookid order.

Tuesday, February 14, 2012

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Thats not working in SQL 2000. You will have to create it in compat. mode 2k.

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||

Would you please show me a sample code?

Thanks

|||Hi,

the equivalent would be:

CREATE TABLE [dbo].[City](

[CityID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED

(

[CityID] ASC

) ON [PRIMARY]

)

GO

CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])

WITH IGNORE_DUP_KEY ON [PRIMARY]

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)

) ON [PRIMARY]

Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.

|||

Thanks for the reply.

I generated the script using SMO in the following steps:

1. In SMO, right-click on the table City, choose Modify.

2. Highlight CityID, click the "Set Primary Key" button and save the change

3. Right-click on table City, choose "Script table as", choose "Create to"....

Thanks.

|||

Hi

u can't use ignore_dup_key with constraint. u need to create index key.

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

|||

I upgraded my SQL Server 2005 to SP1.

Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.

Problem solved!!

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Thats not working in SQL 2000. You will have to create it in compat. mode 2k.

HTH, Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||

Would you please show me a sample code?

Thanks

|||Hi,

the equivalent would be:

CREATE TABLE [dbo].[City](

[CityID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED

(

[CityID] ASC

) ON [PRIMARY]

)

GO

CREATE UNIQUE INDEX [SomenewIndex] ON [dbo].[City]([CityID])

WITH IGNORE_DUP_KEY ON [PRIMARY]

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What did you use to generate the script above? Did you use SMO or Tools? Did you select the correct compatibility level for the script? If you cannot get the correct output for the script then you should just create it by hand since it is easier that way and avoids all the other unnecessary default options. For example, the default for IGNORE_DUP_KEY is OFF for PRIMARY/UNIQUE key constraints and you should probably avoid using it anyway. So you can write DDL like:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)

) ON [PRIMARY]

Btw, if you cannot get the tools or SMO to work correctly then please file a bug using http://connect.microsoft.com/sqlserver or create a thread in the SQL Server Tools forum.

|||

Thanks for the reply.

I generated the script using SMO in the following steps:

1. In SMO, right-click on the table City, choose Modify.

2. Highlight CityID, click the "Set Primary Key" button and save the change

3. Right-click on table City, choose "Script table as", choose "Create to"....

Thanks.

|||

Hi

u can't use ignore_dup_key with constraint. u need to create index key.

CREATE TABLE [dbo].[test] (
[id] [int] NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [ind_id] ON [dbo].[test]([id] DESC ) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

|||

I upgraded my SQL Server 2005 to SP1.

Then this line : WITH (IGNORE_DUP_KEY = OFF) is removed from generated script.

Problem solved!!

Compatibility of SQL 2000

I find that the "Create Table" script generated by SQL Server 2005 is in format:

CREATE TABLE [dbo].[City](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can this appliable in SQL Server 2000 or MSDE? If not, how should I change it to make it work in both SQL 2000 and 2005?

Thanks

Drop the With( IGNORE_DUP_KEY = OFF ) clause. That is not supported in the Create statement in 2000. It refers to how the index created to support the constraint should react to duplicate keys. This option can be specified if the index is created separately. OFF is the default setting, so removing it will not change the behavior.