Replacement for MAX statement?

Hi all :
I got a problem here.

The scenario :
The serial key code of a table is auto generate, and currently I'm using MAX to return the latest serial key code. The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. The serial key code is the only unique key in the table.

Can anyone give me some guide or tips on how to resolve this? Or other way of implementing the select statement to avoid usage of MAX?

Thanks in advance.the best way to avoid this is not to return the latest serial key at all

what do you need it for?|||I need to get the serial key code as a foreign key to insert into another table. The Serial key code will server as a customer ID in my case. since no customer ID can be and should be duplicate, I need to get the serial key code.

I had figure out a way, using a select * statement and use a while loop to loop thru the whole result set collection. From there I can get the last serial key code.

I got a question here, if I use this way, will it be less effective than using MAX? Consider I got a table with more than 5000 record.

Thanks.|||On the table you're inserting into, will the SerialKeyCode be unique, or will you have a one-to-many relationship?|||Serial Key Code will be generate automatically by the database and been set to unique with increament by 1 everytime new record is add.|||The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. Thanks in

If you return the maximum serial ID at 14:00 and then again at 14:01 they may indeed be different. I don't see a problem. If you wish to keep an accurate record, then you could implement an insert trigger to store the most recent serial code in another table.|||here's what you do --

insert a row, and let the database automatically assign the key

use the provided database function to retrieve the value of the key that was assigned

in SQL Server, this is the @.@.identity function, in mysql it is mysql_insert_id(), use whatever function your database provides

if your database does not provide such a function, then simply query back the row that you just inserted, not with MAX but with the values of the other columns

then use the value of the key as the foreign key when you insert the related record

for example, if you insert a username/password combination of 'fred','sesame' into a user table, then you instead of selecting MAX or (shudder!) returning the entire table to see the last one, just run this query:

select userid from users where username='fred' and password='sesame'

the automatically incremented userid value that you get back will be what you use to insert the related row in the other table|||Thanks for your suggestion. The problem is that there r no unique key in the table except the auto serial key code assign by the database. I can not use the second method u suggest. About the first suggestion, it's great, but only if I know what kind of statement the databse provide for me to get the number assign. I'm using Informix. It would be great if you can give some hints on the command to use to get the sequence number. Thanks|||ah, informix

look up NEXTVAL and CURRVAL in the manual

the serial number is actually separate from the table, and you use NEXTVAL to get the next value that you insert into the table|||The process is like this :

1)Insert into table A
2)Get the result set from table A (for the latest serial key code)

What you try to said is that I need to use NEXTVAL before step 1 to get the serial key code I need.

I'm using Java. I need to write SQL statement for NEXTVAL or in the Java Code.

Would be appreciate if you can give any hints. TY.|||Insert into tableA values (seq.nextval, column1, column2)
select * from tableA where id = seq.currVal

REPLACE statement

I am running the following statement but its not updating anything. Whats
wrong.
update baandb.dbo.ttiitm012500
set t_item = REPLACE(t_item,'- get fro','')
where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
ok I found my stupid error. a space in the wrong place.
ghunter wrote:
>I am running the following statement but its not updating anything. Whats
>wrong.
>update baandb.dbo.ttiitm012500
>set t_item = REPLACE(t_item,'- get fro','')
>where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1

REPLACE statement

I am running the following statement but its not updating anything. Whats
wrong.
update baandb.dbo.ttiitm012500
set t_item = REPLACE(t_item,'- get fro','')
where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1ok I found my stupid error. a space in the wrong place.
ghunter wrote:
>I am running the following statement but its not updating anything. Whats
>wrong.
>update baandb.dbo.ttiitm012500
>set t_item = REPLACE(t_item,'- get fro','')
>where t_item like '%get fro%'
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1

REPLACE statement

I am running the following statement but its not updating anything. Whats
wrong.
update baandb.dbo.ttiitm012500
set t_item = REPLACE(t_item,'- get fro','')
where t_item like '%get fro%'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1ok I found my stupid error. a space in the wrong place.
ghunter wrote:
>I am running the following statement but its not updating anything. Whats
>wrong.
>update baandb.dbo.ttiitm012500
>set t_item = REPLACE(t_item,'- get fro','')
>where t_item like '%get fro%'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1

REPLACE NULLS WITH A SELECT STATEMENT (maybe)

