combine scripts - 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

General SQL Server Forums

New to SQL Server Programming

combine scripts

Author

Topic

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-28 : 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

38200 Posts

Posted - 2008-08-28 : 16:38:19

[code]
IF ...
Do something
ELSE
Do something else
[/code]

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

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-28 : 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

30421 Posts

Posted - 2008-08-28 : 16:45:27

Please, experts only...

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 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 - 2008-08-28 : 16:50:57

You are wasting your time and my time

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-28 : 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

30421 Posts

Posted - 2008-08-28 : 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 - 2008-08-28 : 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

30421 Posts

Posted - 2008-08-28 : 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

38200 Posts

Posted - 2008-08-28 : 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 - 2008-08-28 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-28 : 17:10:27

see this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109666

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 17:12:35

[code]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[/code]

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

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-28 : 17:18:39

SODEEP, THATS WHAT I HAVE BUT i wanna use a if staement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 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 - 2008-08-28 : 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

30421 Posts

Posted - 2008-08-28 : 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"

lamujerdetuhermano10
Yak Posting Veteran

75 Posts

Posted - 2008-08-28 : 17:54:56

but mine works too with the if statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 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"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources