Updating two tables so they contain the same data

[johnc83]

Hi all.

would someone be so kind as to help me work out this procedure pls..

I have this update command which runs through all rows in tblTwo and makes the value of field 'NameID' the same as the record in tblOne.

UPDATE [MyDatabase].[dbo].[tblTwo]
   SET NameID = tblOne.NameID
FROM tblTEST
 WHERE tblTwo.JobNo = tblOne.JobNo

I know this has been asked a million times, and I have found some answers, but they are all wide and varying and I was looking for the simplest and most efficient way..

How do I modify this statement so that if the record doesn't exist in tblTwo, then insert it?

Thanks a lot

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[RiverGuy]

Here's a generic method, modify for your particular table/column names.

INSERT INTO Table1
SELECT Table2.*
FROM Table2 a
LEFT OUTER JOIN Table1 b
ON a.Table2Key = b.Table1Key
WHERE b.Table1Key IS NULL

 

[johnc83]

Hi RiverGuy,

thanks for the reply. Unfortunately I am getting an error.

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

Apparantly this means I have specified the correct number of columns? But I've tried and can't work out what is wrong with it.

I only have the two columns in each table (JobNo, NameID)

Any ideas?

Thanks again

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[johnc83]

Sorry, made a minor error.

I had SELECT *
instead of SELECT Table2.*

Works a treat now,

Thanks a lot

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[Naqibuquo]

you could use the following approach as well:

insert into Table1

	select	*
	from	Table2 a
	where	not exists
		(
		 select	*
		 from	Table1 b
		 where	a.Table2Key = b.Table1Key
		)

 

[johnc83]

Hi (again )

My Update command works and your Insert command works but for some reason I cannot get them to work together.

Would it be best to say 'Update all records, if record doesn't exist then insert' or the other way round 'Insert any records which don't exist, then update all ones that do'

UPDATE [MyDatabase].[dbo].[tblTwo]
   SET NameID = test.NameID
FROM tblOne a
 WHERE tblTwo.JobNo = a.JobNo
IF @@ROWCOUNT=0
INSERT INTO [MyDatabase].[dbo].[tblTwo]
SELECT a.*
FROM [MyDatabase].[dbo].[tblOne] a
LEFT OUTER JOIN tblTwo b
ON a.JobNo = b.JobNo
WHERE b.JobNo IS NULL

using that code, all it does is the update, not sure where I am going wrong!

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[SQLSister]

take the row count out.

"NOTHING is more important in a database than integrity." ESquared

 

[memdiscount]

Hi;

This is the need of all developer so that, Microsoft has introduced MERGE statement in SQL SERVER 2008.

MERGE SQL statement

The MERGE statement performs INSERT/UPDATE/DELETE operations on a target table based on the results of a join with a source table.

or

The MERGE statement performs INSERT/UPDATE/DELETE operations on checking whether a row exists and then executing inserts or updates.

MERGE, a very powerful statement to combine insert/updates and even deletes in a single statement

Example from one website:

CREATE TABLE STOCK (STOCK VARCHAR(10) PRIMARY KEY, QTY INT CHECK(QTY > 0))

CREATE TABLE TRADES (STOCK VARCHAR(10) PRIMARY KEY, DELTA INT)

INSERT STOCK VALUES ('MSFT', 10); INSERT STOCK VALUES ('TXN', 5)

INSERT TRADES VALUES ('MSFT', 5); INSERT TRADES VALUES ('TXN', -5);

INSERT TRADES VALUES ('SBUX ', 3)

-- Apply changes to the STOCK table based on daily TRADES tracked in the TRADES table.

-- Delete a row from the STOCK table if all the STOCK has been sold.

-- Update the quantity in the STOCK table if you still hold some STOCK after the daily TRADES.

-- Insert a new row if you acquired a new STOCK.

-- As a result, TXN is deleted, SBUX inserted, MSFT updated

MERGE STOCK S -- target table

USING TRADES T -- source table

ON S.STOCK = T.STOCK

WHEN MATCHED AND (QTY + DELTA = 0) THEN

DELETE -- delete STOCK if entirely sold

WHEN MATCHED THEN

-- update STOCK if you still hold some STOCK

UPDATE SET QTY = QTY + DELTA

WHEN NOT MATCHED THEN

-- insert a row if the STOCK is newly acquired

INSERT VALUES (STOCK, DELTA)

-- output details of INSERT/UPDATE/DELETE operations

-- made on the target table

OUTPUT $action, inserted.STOCK, deleted.STOCK

SELECT * FROM STOCK

GO


I just shared as it comes in the same topic.

Thanks

 

[johnc83]

Hi Memdiscount, thanks for that but being a newbie I am really trying to keep things as small as possible so I can identify problems easier. Appreciate your reply though.

SQLSister, that works now. Thanks very much.

Just a last thought though - Won't taking out the ROWCOUNT reduce performance by making the statement do two things for each row? My command ran super-fast to be fair, on 100,000 records and it just seemed (from what I have read) that ROWCOUNT is something you should include so it doesn't have to bother with the second command?

Don't have to answer that if you don't want - I am just nit-picking!!


Thanks again

John



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[gmmastros]

John,

I think you're thinking like a procedural programmer.

Seriously, though... it's important, when writing SQL Code, to think in sets of data. The thought process should go something like...

1. I have a table that needs to be updated based on another table.
2. I can update ALL of the rows in one table based on matching rows in another table.
3. I can insert ALL of the rows in to one table where they don't exist.

You see... your code is NOT executed for each row in the table(s). With the @@RowCount = 0 line, your code is effectively...

Update data for all rows that match on JobNo
If nothing is updated, then insert missing rows.

If you were looping on each row, then it would make sense to check the ROWCOUNT. However, looping on each row would take MANY times longer than using the set based code.

This is a very important concept, so it's important that it makes sense to you. Does it?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnc83]

Hi George, I think I understand now.

So is it like when is starts the query, it has already separated what should be looked at and what shouldn't be looked at based on the parameters.

i.e it has created two virtual tables (sets of data) and it just ignores the one which doesn't match the parameters.

And it doesn't loop because it is effectively updating one item (The batch update) (instead of my idea of multiple rows/items?)



.NET 2.0, Visual Studio 2005, SQL Server 2005 Express