SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 combine scripts
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 08/28/2008 :  16:29:45  Show Profile  Reply with Quote
i have 2 tables: table1, table2
I have 2 script:
- one script inserts the new records from table2 to table1 if they dont exists.
- one script update the records in table1 with the new info from table2

i want to combine the 2 scripts with an if statement. How can i do it? If you are not familir with this task. do not reply.

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 08/28/2008 :  16:38:19  Show Profile  Visit tkizer's Homepage  Reply with Quote

IF ...
  Do something
ELSE
  Do something else


If you want better help from us, you need to provide better information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/28/2008 :  16:41:26  Show Profile  Reply with Quote
If he wants help from me, he's going to need to be more polite to YOU than he has on past threads.

Boycotted Beijing Olympics 2008
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  16:45:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Please, experts only...




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  16:46:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Have a look at the MERGE command in SQL Server 2008.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 08/28/2008 :  16:50:57  Show Profile  Reply with Quote
You are wasting your time and my time
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  16:52:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Now you don't read valuable suggestions?
Have a look at the MERGE command availabe in SQL Server 2008.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/28/2008 :  16:54:59  Show Profile  Reply with Quote
Look at Business Intelligence features in SQL 2005

1)Merge transformation
2)Sort transformation
3)Look up transformation
4)Split transformation
then you will get results.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  16:58:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think you need to run BOTH scripts. Then you
1) Update all existing records
2) Insert all non-existing records

which is what most people would do.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 08/28/2008 :  17:00:36  Show Profile  Reply with Quote
got it

DECLARE @computersNV table (computerID int primary key, computerName varchar(50), computerPrice varchar(30), computerCheckDate datetime)
INSERT @computersNV
SELECT 1, 'Rose', $1, CONVERT(VARCHAR(8), GETDATE()-5, 112) UNION ALL
-- SELECT 2, 'Clark', $5, CONVERT(VARCHAR(8), GETDATE()-5, 112) UNION ALL
SELECT 4, 'John', $8 , CONVERT(VARCHAR(8), GETDATE()-5, 112)

DECLARE @computersCA table (computerID int, computerName varchar(50), computerPrice money, computerCheckDate datetime)
INSERT @computersCA
SELECT 1, 'Rose', $5.6, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT 2, 'Clark', $3.4, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT 3, 'Marilyn', $6, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT 4, 'John', $8, CONVERT(VARCHAR(8), GETDATE(), 112)

SELECT * FROM @computersNV
SELECT * FROM @computersCA

IF NOT EXISTS(SELECT 1 FROM @computersNV WHERE COMPUTERID = 2)
BEGIN
INSERT @computersNV
SELECT CA.* FROM @computersCA CA
LEFT JOIN @computersnv NV
ON CA.computerID = NV.computerID
WHERE nv.computerID IS NULL
AND ca.computerid = 2
END
ELSE
BEGIN
DECLARE @CID INT
SET @CID = 2

UPDATE @computersNV
SET NV.ComputerPrice = CA.ComputerPrice,
NV.ComputerName = CA.ComputerName,
NV.Computercheckdate = CA.Computercheckdate
FROM @computersNV NV
JOIN @computersCA CA
ON NV.COMPUTERID = CA.COMPUTERID
AND NV.COMPUTERID = @CID
END
SELECT * FROM @computersNV
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  17:04:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You want to insert only the records for ComputerID 2 it they do not exist, and you want to update all other records in case there is at least one present record for ComputerID 2???

Makes no sense whatsoever.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 08/28/2008 :  17:05:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why didn't you post the script in the first place, even if it wasn't working yet? Why post a question with such limited information?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 08/28/2008 :  17:08:25  Show Profile  Reply with Quote
HOW ABOUT NOW?

DECLARE @COMPUTERID INT
SET @COMPUTERID = 2 --- PARAMETER TO INSERT OR UPDATE THE RECORDS FROM THE 2ND TABLE. CAN HAVE ANY PARAMETER.


DECLARE @computersNV table (computerID int primary key, computerName varchar(50), computerPrice varchar(30), computerCheckDate datetime)
INSERT @computersNV
SELECT 1, 'Rose', $1, CONVERT(VARCHAR(8), GETDATE()-5, 112) UNION ALL
-- SELECT 2, 'Clark', $5, CONVERT(VARCHAR(8), GETDATE()-5, 112) UNION ALL
SELECT 4, 'John', $8 , CONVERT(VARCHAR(8), GETDATE()-5, 112)