I need to create a view to support a report requirement. I need the
returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
will be available no matter what vendor the end user filters on. To do
this I have to populate the flight info in the rows that are non airline
vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
the DAN KNOWLES TOUR rows, etc. How can I do this?
I have provided the below info to help you test. I am using SQL Server
2000.
vu_BAS_SAIR
RESERVATIONIDnumeric9
SEGMENTINDEXsmallint
AIRLINECODEvarchar4
FLIGHTNUMvarchar16
DEPARTAIRPORTvarchar4
vu_BAS_SEGMENT
RESERVATIONIDnumeric9
SEGMENTINDEXsmallint2
VENDORNAMEvarchar64
SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
dbo.vu_BAS_SAIR.AIRLINECODE,
dbo.vu_BAS_SAIR.FLIGHTNUM
FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
dbo.vu_BAS_SEGMENT ON
dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
dbo.vu_BAS_SAIR.SEGMENTINDEX =
dbo.vu_BAS_SEGMENT.SEGMENTINDEX
WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
RESERVATIONIDSEGMENTINDEXVENDORNAMEAIRLINECODEFLIGHTNUM
258231Delta Air LinesDL996
258231Delta Air LinesDL996
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582311Dan Knowles Tours
2582311Dan Knowles Tours
2582312Dan Knowles Tours
2582312Dan Knowles Tours
2582313Atlantis, Paradise Island
2582314Atlantis, Paradise Island
2582315Seahorse Sailing Adventures
2582316Neptunes Water Toys
2582317Nassau Cruises Limited
2582318Document Delivery
2582319Trip Mate Insurance Inc.
2582320Package Booking
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582322Dan Knowles Tours
2582322Dan Knowles Tours
2582322Dan Knowles Tours
2582323Dan Knowles Tours
2582323Dan Knowles Tours
2582323Dan Knowles Tours
2582324Atlantis, Paradise Island
2582325Atlantis, Paradise Island
2582326Seahorse Sailing Adventures
258231Delta Air LinesDL996
258231Delta Air LinesDL996
258232Delta Air LinesDL427
2582327Neptunes Water Toys
2582328Nassau Cruises Limited
2582329Document Delivery
2582330Trip Mate Insurance Inc.
258233Delta Air LinesDL928
258234Delta Air LinesDL302
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258236Delta Air LinesDL427
258237Delta Air LinesDL928
258238Delta Air LinesDL302
258239Package Booking
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
Michael Hardy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Without seeing what your data is going to be deaulted to its a bit had to
give exact code however you should probably have a look at the COALESCE
command
Given the following schema
CREATE TABLE [dbo].[Tester] (
[Part] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PartLink] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
With the following data
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, NULL
'Part4', 4, NULL
The following statement
SELECT Part, ID, COALESCE (PartLink,
(SELECT PartLink
FROM Tester
WHERE ID = 1)) AS PartLink
FROM dbo.Tester
will give
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, 45
'Part4', 4, 34
Anyway have a look at BOL and see if it helps.
"I favor the Civil Rights Act of 1964 and it must be enforced at gunpoint if
necessary."
Ronald Reagan
"Michael Hardy" wrote:

> I need to create a view to support a report requirement. I need the
> returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
> will be available no matter what vendor the end user filters on. To do
> this I have to populate the flight info in the rows that are non airline
> vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
> the DAN KNOWLES TOUR rows, etc. How can I do this?
> I have provided the below info to help you test. I am using SQL Server
> 2000.
> vu_BAS_SAIR
> RESERVATIONIDnumeric9
> SEGMENTINDEXsmallint
> AIRLINECODEvarchar4
> FLIGHTNUMvarchar16
> DEPARTAIRPORTvarchar4
> vu_BAS_SEGMENT
> RESERVATIONIDnumeric9
> SEGMENTINDEXsmallint2
> VENDORNAMEvarchar64
> SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
> dbo.vu_BAS_SAIR.AIRLINECODE,
> dbo.vu_BAS_SAIR.FLIGHTNUM
> FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
> dbo.vu_BAS_SEGMENT ON
> dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
> dbo.vu_BAS_SAIR.SEGMENTINDEX =
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX
> WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
>
> RESERVATIONIDSEGMENTINDEXVENDORNAMEAIRLINECODEFLIGHTNUM
> 258231Delta Air LinesDL996
> 258231Delta Air LinesDL996
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582311Dan Knowles Tours
> 2582311Dan Knowles Tours
> 2582312Dan Knowles Tours
> 2582312Dan Knowles Tours
> 2582313Atlantis, Paradise Island
> 2582314Atlantis, Paradise Island
> 2582315Seahorse Sailing Adventures
> 2582316Neptunes Water Toys
> 2582317Nassau Cruises Limited
> 2582318Document Delivery
> 2582319Trip Mate Insurance Inc.
> 2582320Package Booking
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582322Dan Knowles Tours
> 2582322Dan Knowles Tours
> 2582322Dan Knowles Tours
> 2582323Dan Knowles Tours
> 2582323Dan Knowles Tours
> 2582323Dan Knowles Tours
> 2582324Atlantis, Paradise Island
> 2582325Atlantis, Paradise Island
> 2582326Seahorse Sailing Adventures
> 258231Delta Air LinesDL996
> 258231Delta Air LinesDL996
> 258232Delta Air LinesDL427
> 2582327Neptunes Water Toys
> 2582328Nassau Cruises Limited
> 2582329Document Delivery
> 2582330Trip Mate Insurance Inc.
> 258233Delta Air LinesDL928
> 258234Delta Air LinesDL302
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258236Delta Air LinesDL427
> 258237Delta Air LinesDL928
> 258238Delta Air LinesDL302
> 258239Package Booking
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
>
> Michael Hardy
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
sql

REPLACE NULLS WITH A SELECT STATEMENT (maybe)

I need to create a view to support a report requirement. I need the
returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
will be available no matter what vendor the end user filters on. To do
this I have to populate the flight info in the rows that are non airline
vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
the DAN KNOWLES TOUR rows, etc. How can I do this?
I have provided the below info to help you test. I am using SQL Server
2000.
vu_BAS_SAIR
RESERVATIONID numeric 9
SEGMENTINDEX smallint
AIRLINECODE varchar 4
FLIGHTNUM varchar 16
DEPARTAIRPORT varchar 4
vu_BAS_SEGMENT
RESERVATIONID numeric 9
SEGMENTINDEX smallint 2
VENDORNAME varchar 64
SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
dbo.vu_BAS_SAIR.AIRLINECODE,
dbo.vu_BAS_SAIR.FLIGHTNUM
FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
dbo.vu_BAS_SEGMENT ON
dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
dbo.vu_BAS_SAIR.SEGMENTINDEX =
dbo.vu_BAS_SEGMENT.SEGMENTINDEX
WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
RESERVATIONID SEGMENTINDEX VENDORNAME AI
RLINECODE FLIGHTNUM
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 11 Dan Knowles Tours
25823 11 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 13 Atlantis, Paradise Island
25823 14 Atlantis, Paradise Island
25823 15 Seahorse Sailing Adventures
25823 16 Neptunes Water Toys
25823 17 Nassau Cruises Limited
25823 18 Document Delivery
25823 19 Trip Mate Insurance Inc.
25823 20 Package Booking
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 24 Atlantis, Paradise Island
25823 25 Atlantis, Paradise Island
25823 26 Seahorse Sailing Adventures
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 2 Delta Air Lines DL 427
25823 27 Neptunes Water Toys
25823 28 Nassau Cruises Limited
25823 29 Document Delivery
25823 30 Trip Mate Insurance Inc.
25823 3 Delta Air Lines DL 928
25823 4 Delta Air Lines DL 302
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 6 Delta Air Lines DL 427
25823 7 Delta Air Lines DL 928
25823 8 Delta Air Lines DL 302
25823 9 Package Booking
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
Michael Hardy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Without seeing what your data is going to be deaulted to its a bit had to
give exact code however you should probably have a look at the COALESCE
command
Given the following schema
CREATE TABLE [dbo].[Tester] (
[Part] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PartLink] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
With the following data
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, NULL
'Part4', 4, NULL
The following statement
SELECT Part, ID, COALESCE (PartLink,
(SELECT PartLink
FROM Tester
WHERE ID = 1)) AS PartLink
FROM dbo.Tester
will give
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, 45
'Part4', 4, 34
Anyway have a look at BOL and see if it helps.
"I favor the Civil Rights Act of 1964 and it must be enforced at gunpoint if
necessary."
Ronald Reagan
"Michael Hardy" wrote:

