Update, insert or delete? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update, insert or delete?

Author

Topic

pirre001
Starting Member

11 Posts

Posted - 2010-11-08 : 06:19:15

I have two identical tables. Table tblStorage has data from another system as we import every 15 min.
But before the table tblStorge gets the data from the other system, it imported to a temporary table called tmptblStorage.

My question is how I can do this on a easy way?
1. Update tblStorage with the rows has changed in field Quantity.
2. Delete rows from tblStorage as not match in fields Storage_ID and/or Material_ID in tmptblStorage?
3. Insert new rows from table tmptblStorage as not found in fields Storage_ID and /or Material_ID in table tblStorage?


CREATE TABLE [tblStorage](

[Storage_ID] [uniqueidentifier] NOT NULL,

[Quantity] [decimal](18, 5) NOT NULL,

[Material_ID] [uniqueidentifier] NOT NULL

(



CREATE TABLE [tmptblStorage](

[Storage_ID] [uniqueidentifier] NOT NULL,

[Quantity] [decimal](18, 5) NOT NULL,

[Material_ID] [uniqueidentifier] NOT NULL

(

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 06:34:27

What do you mean by "easy way"?
Everything is "easy' once you know and understand how to do things.

PBUH

pirre001
Starting Member

11 Posts

Posted - 2010-11-08 : 06:56:11

quote:
Originally posted by Sachin.Nand

What do you mean by "easy way"?
Everything is "easy' once you know and understand how to do things.

PBUH

I mean, what is the best way to do this, and of course, I want to learn me also...

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 07:33:09

quote:

1. Update tblStorage with the rows has changed in field Quantity.

Update tblStorage join with tmptblStorage on Storage_ID which have been changed in last 15 minutes if my understanding is correct.

quote:

2. Delete rows from tblStorage as not match in fields Storage_ID and/or Material_ID in tmptblStorage?

Write a delete statement which uses not exists subquery to check whether Storage_ID and/or Material_I exists

quote:

3. Insert new rows from table tmptblStorage as not found in fields Storage_ID and /or Material_ID in table tblStorage?

Use a insert statement which uses not exists subquery to check whether Storage_ID and/or Material_I exists

If you want a better answer you need to post some sample data and expected output for both the tables and all the conditions.

PBUH

pirre001
Starting Member

11 Posts

Posted - 2010-11-08 : 10:07:14

quote:
Originally posted by Sachin.Nand

quote:

1. Update tblStorage with the rows has changed in field Quantity.

Update tblStorage join with tmptblStorage on Storage_ID which have been changed in last 15 minutes if my understanding is correct.

quote:

2. Delete rows from tblStorage as not match in fields Storage_ID and/or Material_ID in tmptblStorage?

Write a delete statement which uses not exists subquery to check whether Storage_ID and/or Material_I exists
quote:

3. Insert new rows from table tmptblStorage as not found in fields Storage_ID and /or Material_ID in table tblStorage?

Use a insert statement which uses not exists subquery to check whether Storage_ID and/or Material_I exists

If you want a better answer you need to post some sample data and expected output for both the tables and all the conditions.

PBUH

Ok, lets start with update. Can I do like this?


UPDATE tblStorage 
LEFT JOIN tmptblStorage ON (tblStorage.Storage_ID = tmptblStorage.Storage_ID) 
AND (tblStorage.Material_ID = tmptblStorage.Material_ID) 
SET tblStorage.Quantity = tmptblStorage.Quantity
WHERE tblStorage.Quantity <> tmptblStorage.Quantity;

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 10:18:55

UPDATE tblStorage
SET tblStorage.Quantity = tmptblStorage.Quantity
FROM tblStorage
LEFT JOIN tmptblStorage ON (tblStorage.Storage_ID = tmptblStorage.Storage_ID)
AND (tblStorage.Material_ID = tmptblStorage.Material_ID)
WHERE tblStorage.Quantity <> tmptblStorage.Quantity;

PBUH

pirre001
Starting Member

11 Posts

Posted - 2010-11-08 : 10:32:02

quote:
Originally posted by Sachin.Nand

UPDATE tblStorage
SET tblStorage.Quantity = tmptblStorage.Quantity
FROM tblStorage
LEFT JOIN tmptblStorage ON (tblStorage.Storage_ID = tmptblStorage.Storage_ID)
AND (tblStorage.Material_ID = tmptblStorage.Material_ID)
WHERE tblStorage.Quantity <> tmptblStorage.Quantity;

PBUH

Thanks!

I stuck with the Delete statement. Have just come to this...


SELECT tblStorage.Material_ID, tblStorage.Storage_ID
FROM tblStorage 
RIGHT JOIN tmptblStorage ON 
(tblStorage.Storage_ID = tmptblStorage.Storage_ID) 
AND (tblStorage.Material_ID = tmptblStorage.Material_ID)
WHERE tmptblStorage.Material_ID) Is Null;

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 12:06:42

[code]

DELETE FROM tblStorage
WHERE NOT EXISTS
(SELECT * from tmptblStorage WHERE tblStorage.Storage_ID = tmptblStorage.Storage_ID AND (tblStorage.Material_ID = tmptblStorage.Material_ID) )

[/code]

PBUH

pirre001
Starting Member

11 Posts

Posted - 2010-11-09 : 02:17:01

quote:

Originally posted by Sachin.Nand



DELETE FROM tblStorage 
WHERE NOT EXISTS
(SELECT * from tmptblStorage WHERE tblStorage.Storage_ID = tmptblStorage.Storage_ID AND (tblStorage.Material_ID = tmptblStorage.Material_ID) )

PBUH

Thanks again. So insert statement could look like this?


INSERT INTO tblStorage ( Material_ID, Storage_ID )
SELECT tblStorage.Material_ID, tblStorage.Storage_ID
FROM tblStorage RIGHT JOIN tmptblStorage ON (tblStorage.Storage_ID = tmptblStorage.Storage_ID) 
AND (tblStorage.Material_ID = tmptblStorage.Material_ID)
WHERE tblStorage.Material_ID Is Null;

By the way, how can I do these three steps in same statement?

Sachin.Nand

2937 Posts

Posted - 2010-11-09 : 02:22:10

Your Insert statement looks good to go.
Also you want to Insert,Update and Delete in one SQL statement?
I think you can use Merge clause if using SQL 2008.But I am not quite that familiar with Merge.

PBUH

pirre001
Starting Member

11 Posts

Posted - 2010-11-09 : 06:16:35

quote:
Originally posted by Sachin.Nand

Your Insert statement looks good to go.
Also you want to Insert,Update and Delete in one SQL statement?
I think you can use Merge clause if using SQL 2008.But I am not quite that familiar with Merge.

PBUH

Thats right, I wanna do these three steps in same statement. maybe someone else can show me how I can use Merge?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 06:19:58

Are you using SQL Server 2008?
If not - and you have posted in 2005 forum - then there is no way to do it using MERGE statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

pirre001
Starting Member

11 Posts

Posted - 2010-11-09 : 06:47:50

quote:
Originally posted by webfred

Are you using SQL Server 2008?
If not - and you have posted in 2005 forum - then there is no way to do it using MERGE statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Aaah, I'm using SQL Server 2005...

Sachin.Nand

2937 Posts

Posted - 2010-11-09 : 10:56:11

quote:
Originally posted by pirre001

quote:
Originally posted by webfred

Are you using SQL Server 2008?
If not - and you have posted in 2005 forum - then there is no way to do it using MERGE statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Aaah, I'm using SQL Server 2005...

To bad...

But I would really like that someone should show how it can be done using merge for SQL 2008.

PBUH

pirre001
Starting Member

11 Posts

Posted - 2010-11-10 : 02:34:10

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by pirre001

quote:
Originally posted by webfred

Are you using SQL Server 2008?
If not - and you have posted in 2005 forum - then there is no way to do it using MERGE statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Aaah, I'm using SQL Server 2005...

To bad...

But I would really like that someone should show how it can be done using merge for SQL 2008.

PBUH

Me to...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources