Combining Stored Procedures

I have two stored procedures

ALTER PROCEDURE dbo.qryCountOne
(@.inputID int)
AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
(dbo.TableOne.value = @.inputID)

ALTER PROCEDURE dbo.qryCountTwo
(@.inputID int)
AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
(dbo.TableTwo.value = @.inputID)

What would be the best way to combine these two, so that I only have to
make one database query, and the two values (CountOne, and CountTwo)
will get returned to me?

Any help\pointers greatly appreciated,

Noel"Noel" <vbgooglegroups@.yahoo.com> wrote in message
news:1120752722.113719.37280@.g44g2000cwa.googlegro ups.com...
>I have two stored procedures
> ALTER PROCEDURE dbo.qryCountOne
> (@.inputID int)
> AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
> (dbo.TableOne.value = @.inputID)
> ALTER PROCEDURE dbo.qryCountTwo
> (@.inputID int)
> AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
> (dbo.TableTwo.value = @.inputID)
> What would be the best way to combine these two, so that I only have to
> make one database query, and the two values (CountOne, and CountTwo)
> will get returned to me?
>
> Any help\pointers greatly appreciated,
> Noel

Output parameters are usually the best way to return scalar values from a
stored proc, so perhaps something like this?

create proc dbo.GetRowCounts
@.TableOneID int
@.TableOneCount int OUTPUT,
@.TableTwoID int,
@.TableTwoCount int OUTPUT
as
begin
select @.TableOneCount = count(*)
from dbo.TableOne
where col = @.TableOneID

select @.TableTwoCount = count(*)
from dbo.TableTwo
where col = @.TableTwoID
end

If you have to use a result set instead of output parameters, then see
"UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
MSSQL, so if that is the real column name, you might want to consider
changing it if possible - see "Reserved Keywords" in BOL.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> If you have to use a result set instead of output parameters, then see
> "UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
> MSSQL, so if that is the real column name, you might want to consider
> changing it if possible - see "Reserved Keywords" in BOL.

It's listed among the "Future keywords". Given the record of SQL Server
I would not hold my breath until all those words become reserved.

T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> T-SQL has this funny notion of unreserved keywords, and they seem to grow in number with every release.<<

They got that idea from ANSI, which has such a list when we were
looking at the SQL3 working draft.|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>> T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.<<
> They got that idea from ANSI, which has such a list when we were
> looking at the SQL3 working draft.

Nah, I was thinking of things like OUTPUT - which must have been around
since the 80s. OUTPUT is a keyword, but it's not reserved and you
can create a table or a column with that name, without any quoting.

But I assume you were thinking of the list of "Future keywords". That
does indeed seem like an ANSI list.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's great, thanks!

Noel|||Yes, I agree it's unlikely to be a problem, but I generally prefer to
recommend that people follow best practices as documented by Microsoft.
For me, that's a better option than assuming that something has never
been a problem in the past, so it's going to be OK in the future (cf
the short article in this month's SQL Server Magazine on xp_reg% procs
behaviour in SP4).

Simon

Combine two int columns into one bigint column

