Checking to see if values are in a table or not -- if not then inserting the values.

I'm trying to checking my production table table_a against a working table table_b (which i'm downlading data to)
Here are the collumns i have in table_a and table_b
Description | FundID (this is not my PK) | Money
I'm running an update if there is already vaule in the money collumn. I check to see if table_a matches table_b...if not i update table a with table b's value where FundID match up.
What i'm having trouble on is if there is no record in table_a but there is a record in table_b. How can I insert that record into table_a? I would like to do all of this (the update and insert statement in one stored proc. if possible. )
If anyone has this answer please let me know.
Thanks,
RB

You'll have to check this before you use it, but this should give you all rows in b that are not in a:
select b.FundID, a.FundID
from b
left join a on a.FundID = b.FundID
where a.FundID is null
Then if this is right, you can write a simple insert statement before it, like this:
INSERT INTO a
select b.Description, b.FundID, b.Money
from b
left join a on a.FundID = b.FundID
where a.FundID is null

|||The select statement looks good however I'm trying to insert into table b so wouldn't it be
INSERT INTO b
...
Just clarify for me if you could.
Thanks for the help
RB|||Oh, if you are inserting INTO b, then you'll just have to reverseeverything I wrote. I thought you were inserting into A what wasin B but not in A.

Checking to see if a records exists before inserting - 3 million + rows

I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.

I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.

To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.

Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.

Any thoughts would be great!

David Wynne wrote:

Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.

You aren't doing a select * against the lookup table, are you?|||Of course not - just pseudo code to get across what I think I'd like to achieve.
|||

Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.

I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.

|||

Charles Talleyrand wrote:

Do you have the ability to push your data into a second table for comparison? You'd then be able to do an outer join based on whatever criteria and then you could pipe those results into your destination component and be far kinder on memory requirements.

I know there are some best practices with regard to using the lookup component to minimize impact but I don't recall those off the top of my head.

Yep, the OP could use an Execute SQL task to load up a temporary table with the keys of the lookup table. Then, in the data flow, you can use the mentioned outer join to capture new records. That is, unless you need to repopulate the lookup table with the incoming "new" records before the next lookup.|||

David,

How many columns do you need to put in the lookup transformation to determine if a record exists? Unless we are taking about too many/wide columns; I don't see several millions to be a problem. Another option is to use partial cache with decent amount of memory; so the chances a row is not in cache is low.

Remember, always provide a query with only the columns that are strictly required for the join

Checking to see if a record exists before inserting

I can't seem to get this work. I'm using SQL2005

I want to check if a record exists before entering it. I just can't figure out how to check it before hand.

Thanks in advance.

Protected Sub BTNCreateProdIDandName_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BTNCreateProdIDandName.Click' Define data objectsDim connAs SqlConnectionDim commAs SqlCommand' Reads the connection string from Web.configDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Initialize connection conn =New SqlConnection(connectionString)' Check to see if the record existsIf comm =New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @.BuilderID) AND (OptionNO = @.OptionNO)", conn)Then'if the record is exists display this message. LBerror.Text ="This item already exists in your Option List."Else'If the record does not exist, add it. FYI - This part works fine by itself. comm =New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@.BuilderID, @.OptionNO, @.OptionName)", conn) comm.Parameters.Add("@.BuilderID", System.Data.SqlDbType.Int) comm.Parameters("@.BuilderID").Value = LBBuilderID.Text comm.Parameters.Add("@.OptionNO", System.Data.SqlDbType.NVarChar) comm.Parameters("@.OptionNO").Value = DDLProdID.SelectedItem.Value comm.Parameters.Add("@.OptionName", System.Data.SqlDbType.NVarChar) comm.Parameters("@.OptionName").Value = DDLProdname.SelectedItem.Value LBerror.Text = DDLProdname.SelectedItem.Value &" was added to your Option List."Try'open connection conn.Open()'execute comm.ExecuteNonQuery()Catch'Display error message LBerror.Text ="There was an error adding this Option. Please try again."Finally'close connection conn.Close()End Try End If End Sub

You need to execute the command to see if the record exists

comm =New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @.BuilderID) AND (OptionNO = @.OptionNO)", conn)

If cbool(comm.executescalar) then

|||

Hello my friend,

Try this in your SQL: -

IF EXISTS (SELECT 1 FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO)
BEGIN
RETURN 'ALREADY_EXISTS'
END
ELSE BEGIN
INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName])
VALUES (@.BuilderID, @.OptionNO, @.OptionName)

RETURN 'INSERT_OKAY'
END

Then execute this with Dim strResult as string = comm.ExecuteScalar(), not ExecuteNonQuery(), and check the strResult string to determine whether or not to display the LBerror text.

If you have any questions on this, please let me know.

Kind regards

Scotty

|||

Scotty,

Thanks for your help. I inserted the sql and I'm getting an error.

Incorrect syntax near ')'.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.

Line 81: Dim strResult As String = comm.ExecuteScalar()

Here is what I'm using.

I don't think I did this right..."Then execute this with Dim strResult as string = comm.ExecuteScalar(), not ExecuteNonQuery(), and check the strResult string to determine whether or not to display the LBerror text. "