> I need to create a view to support a report requirement. I need the
> returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
> will be available no matter what vendor the end user filters on. To do
> this I have to populate the flight info in the rows that are non airline
> vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
> the DAN KNOWLES TOUR rows, etc. How can I do this?
> I have provided the below info to help you test. I am using SQL Server
> 2000.
> vu_BAS_SAIR
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint
> AIRLINECODE varchar 4
> FLIGHTNUM varchar 16
> DEPARTAIRPORT varchar 4
> vu_BAS_SEGMENT
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint 2
> VENDORNAME varchar 64
> SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
> dbo.vu_BAS_SAIR.AIRLINECODE,
> dbo.vu_BAS_SAIR.FLIGHTNUM
> FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
> dbo.vu_BAS_SEGMENT ON
> dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
> dbo.vu_BAS_SAIR.SEGMENTINDEX =
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX
> WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
>
> RESERVATIONID SEGMENTINDEX VENDORNAME AI
RLINECODE FLIGHTNUM
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 11 Dan Knowles Tours
> 25823 11 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 13 Atlantis, Paradise Island
> 25823 14 Atlantis, Paradise Island
> 25823 15 Seahorse Sailing Adventures
> 25823 16 Neptunes Water Toys
> 25823 17 Nassau Cruises Limited
> 25823 18 Document Delivery
> 25823 19 Trip Mate Insurance Inc.
> 25823 20 Package Booking
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 24 Atlantis, Paradise Island
> 25823 25 Atlantis, Paradise Island
> 25823 26 Seahorse Sailing Adventures
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 2 Delta Air Lines DL 427
> 25823 27 Neptunes Water Toys
> 25823 28 Nassau Cruises Limited
> 25823 29 Document Delivery
> 25823 30 Trip Mate Insurance Inc.
> 25823 3 Delta Air Lines DL 928
> 25823 4 Delta Air Lines DL 302
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 6 Delta Air Lines DL 427
> 25823 7 Delta Air Lines DL 928
> 25823 8 Delta Air Lines DL 302
> 25823 9 Package Booking
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
>
> Michael Hardy
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

REPLACE NULLS WITH A SELECT STATEMENT (maybe)

