i write a store procedure and it include
a lot of
insert into table1(col1,col2..)
select clo1,col2.. from table2
and i want get the @.@.error and @.@.rowcount
but if i write set @.myErr=@.@.error the @.@.rowcount will
turn to 1
and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
will ture into the error of 'set @.myRowcount=@.@.rowcount'
how can i get then both?thanksTry: SELECT @.myErr = @.@.ERROR, @.myRowCount = @.@.ROWCOUN T
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>|||Hi Frank
This was already answered in another newsgroup. Please do not post the same
question independently to multiple groups, so someone doesn't waste time
answering something that has already been answered.
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>
Showing posts with label table1. Show all posts
Showing posts with label table1. Show all posts
Saturday, February 25, 2012
about store procedure
i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you
Monday, February 13, 2012
About Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting tableAbout Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting tableAbout Group BY Function in sql 2005
Hello
My Name Is Ramesh,
I Have Problem in Using the Group by function. My Requriment is ,i have table1 with name tTitlerates and table 2 with name tQuotation in tQuotation table i have field name called titleId , titlerate and Number of Copies. so i want add all the copies that falling into corresponding titleId and total amount for that total copies . but the Resulting table must contains TitleId, TotalNumberofcopies for that title, titleRate and Total rate Fields .
i have Used below query to get the Result , but by this query i can only display only 2 Fields,
SELECT qi.TitleID , SUM(qI.Copies)as NoofCopies FROM tQuotation q, tQuotationItem qi,tTitleRate tr
WHERE qi.QuotationNo = q.QuotationNo
AND qi.TitleID = tr.TitleId group by qi.TitleID
I need Query for my requriment
Please Repaly me as soon as possiable
Regards
Ramesh
Ramesh:
I started to work on this, but I am afraid that I am still not able to visualize it. Can you provide (1) a short sample input set to the query and (2) the expected output from this input?
|||Hello Kent WaldropI got the result for my last query
I have New query Now .
I have written One Stored procedure , just go through it once
ALTER PROCEDURE bestprice
AS
BEGIN
CREATE TABLE #tmp(QuotationNo INT,CombTitleID INT , TitleId INT, PublisherId INT
)
CREATE TABLE #tmp1(CombTitleID INT ,TitleId INT)
--Local Variables
DECLARE @.Title_id INT
DECLARE @.Publisher_id INT
DECLARE @.message varchar(80)
DECLARE @.Publisher_Name VARCHAR(20)
DECLARE @.Status INT
DECLARE @.Top INT
SET @.Top = 1
DECLARE @.Title_Id1 INT
DECLARE @.Loopcounter1 INT
SET @.Loopcounter1 = 0
DECLARE @.Loopcounter2 INT
SET @.Loopcounter2 = 0
DECLARE @.Counter INT
DECLARE @.QuotationNo INT
DECLARE @.CombTitleID INT
DECLARE @.CombTitleID1 INT
-- Declare Cursor for quotation
INSERT INTO #tmp SELECT q.QuotationNo,null, qi.TitleId, tm.PublisherId
FROM tQuotation q, tQuotationItem qi ,tTitleMaster tm
WHERE q.Status = 1
AND q.QuotationNo = qi.QuotationNo
AND qi.TitleId = tm.TitleId
WHILE(@.Loopcounter1 < (SELECT Count(*) From tTitleComb))
BEGIN
INSERT INTO #Tmp1 SELECT CombTitleID , TitleId FROM tTitleCombList
where CombTitleID = (SELECT CombTitleID FROM tTitleComb a where @.Top = (select Count (*) from tTitleComb b where a.CombTitleID >= b.CombTitleID ))
PRINT 'Top = ' + convert(varchar(20), @.Top)
SET @.Counter = 0
SET @.Loopcounter2 = 0
--statrt Of Inner while Loop
-- Declare Cursor for CombtitleId
DECLARE Inner_Cursor CURSOR FOR
SELECT TitleId ,CombTitleID From #Tmp1
OPEN Inner_Cursor
-- Fetch values from cursor into local variables
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
-- Begin Loop
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
DECLARE best_Quotation CURSOR FOR
SELECT QuotationNo,CombTitleID,TitleId ,PublisherId FROM #tmp
OPEN best_Quotation
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1 ,@.Title_Id, @.Publisher_id
begin transaction Dummy1
--Inner loop to loop through the Title Id in #tmp table for each comb Title Id in #tmp1
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Counter = 0
IF( @.Title_Id1 = @.Title_Id )
BEGIN
SET @.Counter = 1
SET @.Loopcounter2 = @.Loopcounter2 + 1
END
IF(@.Counter = 1)
BEGIN
UPDATE #tmp SET CombTitleID = @.CombTitleID WHERE QuotationNo = @.QuotationNo AND TitleId = @.Title_Id
print 'CombTitleID = ' + convert(varchar(20),@.CombTitleID) + 'QuotationNo = ' + convert(varchar(20),@.QuotationNo) + 'TitleId = ' + convert(varchar(20),@.Title_Id)
BREAK
END
else
BEGIN
FETCH NEXT FROM best_Quotation
INTO @.QuotationNo,@.CombTitleID1,@.Title_Id, @.Publisher_id
END
END
CLOSE best_Quotation
DEALLOCATE best_Quotation
--End OF Inner Loop
FETCH NEXT FROM Inner_Cursor
INTO @.Title_Id1 ,@.CombTitleID
IF( @.Loopcounter2 = (SELECT Count(*) FROM #Tmp1))
BEGIN
COMMIT
Print 'LoopCounter = ' + convert(varchar(20), @.Loopcounter2)
Print 'EXISTS IN COMBTITLE'
END
else
ROLLBACK
--End OF External Loop
END
CLOSE Inner_Cursor
DEALLOCATE Inner_Cursor
--End Of Inner While Loop
set @.Top = @.Top + 1
set @.Loopcounter1 = @.Loopcounter1 + 1
TRUNCATE TABLE #Tmp1
END
DROP TABLE #Tmp1
SELECT * FROM #tmp
END
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 1
1 2
1 7
2 2
2 8
2 9
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
2 gti bookPackage
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 1
1 2
1 8
2 2
2 7
2 9
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
1 1 1
1 1 2
2 1 8
2 2 2
1 2 7
2 2 9
please replay me as soon as possiable
Regards
Ramesh
|||Maybe something like this:
Code Snippet
declare @.tQuotation table
( QuotationNo integer,
[Desc] varchar(15)
)
insert into @.tQuotation
select 1, 'By ITI College' union all
select 2, 'By Gti College'
--select * from @.tQuotation
declare @.tQuotationItem table
( QuotationNo integer,
TitleId integer
)
insert into @.tQuotationItem
select 1, 1 union all
select 1, 2 union all
select 1, 7 union all
select 2, 2 union all
select 2, 8 union all
select 2, 9 union all
select * from @.tQuotationItem
declare @.titleCombList table
( combTitleId integer,
TitleId integer
)
insert into @.titleCombList
select 1, 1 union all
select 1, 2 union all
select 1, 8 union all
select 2, 2 union all
select 2, 7 union all
select 2, 9
--select * from @.titleCombList
select min(b.quotationNo) as min_quotationNo,
a.CombTitleId,
a.TitleId
from @.titleCombList a
join @.tQuotationItem b
on a.titleId = b.titleID
group by a.combTitleId,
a.titleId
order by a.combTitleId, a.TitleId
/*
min_quotationNo CombTitleId TitleId
-- --
1 1 1
1 1 2
2 1 8
1 2 2
1 2 7
2 2 9
*/
Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
By using all These table I want My result table Like
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
|||Hello
Thank You for Your Valueable Information.Your Query is working Perfectly fine it is almost matching my requrement.
Their is one problem , if all the combination exists means then only it has to update my resulting table with Combtitle id.
What i am trying to do is i have table name called tQuotationItem contatining the fields called
QutationNo(pk,fk) ,titleId(pk,fk) (QuotationNo is composite primary key For tQuotationItem and it is primary key for tQuotationtable which is having the fields like QuotationNo (Primary key) and Description(varchar(20) .titleId is composite primary key for tQuotationItem and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
I also hve table called tTitlecombList
CombTitleId(pk,fk) ,titleId(pk,fk) (CombTitleId is composite primary key For tTitlecombList and it is primary key for tTitlecomb table which is having the fields like CombTitleId (Primary key) and Description(varchar(20) .titleId is composite primary key for tTitlecombList and it is primary key for tTitlemaster which contain fields like Title id (pk)and Description (varchar)).
lNow my actual query comes
lNow my actual query comes
tQuotation
QutationNo(pk) Desc(Varchar)
1 By ITI College
2 By Gti College
3 By gmit college
4 by rec College
tQuotationItem
QutationNo(pk,fk) titleId(pk,fk)
1 2
1 3
1 7
1 8
2 2
2 47
2 55
3 2
3 3
Ttitlecomb
combtitleId(pk) Desc(varcahr)
1 ITi Books package
3 gti bookPackage
4 Var1 package
6 Var2 package
7 Var3package
8 Var4 package
TtitlecombList
combtitleId(pk,Fk) TiTleId(pk,fk)
1 2
1 3
3 2
3 8
4 6
4 10
7 2
7 47
7 55
8 2
8 5
Your query is resulting below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 8 2
But In This table th last row contains combid = 8 and titleid = 2 it doesnt has to come because
combid = 8 and titleid = 2
combid = 8 and titleid = 5
is a package. i cnt able to consider it as package since the 5 titleid is not exist in the quotation table.
my requriment is like this, if hole particular package of combination is exist in quotation then only it has to update in to resulting table .if any one comination of particular combid doent match means it doent insert that TItleid in to my resulting table
i want my resulting table below table
QuotationNo CombTitleId TitleId
3 1 2
3 1 3
3 3 2
1 3 8
3 6 2
1 6 7
3 7 2
2 7 47
2 7 55
3 Null 2
Because the if one comination in the quotation table is not exists means we cant update that particular combid in to resulting tableThursday, February 9, 2012
About automatic process at midnight
Hi
I have a table1 which hold a field (amount) with 1000 rows
The amount in the 1000 rows is consumed by clients during the day
The next day the amounts have to return to their original values (at this
time I’m doing that by hand)
Now I’m going to save the original values in another table
My question is: Can the SQL server copy the original values from table1 to
table2 at midnight or at a time I schedule?
Thks again.Yes. Create a SQL Agent job. Make sure the SQLSERVERAGENT service is set t
o
Automatic.
"Kenny M." wrote:
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I’m doing that by hand)
> Now I’m going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>|||Insert them into a new table
1.INSERT INTO NewTableName
Select * From OldTableName
2.Update the Working table (at night after the work) with a update statement
referencing the ID column of the table
BEGIN Transaction
DELETE FROM Oldtable where ID IN (Select IDCOLUMN From NewTable)
INSERT INTO Oldtable SELECT * from NewTable
IF @.@.ERROR = 0
COMMIT
ELSE
ROLLBACK
Is it that what you ment ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kenny M." <KennyM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D19D255D-E863-4E71-8E53-2145187103A7@.microsoft.com...
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I'm doing that by hand)
> Now I'm going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>
I have a table1 which hold a field (amount) with 1000 rows
The amount in the 1000 rows is consumed by clients during the day
The next day the amounts have to return to their original values (at this
time I’m doing that by hand)
Now I’m going to save the original values in another table
My question is: Can the SQL server copy the original values from table1 to
table2 at midnight or at a time I schedule?
Thks again.Yes. Create a SQL Agent job. Make sure the SQLSERVERAGENT service is set t
o
Automatic.
"Kenny M." wrote:
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I’m doing that by hand)
> Now I’m going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>|||Insert them into a new table
1.INSERT INTO NewTableName
Select * From OldTableName
2.Update the Working table (at night after the work) with a update statement
referencing the ID column of the table
BEGIN Transaction
DELETE FROM Oldtable where ID IN (Select IDCOLUMN From NewTable)
INSERT INTO Oldtable SELECT * from NewTable
IF @.@.ERROR = 0
COMMIT
ELSE
ROLLBACK
Is it that what you ment ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kenny M." <KennyM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D19D255D-E863-4E71-8E53-2145187103A7@.microsoft.com...
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I'm doing that by hand)
> Now I'm going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>
Subscribe to:
Posts (Atom)