Hello
I have created a database that uses an int for the table primary keys.
This database is being deployed at several sites. Each site has a
unique site ID which is less than 100.
There is another database that acts as a master viewer. This database
holds a copy of all the site databases to do reports across all the
sites. Basically each site database gets 'merged' with the master
database once a month.
The master viewer database uses exactly the same structure as the site
database, but needs to store the site ID with every record. I have
currently achieved this by changing the table primary key to be a
bigint and store the site ID in 30 bits and the actual site record
primary in the remaining 34 bits (ok I could have used 32/32). The
reason for not adding another primary key column is because I use the
exact same sql queries in the master database and site databases, and
adding another column would mean creating two seperate queries when
joining tables (one to work on the master and one to work on the sites
- more work and difficult to maintain).
It is relatively simple to extract the site ID and record primary key
from the bigint using bitwise operations and bit shifting.
Unfortunately sql does not support bit shifting and I use division for
the same affect. The only downside I see is the performance issue when
extracting the site primary key in a sql query. If I want to test the
site primary key I use "WHERE ((MaintenanceTransaction_PRK &
-1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
1073741824. If the table has tens of thousands/millions of records I
can see this taking a while.
Does anyone have any other suggestions that I could use?
Many thanks
PaulChange your database in the master and client sites to include site ID as
part of the key in all of them. Then you can write one SQL that will run
against all of your databases equally. This will server you better in the
long run.
Or you can simply multiply the PK by 10000 and add the site ID to it to get
the new ID. This will give you a new combined ID without all that screwing
around with bits. Much simpler and you will have room for 10000 customers
before you run out of site IDs. This really is a kludge, however, and not
the best way to solve the problem.
<kerplunkwhoops@.yahoo.co.uk> wrote in message
news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...
> Hello
> I have created a database that uses an int for the table primary keys.
> This database is being deployed at several sites. Each site has a
> unique site ID which is less than 100.
> There is another database that acts as a master viewer. This database
> holds a copy of all the site databases to do reports across all the
> sites. Basically each site database gets 'merged' with the master
> database once a month.
> The master viewer database uses exactly the same structure as the site
> database, but needs to store the site ID with every record. I have
> currently achieved this by changing the table primary key to be a
> bigint and store the site ID in 30 bits and the actual site record
> primary in the remaining 34 bits (ok I could have used 32/32). The
> reason for not adding another primary key column is because I use the
> exact same sql queries in the master database and site databases, and
> adding another column would mean creating two seperate queries when
> joining tables (one to work on the master and one to work on the sites
> - more work and difficult to maintain).
> It is relatively simple to extract the site ID and record primary key
> from the bigint using bitwise operations and bit shifting.
> Unfortunately sql does not support bit shifting and I use division for
> the same affect. The only downside I see is the performance issue when
> extracting the site primary key in a sql query. If I want to test the
> site primary key I use "WHERE ((MaintenanceTransaction_PRK &
> -1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
> MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
> 1073741824. If the table has tens of thousands/millions of records I
> can see this taking a while.
> Does anyone have any other suggestions that I could use?
> Many thanks
> Paul
>|||Hello
I did consider changing the whole of the project to include the site ID
as a seperate column in the site database, but that would be a huge
amount of work and the master viewer is a special case for one
customer. I was trying to make it as simple as possible. It all works
correctly now, but I am just concerned about the performance.
Your solution about mutliplying by 10000 is the same concept to what I
do currently. It still has the problem of division to extract the
primary key.
Thanks
Paul
Jim Underwood wrote:
> Change your database in the master and client sites to include site ID as
> part of the key in all of them. Then you can write one SQL that will run
> against all of your databases equally. This will server you better in the
> long run.
> Or you can simply multiply the PK by 10000 and add the site ID to it to ge
t
> the new ID. This will give you a new combined ID without all that screwin
g
> around with bits. Much simpler and you will have room for 10000 customers
> before you run out of site IDs. This really is a kludge, however, and not
> the best way to solve the problem.
> <kerplunkwhoops@.yahoo.co.uk> wrote in message
> news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...|||I am not sure if you will be able to get the Site Id by using multiplication
and division.
Consider the following scenario
Site Id Other Id
10 1000 = 10X1000 = 10000
20 500 = 20X500 = 10000
You will not be able to find out the site Id using division as both the
multiplication
results in the same value.
You could try changing the datatype of Master database's Id col to VARCHAR
and have Id values as
10 concatenated with 00001000 as 1000001000
20 concatenated with 00000500 as 1000000500.
I hope doing this will not affect your Queries as there are not new column
and only a DataTypeChange. You could also extract the site id by using
substring (first 2 chrs) functions.
- Sha Anand
"kerplunkwhoops@.yahoo.co.uk" wrote:

> Hello
> I did consider changing the whole of the project to include the site ID
> as a seperate column in the site database, but that would be a huge
> amount of work and the master viewer is a special case for one
> customer. I was trying to make it as simple as possible. It all works
> correctly now, but I am just concerned about the performance.
> Your solution about mutliplying by 10000 is the same concept to what I
> do currently. It still has the problem of division to extract the
> primary key.
> Thanks
> Paul
>
> Jim Underwood wrote:
>

Combine Rows in Search Result

In Sql Server 2005 Express I have this table:

CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)

Each paragraph of text (SectionText) is in its own row(SectionNo) Each primary document has a DocumentTypeID of 1 withthree subdocument types (2=Index, 3=Background, 4=Report).

I run this query and return a collection of single rows from various documents grouped together by DocumentNo:

SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo

For each row that contains the search term, I would like toreturn the full document (all rows as parapraphs within one row ofreturned data). In other words, I want to reconstitute the fulldocument as it existed prior to being inserted into the database withparagraph separation.

For exampe, if the search term is in row 3of DocumentNo=5, DocumentTypeID=2, I want to return all the rows ofthat document in one block of text that retains paragraph format(preferablly with a line break and carriage return betweenparagraphs). How can this be done?

You can do this trick which will lead you to solve the problem.

Okay, let say you need to group each page's paragraph in one record insted of many records (as in your current case).

Step#1:

So, Create another table with following columns :
1) BookID: Int or smallint
2) PageID: Int or smallint
3) PageText: Text or NText

Step#2:

1) Do acursor that will loop throug all of theparagraphs related to aspecific page.
2) DoINSERT thefirst record into thePageText field of thenew created table, while you doUPDATEfor therest of recordsafter concatenatingthem with value already exists in thePageText field.

Step#3:

Do this for each page in each book.

Result:

At the end you will have one table from which you can query and seach about any word/paragraph in any page in any book!!

Good luck.

|||

Thanks for the suggestion. I will give it a try.

Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
I would get the following result
Locations
--
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.posting.google.com...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?|||On 22 Apr 2004 03:02:07 -0700, michael wrote:
>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com...
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
> >I have a table Venues
> >ID int
> >Location1 char(10),
> >Location2 char(10),
> >Location3 char(10),
> >Location4 char(10)
> >
> >and would like to have a query that returns a single column of
> >Locations i.e
> >for the record
ID=2,Location1=Boston,Location2=NewYork,Location3=London,Location4=Paris
> >I would get the following result
> >
> >Locations
> >--
> >Boston
> >NewYork
> >London
> >Paris
> >
> >Is it possible to merge the values from columns location1,location2
> >etc into a new column?
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message
>news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.4ax.com...
>> On 22 Apr 2004 03:02:07 -0700, michael wrote:
>> >I have a table Venues
>> >ID int
>> >Location1 char(10),
>> >Location2 char(10),
>> >Location3 char(10),
>> >Location4 char(10)
>> >
>> >and would like to have a query that returns a single
column of
>> >Locations i.e
>> >for the record
>ID=2,Location1=Boston,Location2=NewYork,Location3=London,L
ocation4=Paris
>> >I would get the following result
>> >
>> >Locations
>> >--
>> >Boston
>> >NewYork
>> >London
>> >Paris
>> >
>> >Is it possible to merge the values from columns
location1,location2
>> >etc into a new column?
>> SELECT Location1 AS Locations
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location2
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location3
>> FROM Venues
>> WHERE ID = 2
>> UNION ALL
>> SELECT Location4
>> FROM Venues
>> WHERE ID = 2
>>
>> By the way, your design is not properly normalized. The
>> Venue-Locations should be in a seperate table.
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>.
>

Combine matching multiple rows into one row

IS there a way to combine all matching rows in a table so that it
outputs as one row, for example:

tblMyStuff
UniqueID int IDENTITY
ParentID int
SomeSuch nvarchar(50)
SomeSuch2 nvarchar(50)

Table data:
UniqueID ParentID SomeSuch SomeSuch2
1 1 Dog Bark
2 1 Cat Meow
3 3 Cow Moo
4 3 Horse Whinnie
5 5 Pig Oink

Desired query result from Query:
SELECT ? as myText from tblMyStuff WHERE ParentID = 3
myText = Cow Moo, Horse Whinnie

Help is appreciated,
lqlaurenq uantrell (laurenquantrell@.hotmail.com) writes:
> IS there a way to combine all matching rows in a table so that it
> outputs as one row, for example:
> tblMyStuff
> UniqueID int IDENTITY
> ParentID int
> SomeSuch nvarchar(50)
> SomeSuch2 nvarchar(50)
> Table data:
> UniqueID ParentID SomeSuch SomeSuch2
> 1 1 Dog Bark
> 2 1 Cat Meow
> 3 3 Cow Moo
> 4 3 Horse Whinnie
> 5 5 Pig Oink
> Desired query result from Query:
> SELECT ? as myText from tblMyStuff WHERE ParentID = 3
> myText = Cow Moo, Horse Whinnie

