create a procedure

Hello everyone, i need some help(((
i am creating a procedure as this :
CREATE procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
close od;
deallocate od;
everything is ok, the command completed successfuly.
1-but when i run the procedure by doing :
exec automated_mark
the procedure run, but there is no update.
2- when i open the microsoftsql management studio, and i do right-
click on the percedure,, execute Stored procedure : then the procedure
run, and tha table is updated. BUT when i alter my procedure, and i
change the "update products set Instock= '10000' " with the "update
products set Instock= '2222' ", and i do right-click on the
percedure,, execute Stored procedure THEN the procedure run, BUT NO
UPDATE.
why and how to solve the problem?
thank for your help.
@.@.fetch_status is a global value for each connection. So you cannot depend
on @.@.fetch_status being zero when the stored proc is executed. If you have
previously looped thru a cursor with this connection, doing a
while(@.@.fetch_status=0), then when it left that loop, it was non zero, so it
is still non zero and you loop will not be executed at all. Instead of
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
you want to code the cursor loop like:
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
update products set Instock= '10000' where prodid = @.nom1;
fetch next from od into @.nom1,@.nom2
end
Then you are not depending on @.@.fetch_status being zero before your first
fetch.
The other problem with this stored proc is that it uses a cursor. Cursor's
are almost always an ineffecient way to process data in SQL. It is almost
always much better and faster to use a set based solution. In your case, a
better stored proc would look like:
CREATE procedure [dbo].[automated_mark]
with recompile
as
update products
set Instock='10000'
where prodid in (select ProdID from Orders where Orders.Qty > 20);
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:13ae24ed-37ea-4dbb-91f5-d278bb4f6b77@.d62g2000hsf.googlegroups.com...
> Hello everyone, i need some help(((
> i am creating a procedure as this :
> --
> CREATE procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> while (@.@.fetch_status=0)
> begin
> fetch next from od into @.nom1,@.nom2
> update products set Instock= '10000' where prodid = @.nom1;
> end
> close od;
> deallocate od;
> --
> everything is ok, the command completed successfuly.
> 1-but when i run the procedure by doing :
> --
> exec automated_mark
> --
> the procedure run, but there is no update.
> 2- when i open the microsoftsql management studio, and i do right-
> click on the percedure,, execute Stored procedure : then the procedure
> run, and tha table is updated. BUT when i alter my procedure, and i
> change the "update products set Instock= '10000' " with the "update
> products set Instock= '2222' ", and i do right-click on the
> percedure,, execute Stored procedure THEN the procedure run, BUT NO
> UPDATE.
> why and how to solve the problem?
> thank for your help.
|||thank you very much Tom.
its now working.thanks.
Now, about the set based solution, i agree with you when i have an
update to do.
But, what if instead an update i have an insert to do, like this :
ALTER procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
fetch next from od into @.nom1,@.nom2
end
close od;
deallocate od;
is there a better way to do that?
thanks again.
|||On Feb 29, 4:14Xam, noumian <n.nou...@.gmail.com> wrote:
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> X X X X for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> X X X X fetch next from od into @.nom1,@.nom2
> X X X X while (@.@.fetch_status=0)
> X X X X X X X X begin
> X X X X X X X X X X X X insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> X X X X X X X X X X X X fetch next from od into @.nom1,@.nom2
> X X X X X X X X end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
What about:
INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
dbo.Orders WHERE Orders.Quantity > 20
|||insert into sales (prodid,qty,action)
select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> fetch next from od into @.nom1,@.nom2
> while (@.@.fetch_status=0)
> begin
> insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> fetch next from od into @.nom1,@.nom2
> end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
|||On Feb 29, 4:39 pm, Sean <ColdFusion...@.gmail.com> wrote:
> On Feb 29, 4:14 am, noumian <n.nou...@.gmail.com> wrote:
>
>
>
> What about:
> INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
> dbo.Orders WHERE Orders.Quantity > 20
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?
|||On Feb 29, 5:56 pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:[vbcol=seagreen]
> insert into sales (prodid,qty,action)
> select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
> Tom
> "noumian" <n.nou...@.gmail.com> wrote in message
> news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
>
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?
|||On Feb 29, 10:22Xam, noumian <n.nou...@.gmail.com> wrote:
> On Feb 29, 5:56 pm, "Tom Cooper"
>
>
> <tomcoo...@.comcast.no.spam.please.net> wrote:
>
>
>
> CHECK MY insert: X insert into sales (prodid,qty,action) values
> (@.nom1,@.nom2,'sold');
> what about the values of my column action... Hide quoted text -
> - Show quoted text -
Argh, sorry it's friday... a very long... friday.
INSERT INTO Sales(prodid, qty, action) SELECT prodid, qty, 'Sold' as
Action FROM Orders WHERE qty > 20
That should do the trick.
|||ok, thanks very much.

create a procedure

Hello everyone, i need some help(((
i am creating a procedure as this :
--
CREATE procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
close od;
deallocate od;
--
everything is ok, the command completed successfuly.
1-but when i run the procedure by doing :
--
exec automated_mark
--
the procedure run, but there is no update.
2- when i open the microsoftsql management studio, and i do right-
click on the percedure,, execute Stored procedure : then the procedure
run, and tha table is updated. BUT when i alter my procedure, and i
change the "update products set Instock= '10000' " with the "update
products set Instock= '2222' ", and i do right-click on the
percedure,, execute Stored procedure THEN the procedure run, BUT NO
UPDATE.
why and how to solve the problem?
thank for your help.@.@.fetch_status is a global value for each connection. So you cannot depend
on @.@.fetch_status being zero when the stored proc is executed. If you have
previously looped thru a cursor with this connection, doing a
while(@.@.fetch_status=0), then when it left that loop, it was non zero, so it
is still non zero and you loop will not be executed at all. Instead of
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
you want to code the cursor loop like:
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
update products set Instock= '10000' where prodid = @.nom1;
fetch next from od into @.nom1,@.nom2
end
Then you are not depending on @.@.fetch_status being zero before your first
fetch.
The other problem with this stored proc is that it uses a cursor. Cursor's
are almost always an ineffecient way to process data in SQL. It is almost
always much better and faster to use a set based solution. In your case, a
better stored proc would look like:
CREATE procedure [dbo].[automated_mark]
with recompile
as
update products
set Instock='10000'
where prodid in (select ProdID from Orders where Orders.Qty > 20);
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:13ae24ed-37ea-4dbb-91f5-d278bb4f6b77@.d62g2000hsf.googlegroups.com...
> Hello everyone, i need some help(((
> i am creating a procedure as this :
> --
> CREATE procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> while (@.@.fetch_status=0)
> begin
> fetch next from od into @.nom1,@.nom2
> update products set Instock= '10000' where prodid = @.nom1;
> end
> close od;
> deallocate od;
> --
> everything is ok, the command completed successfuly.
> 1-but when i run the procedure by doing :
> --
> exec automated_mark
> --
> the procedure run, but there is no update.
> 2- when i open the microsoftsql management studio, and i do right-
> click on the percedure,, execute Stored procedure : then the procedure
> run, and tha table is updated. BUT when i alter my procedure, and i
> change the "update products set Instock= '10000' " with the "update
> products set Instock= '2222' ", and i do right-click on the
> percedure,, execute Stored procedure THEN the procedure run, BUT NO
> UPDATE.
> why and how to solve the problem?
> thank for your help.|||thank you very much Tom.
its now working.thanks.
Now, about the set based solution, i agree with you when i have an
update to do.
But, what if instead an update i have an insert to do, like this :
--
ALTER procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
fetch next from od into @.nom1,@.nom2
end
close od;
deallocate od;
--
is there a better way to do that?
thanks again.|||On Feb 29, 4:14=A0am, noumian <n.nou...@.gmail.com> wrote:
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> =A0 =A0 =A0 =A0 for select Orders.ProdID,Orders.Qty from Orders where Orde=rs.Qty > 20
> open od
> =A0 =A0 =A0 =A0 fetch next from od into @.nom1,@.nom2
> =A0 =A0 =A0 =A0 while (@.@.fetch_status=3D0)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 begin
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 insert into sales (prodid,=qty,action) values (@.nom1,@.nom2,'sold');
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 fetch next from od into @.n=om1,@.nom2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
What about:
INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
dbo.Orders WHERE Orders.Quantity > 20|||insert into sales (prodid,qty,action)
select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> fetch next from od into @.nom1,@.nom2
> while (@.@.fetch_status=0)
> begin
> insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> fetch next from od into @.nom1,@.nom2
> end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.|||On Feb 29, 4:39 pm, Sean <ColdFusion...@.gmail.com> wrote:
> On Feb 29, 4:14 am, noumian <n.nou...@.gmail.com> wrote:
>
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
> What about:
> INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
> dbo.Orders WHERE Orders.Quantity > 20
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 5:56 pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:
> insert into sales (prodid,qty,action)
> select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
> Tom
> "noumian" <n.nou...@.gmail.com> wrote in message
> news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 10:22=A0am, noumian <n.nou...@.gmail.com> wrote:
> On Feb 29, 5:56 pm, "Tom Cooper"
>
>
> <tomcoo...@.comcast.no.spam.please.net> wrote:
> > insert into sales (prodid,qty,action)
> > select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 2=0;
> > Tom
> > "noumian" <n.nou...@.gmail.com> wrote in message
> >news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...=
> > > thank you very much Tom.
> > > its now working.thanks.
> > > Now, about the set based solution, i agree with you when i have an
> > > update to do.
> > > But, what if instead an update i have an insert to do, like this :
> > > --
> > > ALTER procedure [dbo].[automated_mark]
> > > with recompile
> > > as
> > > declare @.nom1 nvarchar(50)
> > > declare @.nom2 nvarchar(50)
> > > declare od cursor
> > > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > > open od
> > > fetch next from od into @.nom1,@.nom2
> > > while (@.@.fetch_status=3D0)
> > > begin
> > > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > > fetch next from od into @.nom1,@.nom2
> > > end
> > > close od;
> > > deallocate od;
> > > --
> > > is there a better way to do that?
> > > thanks again.
> CHECK MY insert: =A0 insert into sales (prodid,qty,action) values
> (@.nom1,@.nom2,'sold');
> what about the values of my column action... Hide quoted text -
> - Show quoted text -
Argh, sorry it's friday... a very long... friday.
INSERT INTO Sales(prodid, qty, action) SELECT prodid, qty, 'Sold' as
Action FROM Orders WHERE qty > 20
That should do the trick.|||ok, thanks very much.

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list>)
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thank you for your help. I tried below and got "Msg 102, Level 15, State 1,
Line 13
Incorrect syntax near ','." error. What am I missing?
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT ([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
FROM [LcsLog].[dbo].[messages]
"Plamen Ratchev" wrote:
> The problem is SELECT *. Just use INSERT INTO and list all columns excluding
> the IDENTITY column, like this:
> INSERT INTO [IMArchive].[dbo].[messages]
> (<column_list>)
> SELECT <column_list>
> FROM [LcsLog].[dbo].[messages]
> That way the IDENTITY column will get populated automatically.
> If you want to copy the IDENTITY column, then add it to the <column_list>
> and use SET IDENTITY_INSERT ON.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||artunc,
lose the parentheses around your column list in your select statement
-st|||Try this:
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT
[date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2]
FROM [LcsLog].[dbo].[messages]
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting a different error...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>|||Is this from the same insert? The error indicates violation of FOREIGN KEY
constraint. You can add a WHERE filter to select only rows that do not
violate the constraint. Another option is to disable the FOREIGN KEY (see
ALTER TABLE ... NOCHECK CONSTRAINT ...), but that can lead to breaking the
data integrity.
Posting the create table statements with all constraints will help to get
better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting this errror:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...
The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list>)
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thank you for your help. I tried below and got "Msg 102, Level 15, State 1,
Line 13
Incorrect syntax near ','." error. What am I missing?
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT ([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
FROM [LcsLog].[dbo].[messages]
"Plamen Ratchev" wrote:

> The problem is SELECT *. Just use INSERT INTO and list all columns excluding
> the IDENTITY column, like this:
> INSERT INTO [IMArchive].[dbo].[messages]
> (<column_list>)
> SELECT <column_list>
> FROM [LcsLog].[dbo].[messages]
> That way the IDENTITY column will get populated automatically.
> If you want to copy the IDENTITY column, then add it to the <column_list>
> and use SET IDENTITY_INSERT ON.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>
|||artunc,
lose the parentheses around your column list in your select statement
-st
|||Try this:
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT
[date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2]
FROM [LcsLog].[dbo].[messages]
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||That worked, thank you. Now I'm getting a different error...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:

> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>
|||Is this from the same insert? The error indicates violation of FOREIGN KEY
constraint. You can add a WHERE filter to select only rows that do not
violate the constraint. Another option is to disable the FOREIGN KEY (see
ALTER TABLE ... NOCHECK CONSTRAINT ...), but that can lead to breaking the
data integrity.
Posting the create table statements with all constraints will help to get
better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||That worked, thank you. Now I'm getting this errror:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:

> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>

Covering Index Question

we have a table with the following structure (not full but should give an
idea how it looks like)
CREATE TABLE [dbo].[JK_Product] (
[Creator] [varchar] (50) NOT NULL ,
[ID] [int] NOT NULL ,
[Product] [int] NOT NULL ,
[PartNum] [char] (32) NOT NULL ,
..
..
..
[CheckShipping] [char] (1) NULL
) ON [PRIMARY]
GO
We have these 3 indexes already on this table
CLUSTERED INDEX1
([Creator], [Product])
PRIMARY KEY NONCLUSTERED INDEX1
([Creator],[ID])
NONCLUSTERED INDEX2
([Creator], [Product], [PartNum])
ITW suggested me these 3 new indexes based on some workload i gave
NONCLUSTERED INDEX3
([ID])
NONCLUSTERED INDEX4
([PartNum])
NONCLUSTERED INDEX5
([Product], [Creator])
is it necessary to create INDEX3 and INDEX4 since we already have those
covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
use of the existing indexes effectively? what if we create a covering NCI
consisting of Creator, ID, Product, PartNum columns and drop other NCIs
TIAHi
You need not create Index 3 and 4 as Index 1 and 2 takes care of it.
Creating an Index depends on the query that you frequently use.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"paraa" wrote:

> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||First, it is meaningless to talk about a clustered index covering a query. A
clustered indexes
covers all queries against the table.
So lets focus on the other indexes. If you don't have what you search for in
the first column of the
index, SQL Server cannot s for that condition. It can scan the index, but
not s. That might be
why it suggested an index in the ID, PartNum and Product, Creator. In short,
without knowing your
workload, selectivity etc, we cannot say whether the recommendations from IT
W are good
recommendations...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer ma
ke
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||Hi
As well , read this article
http://www.sql-server-performance.c...ing_indexes.asp
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
> we have a table with the following structure (not full but should give an
> idea how it looks like)
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
> We have these 3 indexes already on this table
> CLUSTERED INDEX1
> ([Creator], [Product])
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
> ITW suggested me these 3 new indexes based on some workload i gave
> NONCLUSTERED INDEX3
> ([ID])
> NONCLUSTERED INDEX4
> ([PartNum])
> NONCLUSTERED INDEX5
> ([Product], [Creator])
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer
> make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> TIA
>|||tibor thx for the reply. yeah, i know a clustered index covers all queries
against the table. i was more interested to find out if instead of having 3-
4
small NCIs to cover some X queries, wouldn't it be better to have one long
NCI in general (I haven't fixed the order of columns in it yet...and maybe
the order can be fixed on further analysis of %age of its use in those X
queries)?
"Tibor Karaszi" wrote:

> First, it is meaningless to talk about a clustered index covering a query.
A clustered indexes
> covers all queries against the table.
> So lets focus on the other indexes. If you don't have what you search for
in the first column of the
> index, SQL Server cannot s for that condition. It can scan the index, b
ut not s. That might be
> why it suggested an index in the ID, PartNum and Product, Creator. In shor
t, without knowing your
> workload, selectivity etc, we cannot say whether the recommendations from
ITW are good
> recommendations...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
>|||The ordering of the columns is one big issue. If you have a query with a whe
re clause in which you
only search for colA, then an index on (colB, colA) will not be searchable.
SQL Server can still
scan the index (assuming it covers the query), but a search is much better (
especially if the index
covers the query as no bookmark lookups are needed).
So, you could say that it is better to create a bunch of one-column indexes
and let SQL Server join
them as the query is processed (index intersection) so that the indexes toge
ther will cover the
query. Now you can search for whichever column you have ion the indexes and
you can always have a
s. OTOH, you will pay the price for the index intersections as the query
is processed (in the
cases then SQL Server joins the indexes in run-time).
So, we cannot say which is better. You can, but either analyzing the queries
etc. Or by running a
load test for your particular load and see which index alternative is pest f
or your load test.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
> tibor thx for the reply. yeah, i know a clustered index covers all queries
> against the table. i was more interested to find out if instead of having
3-4
> small NCIs to cover some X queries, wouldn't it be better to have one long
> NCI in general (I haven't fixed the order of columns in it yet...and maybe
> the order can be fixed on further analysis of %age of its use in those X
> queries)?
> "Tibor Karaszi" wrote:
>|||In addition you may consider the space requirements for the nonclustered
indexes. This can vary but can be high for a table with a large number of
rows and although not recommended - a large clustered index key (will be
dupped in each nc index).
HTH
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1CSKoZzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> The ordering of the columns is one big issue. If you have a query with a
> where clause in which you only search for colA, then an index on (colB,
> colA) will not be searchable. SQL Server can still scan the index
> (assuming it covers the query), but a search is much better (especially if
> the index covers the query as no bookmark lookups are needed).
> So, you could say that it is better to create a bunch of one-column
> indexes and let SQL Server join them as the query is processed (index
> intersection) so that the indexes together will cover the query. Now you
> can search for whichever column you have ion the indexes and you can
> always have a s. OTOH, you will pay the price for the index
> intersections as the query is processed (in the cases then SQL Server
> joins the indexes in run-time).
> So, we cannot say which is better. You can, but either analyzing the
> queries etc. Or by running a load test for your particular load and see
> which index alternative is pest for your load test.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
>

Counting in Self Joins

I have a view that contains a self join:
SELECT dbo.Clients1.ClientID, dbo.Clients1.AccountName,
dbo.Clients1.OwnedByClientID,
Clients1_1.AccountName AS OwnedByClientName
FROM dbo.Clients1 INNER JOIN
dbo.Clients1 AS Clients1_1 ON
dbo.Clients1.OwnedByClientID = TCSClients1_1.ClientID
How can I also include a column that would count the number of
"OwnedByClientID"s that are owned by a ClientID?
TIA,
PaulIf this is a hierarchy of unknwn depth, you will need a different
model. Google up Nested Sets for trees.|||Thanks.
Actually, at this point it is only 2 or three deep, but you are correct, I
am interested in an arbitrarily deep nesting.
...<snip>...
> If this is a hierarchy of unknwn depth, you will need a different
> model. Google up Nested Sets for trees.
>|||Paul,
In SQL Server 2005, finding this information is not
as hard as it used to be. Here's an example from
the AdventureWorks sample database that calculates
the total number of subordinate employees for each
manager. By default, recursive queries allow 100
levels of recursion before generating an error, though
this can be changed with the MAXRECURSION
option, if your hierarchy can be deeper.
with Ancestry(ManagerID,Subordinate) as (
select
ManagerID, EmployeeID
from AdventureWorks.HumanResources.Employee
union all
select
A.ManagerID,
E.EmployeeID
from AdventureWorks.HumanResources.Employee as E
join Ancestry as A
on A.Subordinate = E.ManagerID
)
select ManagerID, count(*) as subs
from Ancestry
group by ManagerID
-- Steve Kass
-- Drew University
Paul wrote:

>Thanks.
>Actually, at this point it is only 2 or three deep, but you are correct, I
>am interested in an arbitrarily deep nesting.
>...<snip>...
>
>
>