DECLARE @computersCA table (computerID int, computerName varchar(50), computerPrice money, computerCheckDate datetime)
INSERT @computersCA
SELECT 1, 'Rose', $5.6, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT 2, 'Clark', $3.4, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT 3, 'Marilyn', $6, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT 4, 'John', $8, CONVERT(VARCHAR(8), GETDATE(), 112)

SELECT * FROM @computersNV
SELECT * FROM @computersCA

IF NOT EXISTS(SELECT 1 FROM @computersNV WHERE COMPUTERID = @COMPUTERID)
BEGIN
INSERT @computersNV
SELECT CA.* FROM @computersCA CA
LEFT JOIN @computersnv NV
ON CA.computerID = NV.computerID
WHERE nv.computerID IS NULL
AND ca.computerid = 2
END
ELSE
BEGIN
DECLARE @CID INT
SET @CID = @COMPUTERID

UPDATE @computersNV
SET NV.ComputerPrice = CA.ComputerPrice,
NV.ComputerName = CA.ComputerName,
NV.Computercheckdate = CA.Computercheckdate
FROM @computersNV NV
JOIN @computersCA CA
ON NV.COMPUTERID = CA.COMPUTERID
AND NV.COMPUTERID = @CID
END
SELECT * FROM @computersNV
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/28/2008 :  17:10:27  Show Profile  Reply with Quote
see this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109666
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  17:12:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@computersNV TABLE
	(
		computerID int primary key,
		computerName varchar(50),
		computerPrice varchar(30),
		computerCheckDate datetime
	)

INSERT	@computersNV
SELECT	1, 'Rose', $1, CONVERT(VARCHAR(8), GETDATE()-5, 112) UNION ALL
SELECT	4, 'John', $8 , CONVERT(VARCHAR(8), GETDATE()-5, 112)

DECLARE	@computersCA TABLE
	(
		computerID int,
		computerName varchar(50),
		computerPrice money,
		computerCheckDate datetime
	)

INSERT	@computersCA
SELECT	1, 'Rose', $5.6, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT	2, 'Clark', $3.4, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT	3, 'Marilyn', $6, CONVERT(VARCHAR(8), GETDATE(), 112) UNION ALL
SELECT	4, 'John', $8, CONVERT(VARCHAR(8), GETDATE(), 112)

SELECT * FROM @computersNV

-- Update all existing records
UPDATE		nv
SET		nv.ComputerPrice = ca.ComputerPrice,
		nv.ComputerName = ca.ComputerName,
		nv.Computercheckdate = ca.Computercheckdate
FROM		@computersCA AS ca
INNER JOIN	@computersNV AS NV ON NV.COMPUTERID = ca.COMPUTERID

-- Insert all non-existing records
INSERT		@computersNV
SELECT		CA.*
FROM		@computersCA AS CA
LEFT JOIN	@computersnv AS NV ON CA.computerID = NV.computerID
WHERE		nv.computerID IS NULL

SELECT * FROM @computersNV



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 08/28/2008 :  17:18:39  Show Profile  Reply with Quote
SODEEP, THATS WHAT I HAVE BUT i wanna use a if staement
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  17:27:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by lamujerdetuhermano10

i wanna use a if staement
Why?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 08/28/2008 :  17:38:52  Show Profile  Reply with Quote
Peso,
I dont know...maybe it performs faster..and the IF statement makes it look better.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  17:42:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by lamujerdetuhermano10

Peso,
I dont know...maybe it performs faster..and the IF statement makes it look better.
A Porsche is nice to look at, feels good to drive, but will you use a Porsche to move your herd of Yaks?


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 08/28/2008 17:43:40
Go to Top of Page

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 08/28/2008 :  17:54:56  Show Profile  Reply with Quote
but mine works too with the if statement.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/28/2008 :  17:59:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes it works, but for one case only.

Your code:
If there already is a record present for ComputerID 2, you update all present records only with the new updated information.
If there are no records present for ComputerID 2, you insert the new records only.

You don't take into account that @ComputersCA table can hold both existing records with updated information as well as new records not present earlier.

Go ahead, knock yourself out.


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 08/28/2008 18:01:30
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000