• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Compare data in two tables

In SQL Server 2008 R2 I have a stored procedure where I can pass in an entire table from VB web page that I want to compare to existing table.  In both tables
col1 is a date column
col2 is a money column

I need to compare the inbound table with the sqlTable

if inbound count(*) > sqlTable count(*) return a fail message

inbound col1 doesn't exist in sqlTable return a fail message
if col1 exists in both tables
inbound.col1 must be < = sqlTable.col1 or return fail message

If nofails...simply return an okee-dokee message
0
lrbrister
Asked:
lrbrister
1 Solution
 
TempDBACommented:
So, you have the logic. What you want to ask? can you make it more clear?
0
 
lrbristerAuthor Commented:
TempDBA:
how do I cycle through that and grab the first incorrect record and return it with a fail status.
0
 
Anthony PerkinsCommented:
Please post the schema of the table.
Do you have a Primary Key on the table?  If so indicate what is/are the field(s)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
lrbristerAuthor Commented:
acperkins

The user-defined table type is attached.

The inbound table is the same structure

if inbound count(*) > sqlTable count(*) return a fail message

inbound startDatedoesn't exist in sqlTable return a fail message
if startDate exists in both tables
inbound.PmtAmount must be < = tbltypTV5Pmts.PmtAmount or return fail message


The inbound table gets passed in with...

ALTER PROCEDURE [dbo].[sp_z_test] (
                              @parentId      INT
                              ,@tv5Pmts      AS tbltypTV5PMTS readonly)
AS
etc...
CREATE TYPE [dbo].[tbltypTV5Pmts] AS TABLE(
	[EventType] [varchar](25) NULL,
	[StartDate] [datetime] NULL,
	[PmtAmount] [money] NULL,
	[NumPmts] [smallint] NULL,
	[PmtFrequency] [varchar](15) NULL,
	[EndDate] [datetime] NULL,
	[PercentIncrease] [numeric](7, 4) NULL,
	[FixedIncrease] [money] NULL,
	[NumberPmtsBeforeChange] [smallint] NULL
)
GO

Open in new window

0
 
millsap_singerCommented:
If you truly want to cycle through then you can use a cursor.  

--Declare your row variables.
DECLARE EventType VARCHAR(25)
DECLARE StartDate DATETIME

--etc.

DECLARE curPayments CURSOR LOCAL STATIC
FOR SELECT EventType, StartDate FROM dbo.tbltypTV5Pmts ORDER BY StartDate

OPEN curPayments

FETCH NEXT FROM curPayments
INTO @EventType, @Start Date

WHILE @@FETCH_STATUS = 0
BEGIN

-- Do your logic here, inserting, returning rows, whatever.


--Get Next row of data.
FETCH NEXT FROM curPayments
INTO @EventType, @Start Date

END --looping through cursor

CLOSE curPayments
DEALLOCATE curPayments

-- do additional work...

Open in new window

0
 
Anthony PerkinsCommented:
I am not sure if you saw this:
Do you have a Primary Key on the table?  If so indicate what is/are the field(s)
0
 
lrbristerAuthor Commented:
acperkins:
Not usre how the Primary key would help

The inbound table from the table tyle variable has no primary ke

The existing table does...id.  It is an integer seed=1000 increment=1
0
 
Anthony PerkinsCommented:
>>Not usre how the Primary key would help<<
I would use it to compare the tables.  But if you know of a better way, that is fine too.
0
 
lrbristerAuthor Commented:
THis is a start.  Thanks
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now