Alternate to cursors

Hi,
I have a situation where I am loading data into a staging
table for multiple data sources. My next step is to pick up the
records from the staging table and compare with the data in the
database and based on the certain conditions, decide whether to insert
the data into the database or update an existing record in the
database. I have to do this job as an sp and schedule it to run on the
server as per the requirements. I thought that cursors are the only
option in this situation. Can anyone suggest if there is any other way
to achieve this in SQL 2005 please.

Thanks

SeshadriIn general this is done with two commands, an UPDATE of existing rows
followed by an INSERT of new rows. Very generally:

UPDATE Target
SET cola = A.cola,
colb - A.colb
FROM Staging as A
WHERE Target.keycol = Staging.keycol

INSERT Target
SELECT keycol, cola, colb
FROM Staging as A
WHERE NOT EXISTS
(SELECT * FROM Target as B
WHERE A.keycol = B.keycol)

Whether this fits your requirements is unknown because you didn't
provide much information. It would require knowing at least the table
definitions and keys, as well as the "certain conditions".

Roy Harvey
Beacon Falls, CT

On Mon, 01 Oct 2007 00:12:22 -0700, srirangam.seshadri@.gmail.com
wrote:

Quote:

Originally Posted by

>Hi,
I have a situation where I am loading data into a staging
>table for multiple data sources. My next step is to pick up the
>records from the staging table and compare with the data in the
>database and based on the certain conditions, decide whether to insert
>the data into the database or update an existing record in the
>database. I have to do this job as an sp and schedule it to run on the
>server as per the requirements. I thought that cursors are the only
>option in this situation. Can anyone suggest if there is any other way
>to achieve this in SQL 2005 please.
>
>Thanks
>
>Seshadri

Altering multiple objects schema

Hi,

I need to change the schema of the stored procedures of several databases.

Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?

thank you

Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

-Raul Garcia

SDE/T

SQL Server Engine

|||

You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

declare @.querystring nvarchar(MAX)

set @.querystring=''

select @.querystring=@.querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

exec(@.querystring)

Either way, you will have to use dynamic sql.

Alter table Question in Sql 2000

How to add multiple columns with alter table command in Sql 2000 ?

ALTER TABLE [deneme].[dbo].[Mudurluk]
ADD HarcamaYetkilisi varchar(50) COLLATE Turkish_CI_AS NULL

//below gives error
ADD MaliKontrolYetkilisi varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD Memur varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD Sef varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD MuhasebeYetkilisiYardimcisi varchar(50) COLLATE Turkish_CI_AS NULL

Can you help me with this ?

Thanks alot in advance

Specify ADD only the first time.

Alter table multiple columns?

How do I alter multiple columns with one SQL statement?

I've tried :

ALTER TABLE epcs_benefit_plan ALTER COLUMN

abc1 varchar(3) not null,

abc2 varchar(3) not null

You will have to use multiple ALTER TABLE statement to achieve this.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

alter table lock?

Hi!

I use proc handling special business logic (I also use constraints, indexes for that ;-)

Now I have a situation where I should check multiple rows with an proc.

Preventing multi-user issues I want to lock the table (yes, yes potential performance issue, but in this case there are few simultaneous jobs) - in Oracle I could lock the table, but what to do in SQL Server?

Maybe you have an better alternative, then let me hear ;-)

Or should I use "begin transaction"...

Thanks for help

Hmm, maybe

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- DO YOUR stuff

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

will be the solution...

Some comments?

sql

Alter Table Alter Column

I need to Alter the multiple column of an Existing table
ALter TABLE Address
Alter Coumn Address1 varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address2 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address3 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
But this gives me an error
please advice me on this
thanks
samayYou have to have a separate ALTER TABLE statement for each ALTER COLUMN I'm
afraid.
--
David Portas
SQL Server MVP
--

Alter Table Alter Column