I need to create a view to support a report requirement. I need the
returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
will be available no matter what vendor the end user filters on. To do
this I have to populate the flight info in the rows that are non airline
vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
the DAN KNOWLES TOUR rows, etc. How can I do this?
I have provided the below info to help you test. I am using SQL Server
2000.
vu_BAS_SAIR
RESERVATIONID numeric 9
SEGMENTINDEX smallint
AIRLINECODE varchar 4
FLIGHTNUM varchar 16
DEPARTAIRPORT varchar 4
vu_BAS_SEGMENT
RESERVATIONID numeric 9
SEGMENTINDEX smallint 2
VENDORNAME varchar 64
SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
dbo.vu_BAS_SAIR.AIRLINECODE,
dbo.vu_BAS_SAIR.FLIGHTNUM
FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
dbo.vu_BAS_SEGMENT ON
dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
dbo.vu_BAS_SAIR.SEGMENTINDEX = dbo.vu_BAS_SEGMENT.SEGMENTINDEX
WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
RESERVATIONID SEGMENTINDEX VENDORNAME AIRLINECODE FLIGHTNUM
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 11 Dan Knowles Tours
25823 11 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 13 Atlantis, Paradise Island
25823 14 Atlantis, Paradise Island
25823 15 Seahorse Sailing Adventures
25823 16 Neptunes Water Toys
25823 17 Nassau Cruises Limited
25823 18 Document Delivery
25823 19 Trip Mate Insurance Inc.
25823 20 Package Booking
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 24 Atlantis, Paradise Island
25823 25 Atlantis, Paradise Island
25823 26 Seahorse Sailing Adventures
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 2 Delta Air Lines DL 427
25823 27 Neptunes Water Toys
25823 28 Nassau Cruises Limited
25823 29 Document Delivery
25823 30 Trip Mate Insurance Inc.
25823 3 Delta Air Lines DL 928
25823 4 Delta Air Lines DL 302
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 6 Delta Air Lines DL 427
25823 7 Delta Air Lines DL 928
25823 8 Delta Air Lines DL 302
25823 9 Package Booking
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
Michael Hardy
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Without seeing what your data is going to be deaulted to its a bit had to
give exact code however you should probably have a look at the COALESCE
command
Given the following schema
CREATE TABLE [dbo].[Tester] (
[Part] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PartLink] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
With the following data
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, NULL
'Part4', 4, NULL
The following statement
SELECT Part, ID, COALESCE (PartLink,
(SELECT PartLink
FROM Tester
WHERE ID = 1)) AS PartLink
FROM dbo.Tester
will give
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, 45
'Part4', 4, 34
Anyway have a look at BOL and see if it helps.
"I favor the Civil Rights Act of 1964 and it must be enforced at gunpoint if
necessary."
Ronald Reagan
"Michael Hardy" wrote:
> I need to create a view to support a report requirement. I need the
> returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
> will be available no matter what vendor the end user filters on. To do
> this I have to populate the flight info in the rows that are non airline
> vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
> the DAN KNOWLES TOUR rows, etc. How can I do this?
> I have provided the below info to help you test. I am using SQL Server
> 2000.
> vu_BAS_SAIR
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint
> AIRLINECODE varchar 4
> FLIGHTNUM varchar 16
> DEPARTAIRPORT varchar 4
> vu_BAS_SEGMENT
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint 2
> VENDORNAME varchar 64
> SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
> dbo.vu_BAS_SAIR.AIRLINECODE,
> dbo.vu_BAS_SAIR.FLIGHTNUM
> FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
> dbo.vu_BAS_SEGMENT ON
> dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
> dbo.vu_BAS_SAIR.SEGMENTINDEX => dbo.vu_BAS_SEGMENT.SEGMENTINDEX
> WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
>
> RESERVATIONID SEGMENTINDEX VENDORNAME AIRLINECODE FLIGHTNUM
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 11 Dan Knowles Tours
> 25823 11 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 13 Atlantis, Paradise Island
> 25823 14 Atlantis, Paradise Island
> 25823 15 Seahorse Sailing Adventures
> 25823 16 Neptunes Water Toys
> 25823 17 Nassau Cruises Limited
> 25823 18 Document Delivery
> 25823 19 Trip Mate Insurance Inc.
> 25823 20 Package Booking
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 24 Atlantis, Paradise Island
> 25823 25 Atlantis, Paradise Island
> 25823 26 Seahorse Sailing Adventures
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 2 Delta Air Lines DL 427
> 25823 27 Neptunes Water Toys
> 25823 28 Nassau Cruises Limited
> 25823 29 Document Delivery
> 25823 30 Trip Mate Insurance Inc.
> 25823 3 Delta Air Lines DL 928
> 25823 4 Delta Air Lines DL 302
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 6 Delta Air Lines DL 427
> 25823 7 Delta Air Lines DL 928
> 25823 8 Delta Air Lines DL 302
> 25823 9 Package Booking
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
>
> Michael Hardy
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>

Replace Multiple Characters

Hi
I need a select statement replace multiple characters from every row
in a column.
I know about replace :
REPLACE ( 'string_expression1' , 'string_expression2' ,
'string_expression3' ) but the question is
how can i do the replace if there are multiple 'string_expression2' ?
For example:
I use replace when i make a select statement in a table like this:
SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
FROM TableName
if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
TTT and ZZZ to be replaced with OOO. how can i modify this select
statement?
Thanks in advance
.Try
SELECT *,
REPLACE(REPLACE(ColumnName, 'XXX', 'TTT'), 'ZZZ','OOO') AS Expr1,
FROM TableName
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1131957553.550242.182530@.g14g2000cwa.googlegroups.com...
> Hi
> I need a select statement replace multiple characters from every row
> in a column.
> I know about replace :
> REPLACE ( 'string_expression1' , 'string_expression2' ,
> 'string_expression3' ) but the question is
> how can i do the replace if there are multiple 'string_expression2' ?
> For example:
> I use replace when i make a select statement in a table like this:
> SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
> FROM TableName
> if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
> TTT and ZZZ to be replaced with OOO. how can i modify this select
> statement?
> Thanks in advance
>
> .
>|||If you know the total number of replacements in advance, you can nest the
REPLACE statements. So, you will write something like REPLACE(REPLACE (...),
..., ...)
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1131957553.550242.182530@.g14g2000cwa.googlegroups.com...
> Hi
> I need a select statement replace multiple characters from every row
> in a column.
> I know about replace :
> REPLACE ( 'string_expression1' , 'string_expression2' ,
> 'string_expression3' ) but the question is
> how can i do the replace if there are multiple 'string_expression2' ?
> For example:
> I use replace when i make a select statement in a table like this:
> SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
> FROM TableName
> if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
> TTT and ZZZ to be replaced with OOO. how can i modify this select
> statement?
> Thanks in advance
>
> .
>|||Thanks!
It works great!sql

Replace in Select Statement??

Hi ;)

I have a select statement where i need to Replace some Chars
Maybe someone can help

SELECT * FROM reguser WHERE REPLACE(" & whereTable & ",'-',')

I want to Replace the"-" and the"/"

Thanks in advanceIt looks like you are concatenating this string from a .NET app (?). If so, is it possible in your scenario to do the replace before you concatenate the qeury together? If not, you are certainly on the right track with the T-SQL REPLACE function. Here's thedoc on that just in case.|||Hi :)

Thanks for the answer ..
The code like it is ..works and replaces the"-" ...but i just wanna add another replacement for the"/"...but i cant find the right way to do it

Thanks|||did you try this:

SELECT * FROM reguser WHERE REPLACE(REPLACE(" & whereTable & ",'-','') ,'/','')|||Thanks a Lot mate ;)

Thats it!!

Cheers

replace IIF statement

I believe I am running into performance issues by using the IIF for filter out zero values in my fact table. My statement that I am using is the following: IIF(Measures.[Test Scores] > 0, Measures.[Test Scores], NULL). Unfortunately this calculated measure is being referenced quite a bit and I believe this is causing major performance issues once the users start to drilldown into the lower levels of the hierarchy.