Protected

Sub BTN1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles BTN1.Click' Define data objectsDim connAs SqlConnectionDim commAs SqlCommand' Reads the connection string from Web.configDim connectionStringAsString = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Initialize connection

conn =

New SqlConnection(connectionString)' Check to see if the record exists

conn.Open()

comm =

New SqlCommand("IF EXISTS (SELECT 1 FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO) BEGIN()Return 'ALREADY_EXISTS' End Else : BEGIN()INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@.BuilderID, @.OptionNO, @.OptionName)Return 'INSERT_OKAY'End)", conn)

comm.Parameters.Add(

"@.BuilderID", System.Data.SqlDbType.Int)

comm.Parameters(

"@.BuilderID").Value = LBBuilderID.Text

comm.Parameters.Add(

"@.OptionNO", System.Data.SqlDbType.NVarChar)

comm.Parameters(

"@.OptionNO").Value = DDLProdID.SelectedItem.Value

comm.Parameters.Add(

"@.OptionName", System.Data.SqlDbType.NVarChar)

comm.Parameters(

"@.OptionName").Value = DDLProdname.SelectedItem.ValueDim strResultAsString = comm.ExecuteScalar()

' I'm not sure what to do here. to get my error message to show.

LBerror.Text = strResult.ToString

conn.Close()

EndSub

|||

Option 1

=====

You can modify the query as

SELECT COUNT(*) as CountOfRecords

FROM

FROM optionlist WHERE BuilderID = @.BuilderID AND OptionNO = @.OptionNO

If the Count is greater than 0 then you know the record exists

Option2

=======

Wrap Scotty's SQL in a Stored procedure and call the SP. I would do this way. SPs are fast and adds alayer of abstraction.

|||

rednelo:

Scotty,

Thanks for your help. I inserted the sql and I'm getting an error.

Incorrect syntax near ')'.
A RETURN statement with a return value cannot be used in this context.
A RETURN statement with a return value cannot be used in this context.

Line 81: Dim strResult As String = comm.ExecuteScalar()

That because you added a set of parentheses following the BEGIN statement that Scott did not have in his supplied code. Remove those and you should have better luck.

|||

I removed the parentheses. Don't know how they got there...

I also tired putting it in a sproc, but I keep getting the same message.

Msg 178, Level 15, State 1, Line 3

A RETURN statement with a return value cannot be used in this context.

Msg 178, Level 15, State 1, Line 9

A RETURN statement with a return value cannot be used in this context.

|||

rednelo:

I removed the parentheses. Don't know how they got there...

I also tired putting it in a sproc, but I keep getting the same message.

Msg 178, Level 15, State 1, Line 3

A RETURN statement with a return value cannot be used in this context.

Msg 178, Level 15, State 1, Line 9

A RETURN statement with a return value cannot be used in this context.

A RETURN statement can only return an integer value. Scotty made a typo in his original code. Use SELECT instead:

SELECT 'ALREADY_EXISTS'

and

SELECT 'INSERT_OKAY'

|||

Those little typos can really cause one to pull their hair out! 4 hours later... We finally got it.

Thank-you for your help this works well.

Checking for duplicate row before inserting

I need to know how to check/compareall fields in a row based on Cust Id before inserting a new record. As it is right now, if the Add/Update button is pressed continually, it increments the record number and adds a duplicate record. And you know you'll never stop people from double clicking a button that only needs to be clicked once.

I thought about just disabling the Update button after the insert occurs, then re-enable it if they go back and edit a field, but that's not a solid solution.I'd do it in your stored procedure.

You've got something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
GO

I'd change it to something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
IF EXISTS
 (SELECT CustID FROM CustTable
 WHERE SomeField = @.OneVal AND OtherField = @.OtherVal)
 BEGIN
 RETURN
 END
ELSE
 BEGIN
 INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
 END
GO

That's pretty simple, obviously...You'll likely want to return something if a match is found, or even if the new record gets inserted...But it shows you the basics, I hope.

Regards,

Xander|||That looks like what I'm looking for. Since the add buttons click event runs 5 functions to insert data into other tables; if I call the SP in the first function, that should intercept the insertion process all together and break right?

InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

I suppose that I could code a boolean to catch if the SP returns a duplicate using True/False, then use it to display a message in my errmsg label; notifying the user that they have attempted to store a duplicate record.

Thanks for the help, any other info would be greatly appreciated.
PD|||InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

No, the fact that a record wasn't inserted will not, in and of itself prevent the other functions from firing.

You've got the idea though...If you pass a boolean value back, you can use it to decide whether to fire the other functions or not.

You could pass an output parameter from the stored procedure, for instance, or return a scalar value that lets you know what the stored procedure did, and wrap your four subsequent functions in an IF block, (perhaps even inside the first function on successful insertion?) and that should give you what you want.|||I got it to work, but it does prevent the updating of the fields that are not part of the SP in one of my functions (Strange).

I am going to check it out. I thought about appending the table to store all the field data and re-code the SP to check all the fields, fire it first, and then let it yield to the other functions if no duplicate exists.