I need to Alter the multiple column of an Existing table
ALter TABLE Address
Alter Coumn Address1 varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
Address2 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address3 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
But this gives me an error
please advice me on this
thanks
samayYou have to have a separate ALTER TABLE statement for each ALTER COLUMN I'm
afraid.
David Portas
SQL Server MVP
--

Alter Table Alter Column

I need to Alter the multiple column of an Existing table
ALter TABLE Address
Alter Coumn Address1 varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address2 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address3 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
But this gives me an error
please advice me on this
thanks
samay
You have to have a separate ALTER TABLE statement for each ALTER COLUMN I'm
afraid.
David Portas
SQL Server MVP

Alter Table

Simple SQL question.

I am trying to add multiple columns to a temp table and the alter statement throws the following error.

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

The alter statement looks like this.

ALTER TABLE #BillingData ADD (T2 FLOAT, T3 varchar(20) NULL)

I thought you can add multiple columns putting the names in ( ).

Any ideas where I am doing wrong.

Thanks.

Quote:

Originally Posted by ymk

Simple SQL question.

I am trying to add multiple columns to a temp table and the alter statement throws the following error.

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.

The alter statement looks like this.

ALTER TABLE #BillingData ADD (T2 FLOAT, T3 varchar(20) NULL)

I thought you can add multiple columns putting the names in ( ).

Any ideas where I am doing wrong.

Thanks.

hi ymk,
remove the brackets after add. just
alter table #billingdata add t2 float,t3 varchar(20) null

Alter column

what is the syntax for multiple column modifications ?

alter table abc
alter column x1 nvarchar(10) null

-- works

alter table abc
alter column x1 nvarchar(10) null,
alter column x2 nvarchar(10) null

-- doesn't work or do i have to use the alter table each time ?Want the lazy/easy way to script this?

Make your changes in Enterprise Manager, and then before you save click the icon to script out the changes. Copy the script, cancel your changes, and paster your script in Query Analyzer for editing.

Allow Multiple Parameters form Windows Application

