I have recently received email that I should update SQL SERVER – Simple Example of Cursor with example of AdventureWorks database.
Simple Example of Cursor using AdventureWorks Database is listed here.
USE AdventureWorks
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
[...] on March 5, 2008 at 7:00 am15 SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2 Journey to SQL Authority with Pinal Dav… [...]
Hello Pinal,
I have begin to learn writing Stored procdures using cursors. Please give a few examples of how I can concatenate a set of strings based on another value in a table
Thanks
hi panel,
can you describe about the Kelset cursor and
what is the exact different between dynamic ,static, Kelset cursor
Hi dave, could you write about database benchmarks; TPC-C and TPC-E?
Pinal,
Thanks lot for such helpful blog. I found lots of nuts and bolts on SQL server from your blog.
I am new to SQL Server but started working on real time project. In your example of Cursor in Adventureworks database, you defined a cursor variable ( as i believe). Is that possible to define variable like that to hold multi-column value.
I would appreciate any kind of response.
regards
You can’t do that. You may need to make use of table variable
declare @t table(col1 int, col2 char(1),….)
I tested your code on sql server 2005, it is not working.
First, define cursor without @ symbol. I am not sure why. Thanks for response. — Coway
use northwind
go
declare QuanCsr cursor for
select quantity from mytable00
declare @qty int
declare @sqlstatus int
open QuanCsr
fetch next from QuanCsr into @qty
while @@fetch_status = 0
begin
print @qty
fetch next from QuanCsr into @qty
select error = @@error — error is equvalent to column
select sqlstatus = @@fetch_status — sqlstatus is equvalent to column
end
print ‘sql status ‘ + convert(varchar, @sqlstatus)
close QuanCsr
deallocate QuanCsr
how can we see the list of cursor ?
Refer this blog post and see if that answers it
There are two methods
http://beyondrelational.com/blogs/madhivanan/archive/2009/09/08/cursor-statuses.aspx
Hi dave
here i want immediate solutions for followings using cursors
master table :- items
sno items
—————–
1 pen
2 pencil
3 box
:- sno is primary key
here another table name is :- trantables
sno nos rate total type
————————————————-
1 5 7 35 issue
1 2 6 12 receipt
1 1 7 7 issue
using cursor
first we get record in master table rows,
check using sno and type
items.sno=trantables.sno and trantables.type=’issue’
if its ok give sum(nos) and sum(total)
———————————————————————–
items.sno=trantables.sno and trantables.type=’receipt’
if its ok give sum(nos) and sum(total)
here final solution : this one i want using cusor
————————————————————–
sno items issuenos issuetotal receiptnos receipttotal
—————————————————————————–
1 pen 6 42 2 12
@sulai
you have two ways to execute it, every time change the values of @var1 and @var2 , or create a stored procedure and just pass the parameters. I have mentioned both,
CREATE TABLE ITEM (SNO INT , ITEMS VARCHAR( 20 ) )
INSERT INTO ITEM
SELECT 1 , ‘PEN’ UNION ALL
SELECT 2 , ‘PENCIL’ UNION ALL
SELECT 3 , ‘BOX’
CREATE TABLE TRANTABLES ( SNO INT , NOS INT , RATE INT , TOTAL AS NOS*RATE , TYPE VARCHAR(20))
INSERT INTO TRANTABLES (SNO , NOS, RATE , TYPE)
SELECT 1, 5 , 7 , ‘ISSUE’ UNION ALL
SELECT 1, 2 ,6 , ‘RECEIPT’UNION ALL
SELECT 1 ,1 ,7 , ‘ISSUE’
–CREATE PROC USP_CHECKING @SNO INT , @TRAN VARCHAR(20) — — if you want to make a stored procedure
–AS
– if you dont want to make a stored proc, start from here
DECLARE @VAR1 INT
DECLARE @VAR2 VARCHAR(20)
DECLARE @VAR3 VARCHAR(20)
DECLARE @VAR4 INT
DECLARE @VAR5 INT
DECLARE @CUR CURSOR
SET @CUR = CURSOR FOR
SELECT DISTINCT SNO , TYPE FROM TRANTABLES
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR1 , @VAR2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @VAR3 = ITEMS FROM ITEM WHERE SNO = @VAR1
SELECT @VAR4 = SUM (NOS) FROM TRANTABLES WHERE TYPE = @VAR2 AND SNO = @VAR1
SELECT @VAR5 = SUM (TOTAL) FROM TRANTABLES WHERE TYPE = @VAR2 AND SNO = @VAR1
IF @VAR1 = 1 AND @VAR2 = ‘ISSUE’ — comment this if you want to automate the process
–IF @VAR1 = @SNO AND @VAR2 = @TRAN — If you want to use this method, comment above line
SELECT @VAR1 SNO , @VAR3 ITEMS, @VAR4 ISSUENOS, @VAR5 ISSUETOTAL, SUM(NOS) RECIEPTNOS, SUM(TOTAL) RECIPTTOTAL
FROM TRANTABLES
WHERE TYPE @VAR2
FETCH NEXT
FROM @CUR INTO @VAR1 , @VAR2
END
CLOSE @CUR
DEALLOCATE @CUR
GO
– AFTER YOU CREATE THIS STORED PROCEDURE EXECUTE THIS PROCEDURE
– EXEC USP_CHECKING 1, ISSUE
Hope this helps.
yeah very superb examble it’s very use full
Thanks,
Raja.R
All,
I am using Microsoft SQL 2005 and am trying to create a cursor that will take data from several records and concatenate that data into one record.
For example:
I have three attributes (OrderYear, SeqNumber, Item) from a single table (table name: TSGItems) that I am working with…
The data in TSGItems table looks like this:
OrderYear SeqNumber Item
2008 10001 Senior IRS Lien
2008 10001 IRS Lien
2008 10002 Senior IRS Lien
2008 10003 IRS Lien
2008 10003 Senior IRS Lien
2008 10003 Jr. Lien
2008 10004 HOA Lien
In addition to the concatenation, I am trying to manipulate the data as follows:
1) I would like to order the items alphabetically.
2) I would like to put an “and” before the last concatenation when there are more than 1 Item for each Orderyear and SeqNumber.
3) I would like to put in a “,” between each concatenation when there are 3 or more Item for each Orderyear and SeqNumber (except in between the last two concatenation.
4) I would like this data inserted into a temp table called #TSGItems
When I do a select statement on #TSGItems, I would like the data shown as follow:
OrderYear SeqNumber Item
2008 10001 IRS Lien and Senior IRS Lien
2008 10002 Senior IRS Lien
2008 10003 Jr. Lien, IRS Lien, and Senior IRS Lien
2008 10004 HOA Lien
I thank you for taking the time to look at and attempt to solve my challenge.
-Anthony
@Anthony
Let me tell you I am not a developer and I dont have much developing skills… I am sure there must be another good way of writing sql code for what you have asked…
Below is my code, This does exactly what you described in your post.
CREATE TABLE TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
INSERT INTO TGSITEMS (ORDERYEAR, SEQNUMBER, ITEM)
SELECT ’2008′ ,10001 , ‘Senior IRS Lien’ UNION ALL
SELECT ’2008′ , 10001 , ‘IRS Lien’ UNION ALL
SELECT ’2008′ , 10002 , ‘Senior IRS Lien’ UNION ALL
SELECT ’2008′ ,10003, ‘IRS Lien’ UNION ALL
SELECT ’2008′ ,10003 , ‘Senior IRS Lien’ UNION ALL
SELECT ’2008′ ,10003, ‘Jr. Lien’ UNION ALL
SELECT ’2008′ ,10004, ‘HOA Lien’
We created a table TGSITEMS and we inserted all the values which you mentioned in your post.
Then I created two more temporary tables #TGSITEMS and #TGSITEMS1… and with the help of While loop and cursor I got your result.
CREATE TABLE #TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
GO
CREATE TABLE #TGSITEMS1 ( ID INT IDENTITY, ITEM VARCHAR(MAX))
GO
DECLARE @VAR INT
DECLARE @CUR CURSOR
DECLARE @VAR1 INT
DECLARE @CMD VARCHAR(1000)
DECLARE @CMD1 VARCHAR(1000)
SET @CUR = CURSOR FOR
SELECT DISTINCT SEQNUMBER
FROM TGSITEMS
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #TGSITEMS1
INSERT INTO #TGSITEMS1 (ITEM) SELECT ITEM FROM TGSITEMS WHERE SEQNUMBER = @VAR ORDER BY ITEM
SET @VAR1 = 1
SET @CMD1 = ”
WHILE @VAR1 <= ( SELECT COUNT(*) FROM #TGSITEMS1 )
BEGIN
SELECT @CMD = ITEM FROM #TGSITEMS1 WHERE ID = @VAR1
IF @VAR1 <(SELECT COUNT(*) FROM #TGSITEMS1) -1
SET @CMD1 = @CMD1 + @CMD +’ , ‘
ELSE IF @VAR1 = (SELECT COUNT(*) FROM #TGSITEMS1 )-1
SET @CMD1 = @CMD1 + @CMD
ELSE IF (SELECT COUNT(*) FROM #TGSITEMS1 ) = 1
SET @CMD1 = @CMD
ELSE
SET @CMD1 = @CMD1 + ‘ and ‘ + @CMD
SET @VAR1 = @VAR1 + 1
END
INSERT INTO #TGSITEMS SELECT DISTINCT ORDERYEAR , SEQNUMBER, @CMD1 FROM TGSITEMS WHERE SEQNUMBER = @VAR
FETCH NEXT
FROM @CUR INTO @VAR
END
CLOSE @CUR
DEALLOCATE @CUR
GO
SELECT DATENAME ( YY, ORDERYEAR)ORDERDATE , SEQNUMBER, ITEM FROM #TGSITEMS
GO
TRUNCATE TABLE #TGSITEMS
GO
Let me tell you this is highly performance consuming process… its upto you if you want to use it…
Hope this helps.
Thanks
Imran.
Hello,
i’m not a programmer but i have an urgent need to have a piece of code to update a table on demand.i need to flag x number of records with the same state code based on a look up table which has two columns State, TotalNumber.
please help!!!
Thank you Imran Mohammed,
Your comments are very helpful and please continue participating on this community.
Thank you Imran,
I really appreciate you taking the time to address this challenge. I have been trying to move away from MS Access and am new to the SQL environment. I agree, what I am asking for is very demanding, but I feel reverting to Access is a step backward. I cannot wait to try the coded at work when I return on Monday.
Cheers
-Anthony
Imran,
When I tweaked your script in a few places it worked perfectly!!!! What more, I was able to apply the script to another project and got that one to work too. Thanks again for all your help.
Anthony
Hi Dave
I have a little problem in that I need to devise a SSIS Package that will look in a folder and concatenate the data files priior to loading. The filenames are of the pattern:-
abcXXX21072009.txt
abcXXX21072008.txt
I need these two put into a new file named (say) Test1.txt
abcZZZ21072008.txt
abcZZZ21072008.txt
and these put into a new file named (say) Test2.txt
would welcome some advice please
Many thanks
Jim
I need to create a diagnostic package using cursors using this logic
1.Loop through and load clean data from source table destination table i.e while the @@fetch_status =0
2.if the @@Fetch_status 0,add the current row to Error table
3.Restart the cursor at the next record to avoid reloading the same records
My brain is blowing out about this :( .I’ll probably add as the responses pour in
Thank you
What is your criteria about clean?
If you post it here, some one may give you a set-based code when you dont need a loop
Thanks Dave …
I have Learned so many things from your blog …
Hi,
I have a similar issue as others who have posted.
I am writing a stored proc to search for a group of products on a quote. I am returning 2 fields: quantity and productid. I then want to update the quantity by a parameter passed when the proc is called.
What I have right now is not working:
ALTER PROCEDURE [dbo].[xxxxxx_main_quantity] (@quoteid uniqueidentifier, @new_maintenanceyears int)
AS
declare @n_quantity decimal(18,5); /*this is to identify which quote we are looking for*/
declare @quotedetailid uniqueidentifier; /*this is to identify which quote product we are looking for*/
declare o_curs cursor for
select q.quantity*@new_maintenanceyears as newquantity, q.quotedetailid
from filteredquotedetail q
left outer join filteredProductPriceLevel p on q.productid = p.productid
left outer join filteredProduct f on p.productid = f.productid and p.pricelevelid = f.pricelevelid
where q.quoteid = @quoteid and p.PriceLevelIDName = ‘Default’
and f.new_itemclassname = ’1′
BEGIN
/*Open cursor and fetch first row of results*/
open o_curs
fetch o_curs into @n_quantity, @quotedetailid
/*If no rows, return immediately*/
if (@@fetch_status = -1)
begin
close o_curs
return
end
/*Loop through each row of query results*/
while (@@fetch_status = 0)
begin
update filteredquotedetail
set quantity = @n_quantity
where quotedetailid = @quotedetailid
end
/*Done with results; close cursor and return*/
close o_curs
END
sir
can i insert values into tables through cursor
Hi Pinal,
I work as a .NET Developer in US. I have been following your posts since i started development. Thanks for the support since then..:). I have a problem joining a table and result set. Here is the problem. I have only single column table with data type int and it is the primary key and I have filled in the values 0 to 23(whole day hours).
Temporary table:
tHours( hour int primary key).
I am performing a select query from the other tables getting the values with columns
dayhour(int)
personalnumber(varchar)
date(timestamp)
balance(double)
So what i trying to do is insert zero value for hour for which there is no balance
Ex:
0 2323232 10/29/2008 56
1 2323232 10/29/2008 0
..
..
23 2323232 10/29/2008 48
I pulling out my hair since 3 days of how to do this…
Your help is much appreciated!
Thanks
i used your code pinaldave , i would like to thank you very much , my suggestion when you used this code ,fill all values into table ex from 1 to 10 then run the cursor if you need serial number
Hi Pinal how can i use if else inside the Cursor?
I am trying to insert records into a table with seqn #
My code is not doing what it is suppose to be doing.
drop table #relationtable
create table #relationtable
(
id varchar(10),
Relation_type varchar(20),
co_id varchar(10),
target_relation_type varchar(20),
updated_by varchar(15),
seqn int
)
insert into #relationtable (id, Relation_type,co_id,target_relation_type,updated_by,seqn)
select id,’CONTACT_I’ as relation_type, co_id as target_ID,’CONTACT_C’ as Target_relation_type,
‘SCHANDRA’ as upated_by ,” as SEQN from name
where id in (’184918′,’181641′,’116787′)
DECLARE @ID char(11),
@seqn int
set @seqn = 1
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT id
FROM #relationtable
OPEN c1
FETCH NEXT FROM c1
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
update #relationtable
set seqn = @seqn
set @seqn = @seqn + 1
FETCH NEXT FROM c1
INTO @ID
END
CLOSE c1
DEALLOCATE c1
select * from #relationtable
I was expecing to see Seqn # as 1,2,3 instead it is set to 3 on all the records.
Could you please help?
Thanks
Shashi
how should i use cursor for make a dynamic query. like
declare @test_temp_count_one cursor
declare @test_temp_query_one nvarchar(1000)
declare @VAR_DIVIDE1 varchar(20)
declare @var_table varchar(100)
declare @hold_temp_value varchar(20)
set @VAR_DIVIDE1 = 10
set @var_table = ‘table13′
set @hold_temp_value = 20
set @test_temp_query_one = ‘ SET
@test_temp_count_one = (SELECT TOP ‘ + @VAR_DIVIDE1 + ‘ id FROM ‘+ @var_table +’ WHERE ID > ‘ + @hold_temp_value + ‘ ORDER BY ID ) ; OPEN @test_temp_count_one ‘
exec (@test_temp_query_one)
when i execute this code , i found an error……
error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable ‘@test_temp_count_one’.
can anybody help me…….
thanks in advance
hi ,
iam new to sqlserver, but i need to write a stored procedure using cursor. i have field in the application called refno. and referencing to that field i need to capture the other field values for a particular date .The refno. field have a specific value for some application type and other field value changes.
could u please help me in sorting out this.
many thanks
Hi,
I’m working with SQL and I admit that I am struggling!!! Here’s my problem, I’m trying to use a cursor to look at:
1. The first line…
2. The second line…
if those lines are equal only output the first line. and fetch the next…compare that to the first line….if those are equal do not output anything…simply fetch next.
BUT
if the rows are not equal…output both rows and make the second row the “comparison” row.
In c# it would be something like
int Variable = variable1
for (int row = 0; row < rowCount; row++)
{
IF (variable 1 == variable[i])
{
output variable1
}
else
{
variable1 = variable[i]
}
}
any help would be much appreciated!
Sir,
You are really doing a great job. I am a MCA student. I have become a regular user of your blog for the past one month and it seems that I have addicted to your blog. I have learned many things from your blog. So, Iwant to thank you from the bottom of my heart.
Keep it up and God bless you.
i have a scenario, where i have to insert agent ids, Say i have 5 agentids(1,2,3,4,5) in Table A and when i insert any records in Table B it has to assign the agent id , If there is a transaction which inserts 21 records in TABLE B for each record agentid should be assigned ,till 5 records it should assign the agent ids accordingly n for the sixth record it should start from 1 so on.For the 21st record the the agentid 1 should be assigned
ex: TableB
c1 c2 c3 agentid
r1 r12 r13 1
r2 r22 r23 2
r3 r32 r33 3
r4 r42 r43 4
r5 r52 r53 5
r6 r62 r63 1
r7 r72 r73 2
………
……
……
r21 r1 r14 1 “last record ”
what will be the best approach fr this
Thanks,
Preetham.
brijnandan — Error statement is itself telling you the solution, you need to declare the variable.
set @test_temp_query_one = ‘ declare @test_temp_count_one cursor SET
@test_temp_count_one = (SELECT TOP ‘ + @VAR_DIVIDE1 + ‘ id FROM ‘+ @var_table +’ WHERE ID > ‘ + @hold_temp_value + ‘ ORDER BY ID ) ; OPEN @test_temp_count_one ‘
exec (@test_temp_query_one)
i have the table with the following fields
FromGrams
ToGrams
Price
CountryID
suppose consider the table have the value as follows
FromGrams=10
ToGrams=100
Price=0.25
CountryID=221
________________________________________________
if suppose user adding new range that have the value like follows
FromGrams=50
ToGrams=120
Price=0.95
CountryID=221
then the result table will like this
FromGrams ToGrams Price CountryID
10 50 0.25 221
51 120 0.95 221
like that all conditions have to satisfy
Dear Pinal,
I’ve got the same problem below as desribed by Smita
Smita
Hello Pinal,
I have begin to learn writing Stored procdures using cursors. Please give a few examples of how I can concatenate a set of strings from a table field and use the result string to update another table field.
Thanks
Can you give us some sample data and exprected result?
It seems you need to use concatenation method specified here
See the code under “Generate data in csv format ”
http://beyondrelational.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx
Is there a way to populate a cursor with multiple sql statements?
Hi,
please you send me the e-book vie email address of cursor of sql server 2005
I want to learn more
thank you sir
Phone
Dont concentrate on cursor. Search for Set based approach in Google/Bing
Hi all,
Im using cursor in my procedure,Im getting the value from 1 table,that value is stored in local variable @var,that value is the column name of another table.I want to pass the @var in Cursor Select Statement.How i do that..the Below statements im using…
Set @var=(select field from table1)
Declare cursorname cursor for
Select @var from table2 where No=2 —>Getting Error
open cursorname
This is not possible until you use dynamic sql. Can you give us more informations on what you are doing with cursor?
Hi EveryOne,
I need a solution to this Situation .
In a table there is a column with values 00090,000080,000189 and so on
I need to update the column of the table to 0090,0080,0189 and so on.the data type of the column is varchar(20)
Can we do that .
If so please let me know.
Thanks,
Sravanthi
use right(col,4)
create table fu1(uf varchar(20),id int)
insert into fu1(uf,id)
select ’00000090′,1
union all
select’000080′,2
union all
select ’000189′,3
select * from fu1
update fu1 set uf=’0090′ where id=1
update fu1 set uf=’0080′ where id=2
update fu1 set uf=’0189′ where id=3
This is very simple
update fu1 set uf=right(uf,4)
1) how to access indivudal values in a multi value filed.
for eg:
table1
col1
(1,2,3)
i want extract each value from col1 and wants to compare with single value column.
2) how to enter into particular user database in stored procedure.
1 Search for split function in this site
2 you need to use three part names dbname.ownername.objectname in the procedure
Hi All,
This blog is very useful to everyone we can discuss our probs here & got a good response with less time.
By the way my self Sunil Kumar kaushal i am wokring as a MIS Analyst but i want to become a SQL Develper, i have good knowledge of SQL SERVER 2005 but i want to work on live project.
Please guide me how can i do that..
Regards
Sunil Kumar Kaushal
Hi,
I have created a cursor on Trigger but getting an error. Row is getting effected after updation.
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0×80040111
(1 row(s) affected)
Can any one help me to resolve this error..
declare @deptno int
declare @deptname varchar(10)
declare curdept cursor for
SELECT vdeptno, vdeptname from departments
OPEN curdept
FETCH curdept INTO @deptno, @deptname
WHILE(@@fetch_status=0)
BEGIN
Print ‘Department No =’ + cast(@deptno as varchar(10)
Print @deptname
FETCH curdept INTO @deptno, @deptname
END
CLOSE curdept
DEALLOCATE curdept
Hai Pinal,
How Could i use if else inside cursor.
You can use it just like you use for other statements. Refer this to know how to use it
http://beyondrelational.com/blogs/madhivanan/archive/2010/08/11/various-usages-of-if-else-clause-in-sql-server.aspx
Hi Pinal,
Can you help me with CASE Update with Cursor. The cursor is workin fine but not updating the value properly.
——
Declare @state varchar (20) , @city varchar (20) ,@occgrp varchar (10)
Declare dimclinfo Cursor
For
Select state,city , occgrp
from dimclinfo
order by state,city,occgrp
open dimclinfo;
while @@fetch_status = 0
begin
Fetch next from dimclinfo
into @state , @city , @occgrp
update dimclinfo
set combinationid = CASE
WHEN ( @state is null and @city is null and @occgrp = ‘Overall’ )
THEN 411
WHEN (@state is not null and @city is null and @occgrp = ‘Overall’ )
THEN 412
WHEN ( @state is null and @city is not null and @occgrp = ‘Overall’)
THEN 413
WHEN (@state is null and @city is null and @occgrp != ‘Overall’ )
THEN 414
WHEN (@state is not null and @city is null and @occgrp != ‘Overall’)
THEN 415
WHEN ( @state is not null and @city is not null and @occgrp!= ‘Overall’)
THEN 416 — It is populating only this value throughout
else 1
END
Fetch next from dimclinfo
into @state , @city ,@occgrp
END
CLOSE dimclinfo ;
Deallocate dimclinfo;
hello i have a question …
suppose a query
select * from emp where empid in(1,5,8,2,4,10,9);
what i want is
the result should come as in the same sequence as the sequence maintained in above query as(1,5,8,2,4,10,9).
generally what happen is the result come in sorted order but i do not want that sorted order.
hoe can i update multiple row in sqlserver but there is no primary or unique key
i like you code
no formating is better
make computer more fast
Id, DoctypeCode, SubtypeCode, Batch_Type,
1 ACC cmp rd
From Document Number, Thru Document Number, Street
R o p
I have 129 column in a table. I want to query a table to find out for each doctypecode and subtypecode how many records exists with ‘o’ and ‘r’
I want to see the result not the count.
Please help me on this
hello sir my name is happy i want to backup of 200 tables but 300 table in 1 database. if i want to full backup use database right click backup.
i don’t know use this query
ok sir
why we are using procedures?function is always return the value.the procedure may or may not return the value..
Sir I am huge fan of your’s . I often read your articles. recently I got a task to insert data in two tables by one subquery . I couldnot perform that task . please give me some solutions
Use Insert trigger that adds data to another table or use OUTPUT clause
Hi Iam irfan ,It such a nice blog thanks for your service
Hi All ,In need of your help,
Here is scenario
Table A :
invoice_no Product
————— —————–
1 Pencil
1 Box
1 Pen
2 Pen
2 Box
3 Pencil
i need to create a cursor such that i store in #details(Temporary table)
Desired Output :
Select * from #details
Invno Products
——– ———————–
1 Pencil,Box,Pen
2 Pen,Box
3 Pencil
[...] Simple Example of Cursor – Sample Cursor Part 2 One of the most popular script on SQL Server Cursor – a very simple example. [...]
I have a table
Id Value
— ——–
1 aa,ab,av
2 ba
3 ca,cb
4 da,db,dc,dd
and I want to create a table like this
id Value
— ——
1 aa
1 ab
1 av
2 ba
3 ca
3 cb
4 da
4 db
4 dc
4 dd
Can you suggest me code for this