SELECT ltrim(str(UniqueID)) + '|' + ltrim(str(ParenID) + '|' +
SomeSuch + '|' + SomeSuch2
FROM tbl

Of course these theme can be varied in several ways, depending if you
want a delimiter, the numeric values to be padded etc.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Will this do you?

DECLARE @.Str nvarchar(500)
SELECT @.Str=CASE WHEN @.Str IS NULL THEN '' ELSE @.Str+', ' END+SomeSuch+'
'+SomeSuch2 from tblMyStuff WHERE ParentID = 3
SELECT @.Str

Mr Tea
http://mr-tea.blogspot.com

"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1106447396.269656.91240@.z14g2000cwz.googlegro ups.com...
> IS there a way to combine all matching rows in a table so that it
> outputs as one row, for example:
> tblMyStuff
> UniqueID int IDENTITY
> ParentID int
> SomeSuch nvarchar(50)
> SomeSuch2 nvarchar(50)
> Table data:
> UniqueID ParentID SomeSuch SomeSuch2
> 1 1 Dog Bark
> 2 1 Cat Meow
> 3 3 Cow Moo
> 4 3 Horse Whinnie
> 5 5 Pig Oink
> Desired query result from Query:
> SELECT ? as myText from tblMyStuff WHERE ParentID = 3
> myText = Cow Moo, Horse Whinnie
> Help is appreciated,
> lq

Combine and

I have a table which has the following columns.

id - int(11)
catid - int(11)
title - varchar(60)
content - text
parent - int(11)
postdate - datetime
user - int(11)
view - int(11)
email - char(1)
emailed - char(1)

Here's what I'm trying to accomplish.

This table is for a forum. If a user posts a question and selects to be automatically email the email column will be set to 'Y'

So when a user responds to the post the emailed column will be 'Y'

Every hour or so I will do a cron job to send out an email to the original poster that he/she has a reply to their post.

I want to get a list of those id's that have email 'Y' and emailed = 'Y'

I can get those queries separtely below, but want to do it in one. how can I accomplish this...

my queries.

SELECT * FROM forum_tbl where parent = 0 and email = 'Y' // original post where user wants emails

SELECT * FROM forum_tbl where parent > 0 and emailed = 'N' // reply to post where email has not been sent.

I'm using MySQL 4.0.20a-max

Thank you.You can use the UNION of both selects to get the result set:

select ....
UNION
select ....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')|||You can use the UNION of both selects to get the result set:

select ....
UNION
select ....

or you can use

SELECT *
FROM
forum_tbl
WHERE
(parent = 0 and email = 'Y') OR (parent > 0 and emailed = 'N')|||The outcome puts the two selects together but I need to eliminate some of the information, like an intersect, but I can't do that in MySQL.

The first select has the ID I want. The second had the parent ID.

I need to match only those.

Make sense?

Thanks.|||Can you post an examle of what you want done? a few records in the table and the result set you are looking for, maybe that way I can help out better.|||id catid title content parent postdate user views email emailed
19 3 test test con 0 2004-12-07 00:00:00 1 3 Y NULL

24 2 test again 0 2004-01-08 12:52:04 1 11 Y NULL
25 2 test ing 24 2004-12-08 00:00:00 1 0 NULL N

This is the outcome of the union your posted earlier.

The outcome I'm looking for is that it only should show id 24 nothing else because it is the only one that has a reply that has not been "emailed".

Thanks.

Column Default Value

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.

Quote:

Originally Posted by J

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.

You have to create trigger

Column Default Value

I want a table to include these columns:
UserID, int, IDENTITY
GroupID, int
I would like for the default value of the GroupID to be equal to the UserID.
The problem is the default only accepts a constant value. Any way to
accomplish this?
Thanks.You can create a trigger to set the GroupID to the UserID value.
CREATE TABLE Foo (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
GroupID INT,
datacol CHAR(1));
GO
CREATE TRIGGER SetGroupID
ON Foo
AFTER INSERT
AS
UPDATE Foo
SET GroupID = I.UserID
FROM Foo AS F
JOIN Inserted AS I
ON F.UserID = I.UserID
AND I.GroupID IS NULL;
GO
INSERT INTO Foo (datacol) VALUES('a');
INSERT INTO Foo (GroupID, datacol) VALUES(5, 'b');
SELECT UserID, GroupID, datacol
FROM Foo;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!
Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>