I have been working through a solution to allow multiple parameters to be
passed into a SQL RS report. I have created a windows application that calls
sql reporting services. The problem that I am having now is passing the
multiple values the user selects from the dropdown to sql reporting services.
I am setting: returnValues.Value
When I try to set this parameters to 1;2;3, report fails. However, setting
that value to 1 works.
Is this possbile?RS 2000 does not support multiple selections. For instance,
select * from blah where somefield in (@.Param)
will not work. What you can do is use either an expression or call a stored
procedure that takes the parameter and handles appropriately).
This will work:
= "select * from blah where somefield in (" & Parameters!Paramname.value &
")"
Note that this assume you have dealt with putting in all the proper syntax
like single quotes around charater type parameters, etc and that this will
be a valid query when done.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>I have been working through a solution to allow multiple parameters to be
> passed into a SQL RS report. I have created a windows application that
> calls
> sql reporting services. The problem that I am having now is passing the
> multiple values the user selects from the dropdown to sql reporting
> services.
>
> I am setting: returnValues.Value
> When I try to set this parameters to 1;2;3, report fails. However,
> setting
> that value to 1 works.
> Is this possbile?|||I have been able to get the mulitple selection parameters to work if I call
the SQL RS report from a web page. I basically created dropdown listboxes
and have the form post to the url of the page I want to run. The multiple
selection values are sent to the report and the report works fine. However,
I am trying to call SQL RS report from a Windows Application. I have my
report created in a way that it uses a stored procedure to parse the multiple
values passed to in and joins to those values from a temp table.
"Bruce L-C [MVP]" wrote:
> RS 2000 does not support multiple selections. For instance,
> select * from blah where somefield in (@.Param)
> will not work. What you can do is use either an expression or call a stored
> procedure that takes the parameter and handles appropriately).
> This will work:
> = "select * from blah where somefield in (" & Parameters!Paramname.value &
> ")"
> Note that this assume you have dealt with putting in all the proper syntax
> like single quotes around charater type parameters, etc and that this will
> be a valid query when done.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >I have been working through a solution to allow multiple parameters to be
> > passed into a SQL RS report. I have created a windows application that
> > calls
> > sql reporting services. The problem that I am having now is passing the
> > multiple values the user selects from the dropdown to sql reporting
> > services.
> >
> >
> > I am setting: returnValues.Value
> >
> > When I try to set this parameters to 1;2;3, report fails. However,
> > setting
> > that value to 1 works.
> >
> > Is this possbile?
>
>|||OK, so you are doing the stored procedure method. That a good way to do it.
This whole thing work if from a web page passing in the multiple selections,
the only difference is that you are doing this from a windows app?
How are you integrating your windows app? URL integration or web services?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>I have been able to get the mulitple selection parameters to work if I call
> the SQL RS report from a web page. I basically created dropdown listboxes
> and have the form post to the url of the page I want to run. The multiple
> selection values are sent to the report and the report works fine.
> However,
> I am trying to call SQL RS report from a Windows Application. I have my
> report created in a way that it uses a stored procedure to parse the
> multiple
> values passed to in and joins to those values from a temp table.
> "Bruce L-C [MVP]" wrote:
>> RS 2000 does not support multiple selections. For instance,
>> select * from blah where somefield in (@.Param)
>> will not work. What you can do is use either an expression or call a
>> stored
>> procedure that takes the parameter and handles appropriately).
>> This will work:
>> = "select * from blah where somefield in (" & Parameters!Paramname.value
>> &
>> ")"
>> Note that this assume you have dealt with putting in all the proper
>> syntax
>> like single quotes around charater type parameters, etc and that this
>> will
>> be a valid query when done.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >I have been working through a solution to allow multiple parameters to
>> >be
>> > passed into a SQL RS report. I have created a windows application that
>> > calls
>> > sql reporting services. The problem that I am having now is passing
>> > the
>> > multiple values the user selects from the dropdown to sql reporting
>> > services.
>> >
>> >
>> > I am setting: returnValues.Value
>> >
>> > When I try to set this parameters to 1;2;3, report fails. However,
>> > setting
>> > that value to 1 works.
>> >
>> > Is this possbile?
>>|||I actually need the ability to write the report to a file or display the
report in a web browser on the screen. In both cases I have to set the
Parameter values using an array.
"Bruce L-C [MVP]" wrote:
> OK, so you are doing the stored procedure method. That a good way to do it.
> This whole thing work if from a web page passing in the multiple selections,
> the only difference is that you are doing this from a windows app?
> How are you integrating your windows app? URL integration or web services?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
> >I have been able to get the mulitple selection parameters to work if I call
> > the SQL RS report from a web page. I basically created dropdown listboxes
> > and have the form post to the url of the page I want to run. The multiple
> > selection values are sent to the report and the report works fine.
> > However,
> > I am trying to call SQL RS report from a Windows Application. I have my
> > report created in a way that it uses a stored procedure to parse the
> > multiple
> > values passed to in and joins to those values from a temp table.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS 2000 does not support multiple selections. For instance,
> >> select * from blah where somefield in (@.Param)
> >>
> >> will not work. What you can do is use either an expression or call a
> >> stored
> >> procedure that takes the parameter and handles appropriately).
> >>
> >> This will work:
> >>
> >> = "select * from blah where somefield in (" & Parameters!Paramname.value
> >> &
> >> ")"
> >>
> >> Note that this assume you have dealt with putting in all the proper
> >> syntax
> >> like single quotes around charater type parameters, etc and that this
> >> will
> >> be a valid query when done.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
> >> message
> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >> >I have been working through a solution to allow multiple parameters to
> >> >be
> >> > passed into a SQL RS report. I have created a windows application that
> >> > calls
> >> > sql reporting services. The problem that I am having now is passing
> >> > the
> >> > multiple values the user selects from the dropdown to sql reporting
> >> > services.
> >> >
> >> >
> >> > I am setting: returnValues.Value
> >> >
> >> > When I try to set this parameters to 1;2;3, report fails. However,
> >> > setting
> >> > that value to 1 works.
> >> >
> >> > Is this possbile?
> >>
> >>
> >>
>
>|||What I was trying to clarify is what does and does not work.
It sounds like calling the report from a web page using URL integration does
work. But, you are trying to use web services from your windows app (as an
alternative you can embed an IE control and use URL integration). Since you
say that a single value selected work, I wonder if some seperator character
is causing a problem. In your windows app try having a textbox that you key
in the correct value and see if that works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:A07E6166-50E6-44D8-8ED4-3E3C279EAB1E@.microsoft.com...
>I actually need the ability to write the report to a file or display the
> report in a web browser on the screen. In both cases I have to set the
> Parameter values using an array.
> "Bruce L-C [MVP]" wrote:
>> OK, so you are doing the stored procedure method. That a good way to do
>> it.
>> This whole thing work if from a web page passing in the multiple
>> selections,
>> the only difference is that you are doing this from a windows app?
>> How are you integrating your windows app? URL integration or web
>> services?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>> >I have been able to get the mulitple selection parameters to work if I
>> >call
>> > the SQL RS report from a web page. I basically created dropdown
>> > listboxes
>> > and have the form post to the url of the page I want to run. The
>> > multiple
>> > selection values are sent to the report and the report works fine.
>> > However,
>> > I am trying to call SQL RS report from a Windows Application. I have
>> > my
>> > report created in a way that it uses a stored procedure to parse the
>> > multiple
>> > values passed to in and joins to those values from a temp table.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> RS 2000 does not support multiple selections. For instance,
>> >> select * from blah where somefield in (@.Param)
>> >>
>> >> will not work. What you can do is use either an expression or call a
>> >> stored
>> >> procedure that takes the parameter and handles appropriately).
>> >>
>> >> This will work:
>> >>
>> >> = "select * from blah where somefield in (" &
>> >> Parameters!Paramname.value
>> >> &
>> >> ")"
>> >>
>> >> Note that this assume you have dealt with putting in all the proper
>> >> syntax
>> >> like single quotes around charater type parameters, etc and that this
>> >> will
>> >> be a valid query when done.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >>
>> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >> >I have been working through a solution to allow multiple parameters
>> >> >to
>> >> >be
>> >> > passed into a SQL RS report. I have created a windows application
>> >> > that
>> >> > calls
>> >> > sql reporting services. The problem that I am having now is passing
>> >> > the
>> >> > multiple values the user selects from the dropdown to sql reporting
>> >> > services.
>> >> >
>> >> >
>> >> > I am setting: returnValues.Value
>> >> >
>> >> > When I try to set this parameters to 1;2;3, report fails. However,
>> >> > setting
>> >> > that value to 1 works.
>> >> >
>> >> > Is this possbile?
>> >>
>> >>
>> >>
>>