I orginally tried placing a filter on a named set I was using, but it was not filtering out the measures with a zero value. The statement I was using was the following: FILTER([Course Score Tests], Measures.[Test Scores] > 0) where [Course Score Tests] is a named set that I am referencing. Unfotunately this was not removing the zero test scores when I started to reference this newly filtered named set in one of my calculated measures to try and bypass using the IIF statement. I went back to using the IIF statement on the calculated measure and that is working, but I am see major performance issues and I am currently unable to drilldown to the lowest level of the hierarchy and the memory on the server is taken major hits.

At this point I am at a loss and I am unsure how the rectify the situation. Any advice would be greatly appreciated. I am using SSAS 2005 w/ SP1.

Is [Test Scores] a calculated measure or a real measure ? If it is a calculated measure - what is the expression for it ? If it is a real measure - can you define it as nullable - then you won't need calculated measure to do the conversion.|||Thanks for your reply. [Test Scores] is a real measure, not a calculated measure. I have actual zero values in the fact table that I want to filter out for a particular calculated measure, but not necessarily permanently. Are you refering to changing the NullProcessing on the measure from Automatic to ZeroOrBlank?|||

In that case, you may consider creating a calculated column in DSV which will convert zeros to NULLs, and creating two different measures - one with zeros and another one with NULLs. It would be an overhead during processing, of course, and the cube will be somewhat bigger, but at least it should solve the performance problem.

Replace Apostrophes for Openquery?

Hi All,

I have trigger that sends data from one table to another.
I am using the Openquery statement within the trigger to send the data.

However when I Insert anything with an apostrophe into the table where the trigger resides it errors saying...

"Incorrect syntax near" where the apostrophe is and this puts out the number of Quotes in the Openquery Statement.

I use the Replace function written as REPLACE(@.VARIABLE, '''', ''') to replace One Apostrophe with 2.
I'm not sure whether I can use this with Openquery or not / or whether the syntax is correct for Replace in conjunction with OpenQuery.

If someone can give me anysight into this and any code on how to do this would be great.

Thanks
AnthonyYou are posting on multiple forums :)

Once again EXEC(), QUOTENAME()|||How do I use QUOTENAME() if I want to use a variable that has a value of "O'Reilly" for instance for the following OPENQUERY statement within the trigger

SET @.TSQL = 'INSERT INTO ' +
'OPENQUERY(AUTHTEST,''Select nar_num, ser_key, alp_key from aunrmast'') ' +
'VALUES(0, '''', ''' + @.VARIABLE + ''', 99)'

EXEC(@.TSQL)

Thanks for your help on this

Anthony|||select QUOTENAME('O''Reilly','''')sql

Replace - simple MDX question

I am using the following MDX statement to retrun data to populate a drop dow
n
list allowing the user to select a time period:
WITH
MEMBER [Measures].[DisplayName] AS
'SPACE([Period].CurrentMember.Level.Ordinal * 4) +
[Period].CurrentMember.Name'
MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Unique
Name'
SELECT
{[Measures].[UniqueName], [Measures].[DisplayName]} ON
Columns,
[Period].Members ON Rows
FROM MyCube
The idea of the "SPACE([Period].CurrentMember.Level.Ordinal * 4)" bit in
the
first MEMBER declaration is to provide indentation to give a sense of the
hierarchy in the drop down list display.
I now need to serve a client application that doesn't allow leading spaces
in the drop down list display. Is there a function I can use instead of SPAC
E
to put an alterntive indentation character at the start?
For example, a hyphen. So the returned data would look something like:
ALL
--2004
--Qtr1
--Jan
--Feb
--MarWITH
MEMBER [Measures].[DisplayName] AS
'String([Period].CurrentMember.Level.Ordinal * 4, "-") +
[Period].CurrentMember.Name'
MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Unique
Name'
SELECT
{[Measures].[UniqueName], [Measures].[DisplayName]} ON
Columns,
[Period].Members ON Rows
FROM MyCube
"Dave Morrow" wrote:

> I am using the following MDX statement to retrun data to populate a drop d
own
> list allowing the user to select a time period:
> WITH
> MEMBER [Measures].[DisplayName] AS
> 'SPACE([Period].CurrentMember.Level.Ordinal * 4) +
> [Period].CurrentMember.Name'
> MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Uniq
ueName'
> SELECT
> {[Measures].[UniqueName], [Measures].[DisplayName]} O
N Columns,
> [Period].Members ON Rows
> FROM MyCube
> The idea of the "SPACE([Period].CurrentMember.Level.Ordinal * 4)" bit
in the
> first MEMBER declaration is to provide indentation to give a sense of the
> hierarchy in the drop down list display.
> I now need to serve a client application that doesn't allow leading spaces
> in the drop down list display. Is there a function I can use instead of SP
ACE
> to put an alterntive indentation character at the start?
> For example, a hyphen. So the returned data would look something like:
> ALL
> --2004
> --Qtr1
> --Jan
> --Feb
> --Mar
>

repeating an insert statement

