| Author |
 Topic  |
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
 Posted - 08/28/2008 : 16:29:45
|
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
35017 Posts |
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 08/28/2008 : 16:41:26
|
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 |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 16:45:27
|
Please, experts only...
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 16:46:08
|
Have a look at the MERGE command in SQL Server 2008.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 08/28/2008 : 16:50:57
|
You are wasting your time and my time
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 16:52:28
|
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" |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 08/28/2008 : 16:54:59
|
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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 16:58:40
|
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" |
|
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 08/28/2008 : 17:00:36
|
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
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 17:04:46
|
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" |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 08/28/2008 : 17:05:15
|
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
|
|
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 08/28/2008 : 17:08:25
|
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
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 17:12:35
|
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" |
|
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 08/28/2008 : 17:18:39
|
| SODEEP, THATS WHAT I HAVE BUT i wanna use a if staement |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 17:27:46
|
quote:
Originally posted by lamujerdetuhermano10
i wanna use a if staement
Why?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 08/28/2008 : 17:38:52
|
Peso,
I dont know...maybe it performs faster..and the IF statement makes it look better. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 17:42:15
|
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 |
|
|
|
lamujerdetuhermano10
Yak Posting Veteran
75 Posts |
Posted - 08/28/2008 : 17:54:56
|
| but mine works too with the if statement. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 08/28/2008 : 17:59:02
|
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 |
|
|
|
Topic |
|
|
|
combine scripts