All about the PIVOT - HELP!!

Hi all,
I have a problem whereby i need to convert multiple row data into a single
row for instance if i have a table
CliID Code V D T
1 A 100
1 B 01/01/06
1 C Beer
2 A 50
2 C Milk
I would want to return this as
CliID A-V A-D A-T B-V B-D B-T C-V C-D C-T
1 100 01/01/06 Beer
2 50 Milk
I have been able to achieve this using the follow code
select CliID,
max(case when code = 'A' then V else 0 end) as A-V,
max(case when code = 'A' then D else null end) as A-D,
max(case when code = 'A' then T else null end) as A-T,
max(case when code = 'B' then V else 0 end) as B-V,
max(case when code = 'B' then D else null end) as B-D,
max(case when code = 'B' then T else null end) as B-T
max(case when code = 'C' then V else 0 end) as C-V,
max(case when code = 'C' then D else null end) as C-D,
max(case when code = 'C' then T else null end) as C-T
from casdet
group by CliID
which works fine however we are now using sql 2005 and i thought it may be
more efficient to make use of the PIVOT function everyone is talking about.
I
can get a basic pivot working for instance pivoting on the code and
sumarising a column say the V column, however i cannot get it to add in the
additional columns.. the code i am currently using looks like this
select CliID, [A] as A, [C] as C
from
(select CliID, code, V, T from casdet) p
Pivot(
max (V)
for code in
( [A], [C])
) as pvt
This pivots the codes and summarised the V column, for all codes. What i
want to do is pivot the codes as column headers then for each code display
the associated V D and T columns all on a single row.
I know this may sound a little confusing so to summarise.. I have a table
with ID, Code, Value, Date, Text columns.. for a given id i need to produce
a
single row where the code and each of its associated Value, Date, and Text
columns appear on a single row. So hopefully my result set would look simila
r
to
code A Code B Code C
ID V D T V D T V D T
1 1 - - - X - - - A
2 4 - C - Z - - - F
I would like to use the pivot command if possible to do this.
Thanks
Iancan you post the ddl, insert script for sample data and the result for the
sample data?|||ok this is going to seem like a very stupid question but how do i do this.
The data i have given in the above is all made up by way of an example. But
in order to assist I have done a select into statement on the live data to
another table. I can script this but when i do so, all i get is the create
table element or the insert script (depending which one i choose) but i
cannot seem to get it to generate a script to create the table with the
data.. I am sure this was possible when i last used SQL several years back.
"Omnibuzz" wrote:

