Help with an Update/Insert procedure

Hello,
I'm new to SQL in general and I'm looking for the most efficient stored
procedure that can accomplish the following:
If aRecord with field "RecID" = # is found in aTable then
{
If aRecord's field "Updateable"=True then
UPDATE aRecord...
}
Else
{
INSERT aNewRecord into aTable ...
}
Basically, I'm submitting the field parameters for a record to a table in
the stored procedure. If a record already exists with the same primary key
in that table , then, if a field's value in that existing record isn't
"False", update it. Otherwise, if there is no record with that primary key
value, then insert this new record into the table.
Thanks and let me know if this doesn't make any sense!
WykAssuming "RecID" (what a horrible column name!) is a primary key, probably
most efficient is to try to update it first, and if it didn't work, insert.
UPDATE ... WHERE RecID = @.RecID
IF @.@.ROWCOUNT = 0
INSERT ...
You could do something slightly more elaborate, to make the code more
readable, with negligible performance differences:
IF EXISTS (SELECT 1 FROM table WHERE RecID = @.RecID)
UPDATE ...
ELSE
INSERT ...
You're going to have to test in your own environment to be sure, of course.
Most efficient on my schema is not necessarily most efficient on your
schema.
"Wyk" <wykananda@.hotmail.com> wrote in message
news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I'm new to SQL in general and I'm looking for the most efficient stored
> procedure that can accomplish the following:
>
> If aRecord with field "RecID" = # is found in aTable then
> {
> If aRecord's field "Updateable"=True then
> UPDATE aRecord...
> }
> Else
> {
> INSERT aNewRecord into aTable ...
> }
> Basically, I'm submitting the field parameters for a record to a table in
> the stored procedure. If a record already exists with the same primary key
> in that table , then, if a field's value in that existing record isn't
> "False", update it. Otherwise, if there is no record with that primary
> key value, then insert this new record into the table.
> Thanks and let me know if this doesn't make any sense!
> Wyk
>
>|||Here's what I've tried which doesn't seem to work though it compiles...
PROCEDURE [dbo].[p_MergeRecordIntoTable]
(
@.Id bigint,
@.Field1 nvarchar(50)
@.Updateable bit
)
AS
BEGIN
SET NOCOUNT ON
SELECT Count(*)
FROM [aTable]
WHERE ( Id= @.Id)
IF @.@.ROWCOUNT != 0
BEGIN
UPDATE [MergeTable]
SET
Field1= @.Field1
WHERE
( Id= @.Id )
AND
( Updateable = 1 )
END
ELSE
BEGIN
INSERT INTO [SI_MLB_Games] (
ID,
Field1,
Updateable,
VALUES (
@.Id,
@.Field1,
@.Updateable)
END
END
"Wyk" <wykananda@.hotmail.com> wrote in message
news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I'm new to SQL in general and I'm looking for the most efficient stored
> procedure that can accomplish the following:
>
> If aRecord with field "RecID" = # is found in aTable then
> {
> If aRecord's field "Updateable"=True then
> UPDATE aRecord...
> }
> Else
> {
> INSERT aNewRecord into aTable ...
> }
> Basically, I'm submitting the field parameters for a record to a table in
> the stored procedure. If a record already exists with the same primary key
> in that table , then, if a field's value in that existing record isn't
> "False", update it. Otherwise, if there is no record with that primary
> key value, then insert this new record into the table.
> Thanks and let me know if this doesn't make any sense!
> Wyk
>
>|||Aaron, I was able to make it work perfectly with the your second example.
Not sure how I could make it work with the first though.
Big thanks!
Wyk
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OErAwLAPGHA.1288@.TK2MSFTNGP09.phx.gbl...
> Assuming "RecID" (what a horrible column name!) is a primary key, probably
> most efficient is to try to update it first, and if it didn't work,
> insert.
> UPDATE ... WHERE RecID = @.RecID
> IF @.@.ROWCOUNT = 0
> INSERT ...
> You could do something slightly more elaborate, to make the code more
> readable, with negligible performance differences:
> IF EXISTS (SELECT 1 FROM table WHERE RecID = @.RecID)
> UPDATE ...
> ELSE
> INSERT ...
> You're going to have to test in your own environment to be sure, of
> course. Most efficient on my schema is not necessarily most efficient on
> your schema.
>
>
> "Wyk" <wykananda@.hotmail.com> wrote in message
> news:%23aYKY$$OGHA.2604@.TK2MSFTNGP09.phx.gbl...
>

Help with a Query

Am I able to do something like this? The syntax below is obviously
wrong, I'm looking to see if there is a way to accomplish this.
DECLARE @.DBName varchar(5)
SET @.DBName = 'pubs'
SELECT * FROM @.DBName.dbo.authors
I have a master application and database containing data for several
companies. Each company uses a certain web-based sales software. I have
a local copy of those databases. Each of those databases is identical
in structure, they just contain each company's data.
I want to use stored procedures in my main database to run reports on
the data in each of the databases. I don't want to copy my stored
procedures into each database. Instead I want to call them from the
main database, and within the stored procedure, decide on which
database to run the t-sql.
Thanks!"George" <george.durzi@.gmail.com> wrote in message
news:1134592425.040038.242050@.z14g2000cwz.googlegroups.com...
> Am I able to do something like this? The syntax below is obviously
> wrong, I'm looking to see if there is a way to accomplish this.
> DECLARE @.DBName varchar(5)
> SET @.DBName = 'pubs'
> SELECT * FROM @.DBName.dbo.authors
> I have a master application and database containing data for several
> companies. Each company uses a certain web-based sales software. I have
> a local copy of those databases. Each of those databases is identical
> in structure, they just contain each company's data.
> I want to use stored procedures in my main database to run reports on
> the data in each of the databases. I don't want to copy my stored
> procedures into each database. Instead I want to call them from the
> main database, and within the stored procedure, decide on which
> database to run the t-sql.
> Thanks!
>
You will need to use Dynamic SQL.
Try the following:
EXECUTE ('SELECT * FROM ' + @.DBName + '.dbo.authors')
Rick Sawtell
MCT, MCSD, MCDBA|||Rick, the only issue with that is I actually am gonna be writing a
complex stored procedure.
It looks like dynamic sql is going to have to be the way to go though.|||Before diving into the dynamicity pool and drowning, take look up
"distributed partitioned views" in Books Online.
I'd create an extra database and use it as a central reporting data source.
ML
http://milambda.blogspot.com/

Help using Lookup Transformation

Hi All,

I am doing something really simple and it doesnt work, may be I am missing something, What I am trying to accomplish is to load a fact table using lookup transaformation, however my source data was different from the data in my dimension (or the datatype ) I had to use a data conversion task before my lookup , so the data flow is something like this source -> Data Conversion -> Lookup -> destination , I am getting an error at my lookup task where it says the "[Lookup [82]] Error: Row yielded no match during lookup". and then it just fails. I know for sure that there has to be matching data. donno what is it that I am missing.

Thanks

Things to look out for...

Trailing spaces on either side of the lookup (source data or lookup data). If one has trailing spaces and the other doesn't, it won't match.

CaSE. The lookups are case sensitive.|||

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

|||

db_guy wrote:

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

Redirect the errors (error output, red arrow) to a flat file source, OLE DB destination, or even a Row Count Transformation, and add a data viewer to inspect the data that does not match.|||

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

|||

db_guy wrote:

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

Add a data viewer to the red arrow connecting the lookup to the row count. (Double click on it and select data viewers, then add a new data viewer)|||

Hi,

I have attached data viewers and one thing I noticed is they behave in a weird way, sometimes they have 0 records and sometimes they have data, donno why.I have three lookups that I use , and then finally they all load the fact table

Thanks

|||Connect all lookup error flows to a Union All component, and then to a row counter. Add the data viewer between the union all and the row counter. This will let you see all of the lookup error outputs.

Or, just hook each lookup up to their own row counter and add data viewers there. You're data is getting caught up somewhere.|||

I think using the data viewers I have narrowed down the problem thanks for that, however I have another question how do I do lookup where I have a join which is something like this

INNER JOIN dbo.Dim_Time T WITH (NOLOCK)

ON SUBSTRING(T.MonthYear,1,3) = SUBSTRING(s.Fiscal_Time,1,3)

AND SUBSTRING(T.MonthYear,6,2) = SUBSTRING(s.Fiscal_Time,6,2)

The problem is I was using derived column to create/replace the stage column using the substring function , however I cannot do that twice because derived column cannot be used twice for the same column, any suggestions?

Thanks

|||

Where did you get that you can't use a derived column twice for the same column?

If you are having a problem with your lookup you can always use a Source / merge join transform, or you can go to the advanced tab of the Lookup transform and select enable memory restriction and type in your exact query with parameters and line it up that way (This is much slower than using it in the regular lookup mode)

If you were to give more info we could probably tell you the exact steps needed to complete this process.

(also, if your original question was answered, remember to mark it as such, along with all other applicable answers)