I want a insert statement to repeat X number of times (X will be a variable
passed from a user interface)...any hints on how to code the sql'
Thanks!!!Put it in a stored proc with a WHILE Loop.
CREATE PROC YourProc
@.Loop INT
AS
WHILE @.Loop > 0
BEGIN
INSERT INTO Table VALUES (x)
SET @.Loop = @.Loop - 1
END
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||And I might add that if X happens to be a large number, there may be a
need--for better performance--to control the number of INSERTs you want to
commit in a single transaction. By default, each INSERT commits as a single
transaction, which may not be most efficient if you are doing many
single-INSERT commits in a row.
Linchi
"Gerry M" wrote:
> I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!
What data do you want to insert? Perhaps you want all the integers from 1 to
X, in which case you can use a numbers table to help you:
INSERT INTO SomeTable (z)
SELECT num
FROM numbers
WHERE num BETWEEN 1 AND @.x -- your parameter ;
--
David Portas|||If it's SQL2005, you can do without a numbers auxiliary table. To insert the
same integer X times:
with tmp as (
select 1 as a, 1 as b
union all
select a, b+1 from tmp where b < 100 -- or @.x
)
insert junk(a)
select a from tmp;
To insert integers from 1 to @.x:
with tmp as (
select 1 as a
union all
select a + 1 from tmp where a < 100 -- or @.x
)
insert junk(a)
select a from tmp;
Linchi
"David Portas" wrote:
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
> What data do you want to insert? Perhaps you want all the integers from 1 to
> X, in which case you can use a numbers table to help you:
>
> INSERT INTO SomeTable (z)
> SELECT num
> FROM numbers
> WHERE num BETWEEN 1 AND @.x -- your parameter ;
> --
> David Portas
>
>|||Hi Gerry!
You could try
INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
GO 10;
This will loop through all statements in the batch 10 times. That's assuming
that you want identical copies of the same row to be inserted of course. And
it doesn't work with variables unfortunately, as they get re-declared and
re-assigned in every loop.
Regards,
Jan
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
The "GO n" method will work with SQL Server tools like SSMS or SQLCMD but
not from application code. GO is a batch terminator recognized only by the
SQL Server tools and is not actually sent to the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Van der Eecken" <jkerner@.mweb.co.za> wrote in message
news:OBAl5ZaGIHA.4956@.TK2MSFTNGP06.phx.gbl...
> Hi Gerry!
> You could try
> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
> This will loop through all statements in the batch 10 times. That's
> assuming that you want identical copies of the same row to be inserted of
> course. And it doesn't work with variables unfortunately, as they get
> re-declared and re-assigned in every loop.
> Regards,
> Jan
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>>I want a insert statement to repeat X number of times (X will be a
>>variable
>> passed from a user interface)...any hints on how to code the sql'
>> Thanks!!!
>|||Andrew, how do I pass the variable to the stored proc? Do I use a select
statement to select it?...I want it all to fit into a single job...this is
what I have so far...but it only inserts a single record...
(In this example freeintfield_01 has a value of 100)
Select freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null
create proc sngen
@.freeintfield_01 int
as
while @.freeintfield_01>0
begin
insert into sngenerator (itemcode)
select itemcode
from absences
where type=206 and status=1 and freedatefield_05 is null and
freeintfield_01=@.freeintfield_01
set @.freeintfield_01=@.freeintfield_01-1
end
"Andrew J. Kelly" wrote:
> Put it in a stored proc with a WHILE Loop.
> CREATE PROC YourProc
> @.Loop INT
> AS
> WHILE @.Loop > 0
> BEGIN
> INSERT INTO Table VALUES (x)
> SET @.Loop = @.Loop - 1
> END
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
>|||Are you talking about a SQL Agent job? If so then like this:
DECLARE @.freeintfield_01 int
SET @.freeintfield_01 = (SELECT freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null )
EXEC dbo.sngen @.freeintfield_01
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> Andrew, how do I pass the variable to the stored proc? Do I use a select
> statement to select it?...I want it all to fit into a single job...this
> is
> what I have so far...but it only inserts a single record...
> (In this example freeintfield_01 has a value of 100)
> Select freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null
> create proc sngen
> @.freeintfield_01 int
> as
> while @.freeintfield_01>0
> begin
> insert into sngenerator (itemcode)
> select itemcode
> from absences
> where type=206 and status=1 and freedatefield_05 is null and
> freeintfield_01=@.freeintfield_01
> set @.freeintfield_01=@.freeintfield_01-1
> end
>
> "Andrew J. Kelly" wrote:
>> Put it in a stored proc with a WHILE Loop.
>> CREATE PROC YourProc
>> @.Loop INT
>> AS
>> WHILE @.Loop > 0
>> BEGIN
>> INSERT INTO Table VALUES (x)
>> SET @.Loop = @.Loop - 1
>> END
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >I want a insert statement to repeat X number of times (X will be a
>> >variable
>> > passed from a user interface)...any hints on how to code the sql'
>> >
>> > Thanks!!!
>>|||Yes, the loop works fine but it does not insert the data (itemcode)100 times,
just once....?
"Andrew J. Kelly" wrote:
> Are you talking about a SQL Agent job? If so then like this:
> DECLARE @.freeintfield_01 int
> SET @.freeintfield_01 = (SELECT freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null )
> EXEC dbo.sngen @.freeintfield_01
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> > Andrew, how do I pass the variable to the stored proc? Do I use a select
> > statement to select it?...I want it all to fit into a single job...this
> > is
> > what I have so far...but it only inserts a single record...
> > (In this example freeintfield_01 has a value of 100)
> >
> > Select freeintfield_01
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null
> >
> > create proc sngen
> > @.freeintfield_01 int
> > as
> > while @.freeintfield_01>0
> >
> > begin
> >
> > insert into sngenerator (itemcode)
> > select itemcode
> >
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null and
> > freeintfield_01=@.freeintfield_01
> >
> > set @.freeintfield_01=@.freeintfield_01-1
> > end
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Put it in a stored proc with a WHILE Loop.
> >>
> >> CREATE PROC YourProc
> >> @.Loop INT
> >>
> >> AS
> >>
> >> WHILE @.Loop > 0
> >> BEGIN
> >>
> >> INSERT INTO Table VALUES (x)
> >>
> >> SET @.Loop = @.Loop - 1
> >> END
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >I want a insert statement to repeat X number of times (X will be a
> >> >variable
> >> > passed from a user interface)...any hints on how to code the sql'
> >> >
> >> > Thanks!!!
> >>
> >>
>|||If the loop is executing 100 times and the data is not being inserted you
must have an issue with the select statement.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> Yes, the loop works fine but it does not insert the data (itemcode)100
> times,
> just once....?
> "Andrew J. Kelly" wrote:
>> Are you talking about a SQL Agent job? If so then like this:
>> DECLARE @.freeintfield_01 int
>> SET @.freeintfield_01 = (SELECT freeintfield_01
>> from absences
>> where type=206 and status=1 and freedatefield_05 is null )
>> EXEC dbo.sngen @.freeintfield_01
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
>> > Andrew, how do I pass the variable to the stored proc? Do I use a
>> > select
>> > statement to select it?...I want it all to fit into a single
>> > job...this
>> > is
>> > what I have so far...but it only inserts a single record...
>> > (In this example freeintfield_01 has a value of 100)
>> >
>> > Select freeintfield_01
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null
>> >
>> > create proc sngen
>> > @.freeintfield_01 int
>> > as
>> > while @.freeintfield_01>0
>> >
>> > begin
>> >
>> > insert into sngenerator (itemcode)
>> > select itemcode
>> >
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null and
>> > freeintfield_01=@.freeintfield_01
>> >
>> > set @.freeintfield_01=@.freeintfield_01-1
>> > end
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Put it in a stored proc with a WHILE Loop.
>> >>
>> >> CREATE PROC YourProc
>> >> @.Loop INT
>> >>
>> >> AS
>> >>
>> >> WHILE @.Loop > 0
>> >> BEGIN
>> >>
>> >> INSERT INTO Table VALUES (x)
>> >>
>> >> SET @.Loop = @.Loop - 1
>> >> END
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >> >I want a insert statement to repeat X number of times (X will be a
>> >> >variable
>> >> > passed from a user interface)...any hints on how to code the sql'
>> >> >
>> >> > Thanks!!!
>> >>
>> >>
>>|||Yes, I found it...thanks for your help!
Gerry
"Andrew J. Kelly" wrote:
> If the loop is executing 100 times and the data is not being inserted you
> must have an issue with the select statement.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> > Yes, the loop works fine but it does not insert the data (itemcode)100
> > times,
> > just once....?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you talking about a SQL Agent job? If so then like this:
> >>
> >> DECLARE @.freeintfield_01 int
> >>
> >> SET @.freeintfield_01 = (SELECT freeintfield_01
> >> from absences
> >> where type=206 and status=1 and freedatefield_05 is null )
> >>
> >> EXEC dbo.sngen @.freeintfield_01
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> >> > Andrew, how do I pass the variable to the stored proc? Do I use a
> >> > select
> >> > statement to select it?...I want it all to fit into a single
> >> > job...this
> >> > is
> >> > what I have so far...but it only inserts a single record...
> >> > (In this example freeintfield_01 has a value of 100)
> >> >
> >> > Select freeintfield_01
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null
> >> >
> >> > create proc sngen
> >> > @.freeintfield_01 int
> >> > as
> >> > while @.freeintfield_01>0
> >> >
> >> > begin
> >> >
> >> > insert into sngenerator (itemcode)
> >> > select itemcode
> >> >
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null and
> >> > freeintfield_01=@.freeintfield_01
> >> >
> >> > set @.freeintfield_01=@.freeintfield_01-1
> >> > end
> >> >
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Put it in a stored proc with a WHILE Loop.
> >> >>
> >> >> CREATE PROC YourProc
> >> >> @.Loop INT
> >> >>
> >> >> AS
> >> >>
> >> >> WHILE @.Loop > 0
> >> >> BEGIN
> >> >>
> >> >> INSERT INTO Table VALUES (x)
> >> >>
> >> >> SET @.Loop = @.Loop - 1
> >> >> END
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> Solid Quality Mentors
> >> >>
> >> >>
> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >> >I want a insert statement to repeat X number of times (X will be a
> >> >> >variable
> >> >> > passed from a user interface)...any hints on how to code the sql'
> >> >> >
> >> >> > Thanks!!!
> >> >>
> >> >>
> >>
> >>
>