> can you post the ddl, insert script for sample data and the result for the
> sample data?|||Well,
To answer to this post, have a look at this..
http://www.aspfaq.com/etiquette.asp?id=5006
And, anyways, I tried to create the table with the sample data and tried to
get the results.
But looks like you are better off in using your old syntax than using PIVOT.
PIVOT doesn't address this issue you are facing since you are pivoting on th
e
code and grouping on CliId, we it get complicated and results in lots of
nesting..
Anyways if you want to use that then it goes this way.
select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
from casdet pivot (
max(T) for code in ([a],[b],[c])
) as T,
casdet pivot (
max(D) for code in ([a],[b],[c])
) as D,
casdet pivot (
max(V) for code in ([a],[b],[c])
) as V
where t.cliid = d.cliid and d.cliid = v.cliid
group by T.CliID
And it performs slower :)
Hope this helps.|||Thanks for the reply and the info on how to create the DDL and sample data..
It will help a lot. The only reason i wanted to use the PIVOT method over th
e
older way was I figured that with it being a new feature in SQL2005 it would
be optimised and provide better performance than the older way.
Thanks again
Ian
"Omnibuzz" wrote:

> Well,
> To answer to this post, have a look at this..
> http://www.aspfaq.com/etiquette.asp?id=5006
> And, anyways, I tried to create the table with the sample data and tried t
o
> get the results.
> But looks like you are better off in using your old syntax than using PIVO
T.
> PIVOT doesn't address this issue you are facing since you are pivoting on
the
> code and grouping on CliId, we it get complicated and results in lots of
> nesting..
> Anyways if you want to use that then it goes this way.
>
> select T.CliID,max(T.a) as [t-a],max(t.b) as [t-b],max(t.c) as [t-c]
> ,max(D.a) as [D-a],max(d.b ) as [d-b],max(d.c) as [d-c] ,
> max(v.a) as [v-a],max(v.b) as [v-b],max(v.c) as [v-c]
> from casdet pivot (
> max(T) for code in ([a],[b],[c])
> ) as T,
> casdet pivot (
> max(D) for code in ([a],[b],[c])
> ) as D,
> casdet pivot (
> max(V) for code in ([a],[b],[c])
> ) as V
> where t.cliid = d.cliid and d.cliid = v.cliid
> group by T.CliID
> And it performs slower :)
> Hope this helps.
>