Thanks.|||Well, it has turned into a proverbial nightmare. If I yield to the other insert functions from my SP, then duplicate records can be created in those tables. So I guess I am going to have to write SP's for each of those functions to prevent duplicates there as well.

I inherited this app a couple of months ago from an outside firm, and what a mess it has been.

Checking existence with insert... select OPENXML

Hi,
If I am inserting data into a table and I do not wish to insert if the row is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form that I can use to test for existence of the row in the destination table as I did above. I can always insert into a temp table of course and then use that as the source of the da
ta for the insert and use the first syntax form. But I was just curious whether this can be easily achieved without the use of a temp table.
Cheers
Ken
Yes, it is possible. You just need to add an alias to the OPENXML derived
table. For example:
INSERT INTO YourTable(CustomerID, ContactName)
SELECT CustomerID, ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)) AS XM
WHERE NOT EXISTS
(
SELECT 1
FROM YourTable AS x
WHERE x.CustomerID = XM.CustomerID
)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
Hi,
If I am inserting data into a table and I do not wish to insert if the row
is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form
that I can use to test for existence of the row in the destination table as
I did above. I can always insert into a temp table of course and then use
that as the source of the data for the insert and use the first syntax form.
But I was just curious whether this can be easily achieved without the use
of a temp table.
Cheers
Ken
|||Many thanks
Ken
"Narayana Vyas Kondreddi" wrote:

> Yes, it is possible. You just need to add an alias to the OPENXML derived
> table. For example:
> INSERT INTO YourTable(CustomerID, ContactName)
> SELECT CustomerID, ContactName
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20)) AS XM
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM YourTable AS x
> WHERE x.CustomerID = XM.CustomerID
> )
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
> news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
> Hi,
> If I am inserting data into a table and I do not wish to insert if the row
> is already there I can write:
>
> insert into t1 (c1,c2,c3)
> select c1,c2,c3 from t2
> where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
> c3=t2.c3)
> If my source query for the insert uses OPENXML is there any syntactical form
> that I can use to test for existence of the row in the destination table as
> I did above. I can always insert into a temp table of course and then use
> that as the source of the data for the insert and use the first syntax form.
> But I was just curious whether this can be easily achieved without the use
> of a temp table.
> Cheers
> Ken
>
>

Check value type before inserting into a table

Is there a way to check the type of a value before inserting the value into
the table? I am working with sql 7 and I need to verify the input values to
a table before inserting them.
For example...
table A, field1 is an integer field type
default value=0;
value//input value from application
if value (future value) is an integer to be inserted into field1 then
insert(value)
else
insert(default value)
This is just a simple logic procedure I want the trigger to check before ins
erting or updating field1You could use isnumeric, e.g.
DECLARE @.def INT
SET @.def = 0
INSERT tbl(col) SELECT CASE WHEN ISNUMERIC(@.param)=1 THEN @.param ELSE @.def
END
Though mind that IsPerfect(IsNumeric())=0.
http://www.aspfaq.com/2390
http://www.aspfaq.com/
(Reverse address to reply.)
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1|||You could use the CASE statement in an insert statement:
create table #test(c char(1), i int)
declare @.x varchar(10)
set @.x = 1
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
set @.x = 'xxx'
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
select * from #test
drop table #test
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1

Check value type before inserting into a table

Is there a way to check the type of a value before inserting the value into the table? I am working with sql 7 and I need to verify the input values to a table before inserting them
For example..
table A, field1 is an integer field typ
default value=0
value//input value from applicatio
if value (future value) is an integer to be inserted into field1 the
insert(value
els
insert(default value
This is just a simple logic procedure I want the trigger to check before inserting or updating field1You could use isnumeric, e.g.
DECLARE @.def INT
SET @.def = 0
INSERT tbl(col) SELECT CASE WHEN ISNUMERIC(@.param)=1 THEN @.param ELSE @.def
END
Though mind that IsPerfect(IsNumeric())=0.
http://www.aspfaq.com/2390
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1|||You could use the CASE statement in an insert statement:
create table #test(c char(1), i int)
declare @.x varchar(10)
set @.x = 1
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
set @.x = 'xxx'
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
select * from #test
drop table #test
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1

Check value type before inserting into a table

Is there a way to check the type of a value before inserting the value into the table? I am working with sql 7 and I need to verify the input values to a table before inserting them.
For example...
table A, field1 is an integer field type
default value=0;
value//input value from application
if value (future value) is an integer to be inserted into field1 then
insert(value)
else
insert(default value)
This is just a simple logic procedure I want the trigger to check before inserting or updating field1
You could use isnumeric, e.g.
DECLARE @.def INT
SET @.def = 0
INSERT tbl(col) SELECT CASE WHEN ISNUMERIC(@.param)=1 THEN @.param ELSE @.def
END
Though mind that IsPerfect(IsNumeric())=0.
http://www.aspfaq.com/2390
http://www.aspfaq.com/
(Reverse address to reply.)
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1
|||You could use the CASE statement in an insert statement:
create table #test(c char(1), i int)
declare @.x varchar(10)
set @.x = 1
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
set @.x = 'xxx'
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
select * from #test
drop table #test
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1