repeat a statement in a loop

hello,
i need to repeat the update statement for all days in the actual month
example:if i run today (19.09.2005) the job - it must run 19 times for
the value of
GETDATE() to GetDate() -19. How can be done this in a loop.
In the update statement is GetDate() used in the where condition and it must
be replaced with the values
in the first loop with GetDate() -1
in the second loop with GetDate() - 2
...
in the last GetDate()-19
The update statement looks like :
UPDATE [table1]
SET [OrderDate]=(
SELECT DISTINCT Top 1 OrderCreationDate
FROM table2
WHERE (SalesDocNr = table1.OrderNr)
)
WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
FROM table1 INNER JOIN
table2 ON table1.OrderNr = table2.SalesDocNr
WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
CONVERT(VarChar(10), GETDATE() , 104))
)
thanks
XavierWhile can use a WHILE loop or a cursor to get this done as you want, a
better approach is to use a single UPDATE statement which can update all the
rows.
Based on the sample code you posted, it is hard to work out such a solution,
so please refer to www.aspfaq.com/5006 and post relevant information for
others to reproduce your problem.
Anith|||For a looped approach try this for your basic loop. @.date should be used in
place of GETDATE() in your update query. Replace the PRINT statement with
your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
ONLY. You may want a better method than what I did.
Mike
DECLARE @.day int
DECLARE @.date datetime
SET @.day = DAY(GETDATE())
SET @.date = GETDATE()
SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
WHILE @.day > 0
BEGIN
PRINT @.date
SET @.day = @.day - 1
SET @.date = DATEADD(d, -1, @.date)
END
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it
> must
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||Please post DDL (CREATE TABLE), sample data (INSERTs) and show your required
results.
I'm certain it's possible to do what you want in a single UPDATE statement
without a loop. However, the UPDATE you have posted may not give reliable
results because you've used TOP without ORDER BY. For that reason it's trick
y
to guess what you intended by it (even though it may not always work as you
wanted).
I expect the solution will look like
:
UPDATE Table1
SET orderdate =
(
..
)
WHERE insertdate >= DATEADD(DAY,-19,CURRENT_TIMESTAMP)
AND insertdate <= CURRENT_TIMESTAMP ;
David Portas
SQL Server MVP
--
"Xavier" wrote:

> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it mu
st
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||it works,
thanks Mike
"Mike Jansen" wrote:

> For a looped approach try this for your basic loop. @.date should be used
in
> place of GETDATE() in your update query. Replace the PRINT statement with
> your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
> ONLY. You may want a better method than what I did.
> Mike
>
> DECLARE @.day int
> DECLARE @.date datetime
>
> SET @.day = DAY(GETDATE())
> SET @.date = GETDATE()
> SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
> WHILE @.day > 0
> BEGIN
> PRINT @.date
> SET @.day = @.day - 1
> SET @.date = DATEADD(d, -1, @.date)
> END
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
>
>|||> While can use a WHILE loop or a cursor to get this done as you want, a
> better approach is to use a single UPDATE statement which can update all
> the rows.
I don't think that's really possible without SQL Server 2005 (or perhaps a
_really_ messed up query in SQL 2000), in which case it would be something
like this as a base:
WITH DAYS(DayValue, Remaining) AS
(
SELECT
CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime) AS DayValue,
DAY(GETDATE()) - 1
UNION ALL
SELECT
DATEADD(d, -1, r.DayValue) AS DayValue,
r.Remaining - 1
FROM
DAYS r
WHERE
r.Remaining > 0
)
SELECT * FROM DAYS;
Instead of SELECT * FROM DAYS you'd do an UPDATE and JOIN to DAYS.
Mike

repair_allow_data_loss Repair statement not processed. Database needs to be in single user

I have to repair a database with repair_allow_data_loss level, so I start
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>

repair_allow_data_loss Repair statement not processed. Database needs to be in single

I have to repair a database with repair_allow_data_loss level, so I start
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>

Re-ordering Table Fields

When I use the ALTER TABLE statement to Add new fields to a table, is there a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!http://vyaskn.tripod.com/administration_faq.htm#q11
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!|||"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
> When I use the ALTER TABLE statement to Add new fields to a table, is
> there a
> way to specify it's position on the table? I don't want it to be in the
> end
> (last position) of the fields...
> In Enterprise Manager, it does this by making a copy of the table with new
> structure, droping the old and renaming the new... This is no god when I
> add
> fields to a table with 1 million regs, in production environment... But I
> also want to keep the structure the same as the development environment...
> Does anyone knows how can I do that simply with T-SQL commands?
> Thanks!
Is there any particular reason why the column *needs* to be in a different
location? SQL Server internally will move the data around in it's storage
scheme depending on what datatype the column has.
You can use EM to do this, but it basically copies the data to a temp table,
drops the original table, recreates the original table and then copies the
data back in.
Rick Sawtell
MCT, MCSD, MCDBA

Re-ordering Table Fields

When I use the ALTER TABLE statement to Add new fields to a table, is there a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!
http://vyaskn.tripod.com/administration_faq.htm#q11
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!
|||"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
> When I use the ALTER TABLE statement to Add new fields to a table, is
> there a
> way to specify it's position on the table? I don't want it to be in the
> end
> (last position) of the fields...
> In Enterprise Manager, it does this by making a copy of the table with new
> structure, droping the old and renaming the new... This is no god when I
> add
> fields to a table with 1 million regs, in production environment... But I
> also want to keep the structure the same as the development environment...
> Does anyone knows how can I do that simply with T-SQL commands?
> Thanks!
Is there any particular reason why the column *needs* to be in a different
location? SQL Server internally will move the data around in it's storage
scheme depending on what datatype the column has.
You can use EM to do this, but it basically copies the data to a temp table,
drops the original table, recreates the original table and then copies the
data back in.
Rick Sawtell
MCT, MCSD, MCDBA

Re-ordering Table Fields

When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!http://vyaskn.tripod.com/administration_faq.htm#q11
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
When I use the ALTER TABLE statement to Add new fields to a table, is there
a
way to specify it's position on the table? I don't want it to be in the end
(last position) of the fields...
In Enterprise Manager, it does this by making a copy of the table with new
structure, droping the old and renaming the new... This is no god when I add
fields to a table with 1 million regs, in production environment... But I
also want to keep the structure the same as the development environment...
Does anyone knows how can I do that simply with T-SQL commands?
Thanks!|||"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:3BC02B7C-2B8A-4A3A-9D3C-ABC7134A9F8D@.microsoft.com...
> When I use the ALTER TABLE statement to Add new fields to a table, is
> there a
> way to specify it's position on the table? I don't want it to be in the
> end
> (last position) of the fields...
> In Enterprise Manager, it does this by making a copy of the table with new
> structure, droping the old and renaming the new... This is no god when I
> add
> fields to a table with 1 million regs, in production environment... But I
> also want to keep the structure the same as the development environment...
> Does anyone knows how can I do that simply with T-SQL commands?
> Thanks!
Is there any particular reason why the column *needs* to be in a different
location? SQL Server internally will move the data around in it's storage
scheme depending on what datatype the column has.
You can use EM to do this, but it basically copies the data to a temp table,
drops the original table, recreates the original table and then copies the
data back in.
Rick Sawtell
MCT, MCSD, MCDBA