how to change this insert statement to an update s

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 2008 Forums

Transact-SQL (2008)

how to change this insert statement to an update s

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-08-23 : 08:41:33

INSERT INTO Table1.dbo.fullreconcile
SELECT 'BACS' AS 'FILETYPE'
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet WHERE SubmissionStatus = 'UNSUBMITTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS')
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'EXTRACTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS')
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'SUBMITTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS')
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'PROCESSING'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS')
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'UNSUBMITTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS')
,(SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS' )

,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'EXTRACTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'SUBMITTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'PROCESSING'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'UNSUBMITTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'BACS' )
UNION
SELECT 'IRISH' AS 'FILETYPE'
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'UNSUBMITTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )

,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH')
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'EXTRACTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'SUBMITTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'PROCESSING'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'UNSUBMITTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'IRISH' )

,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH')
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'EXTRACTED'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'SUBMITTED'
AND SubmissionTF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE type IN ('Live', 'AUDDIS Live')
AND DATEDIFbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'PROCESSING'
AND SubmissionType IN ('Live', 'AUDDIS Live')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'UNSUBMITTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'ACCEPTED'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH' )
,( SELECT COUNT(*) FROM Table1.dbo.tbCBMTranSet
WHERE SubmissionStatus = 'REJECT'
AND SubmissionType IN ('Test', 'AUDDIS Test')
AND DATEDIFF(d, SubmittedDate, GETDATE()) = 0
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount
WHERE tbBOSS_OAccount.OAccountID = tbCBMTranSet.OAccountID) = 'IRISH' )

Kristen
Test

22859 Posts

Posted - 2011-08-23 : 09:16:03

I would change the SELECT statement to have Alias names for each column in the resultset (that match the column names in your [fullreconcile] table), something like:


INSERT INTO Table1.dbo.fullreconcile
SELECT 'BACS' AS 'FILETYPE'
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet WHERE SubmissionStatus = 'UNSUBMITTED' 
AND SubmissionType IN ('Live', 'AUDDIS Live') 
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount 
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS' ) AS MyCol2
, ...

you could then, relatively easily, modify this code to be


UPDATE U
SET FILETYPE = T.FILETYPE,
    MyCol2 = T.MyCol2,
    ....
FROM Table1.dbo.fullreconcile AS U,
(
SELECT 'BACS' AS 'FILETYPE'
,( SELECT COUNT(*) FROM Table2.dbo.tbBOSS_TranSet WHERE SubmissionStatus = 'UNSUBMITTED' 
AND SubmissionType IN ('Live', 'AUDDIS Live') 
AND (SELECT Country FROM Table2.dbo.tbBOSS_OAccount 
WHERE tbBOSS_OAccount.OAccountID = tbBOSS_TranSet.OAccountID) = 'BACS' ) AS MyCol2
, ...
) AS T
WHERE U.SomePK = @SomeVariable

If there is only one row in the target table you won't need the WHERE clause (but in that case I recommend using a CHECK constraint to make sure that a second row cannot be inserted

)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-23 : 09:22:06

First of all, refactor your code for better reading and performance (at least 33 times faster), like this below.
Also, for reusability, create the query as a view.

CREATE VIEW dbo.MyView
AS

SELECT		a.Country AS FileType
		b.bLiveUnsubmitted,
		b.bLiveAccepted,
		b.bLiveReject,
		b.bLiveExtracted,
		b.bLiveSubmitted,
		b.bLiveProcessing,
		b.bTestUnsubmitted,
		b.bTestAccepted,
		b.bTestReject,
		c.cLiveAccepted,
		c.cLiveReject,
		c.cLiveExtracted,
		c.cLiveSubmitted,
		c.cLiveProcessing,
		c.cTestUnsubmitted,
		c.cTestAccepted,
		c.cTestReject
FROM		Table2.dbo.tbBOSS_OAccount AS a
LEFT JOIN	(
			SELECT		OAccountID,
					SUM(CASE WHEN SubmissionStatus = 'UNSUBMITTED' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bLiveUnsubmitted,
					SUM(CASE WHEN SubmissionStatus = 'ACCEPTED' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bLiveAccepted,
					SUM(CASE WHEN SubmissionStatus = 'REJECT' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bLiveReject,
					SUM(CASE WHEN SubmissionStatus = 'EXTRACTED' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bLiveExtracted,
					SUM(CASE WHEN SubmissionStatus = 'SUBMITTED' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bLiveSubmitted,
					SUM(CASE WHEN SubmissionStatus = 'PROCESSING' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bLiveProcessing,
					SUM(CASE WHEN SubmissionStatus = 'UNSUBMITTED' AND SubmissionType IN ('Test', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bTestUnsubmitted,
					SUM(CASE WHEN SubmissionStatus = 'ACCEPTED' AND SubmissionType IN ('Test', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bTestAccepted,
					SUM(CASE WHEN SubmissionStatus = 'REJECT' AND SubmissionType IN ('Test', 'AUDDIS Live') THEN 1 ELSE 0 END) AS bTestReject
			FROM		Table2.dbo.tbBOSS_TranSet
			WHERE		SubmissionStatus IN ('UNSUBMITTED', 'ACCEPTED', 'REJECT', 'EXTRACTED', 'SUBMITTED', 'PROCESSING')
					AND SubmissionType IN ('Live', 'Test', 'AUDDIS Live')
					AND SubmittedDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')
					AND SubmittedDate < DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101')
			GROUP BY	OAccountID
		) AS b ON b.OAccountID = a.OAccountID
LEFT JOIN	(
			SELECT		OAccountID,
					SUM(CASE WHEN SubmissionStatus = 'ACCEPTED' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cLiveAccepted,
					SUM(CASE WHEN SubmissionStatus = 'REJECT' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cLiveReject,
					SUM(CASE WHEN SubmissionStatus = 'EXTRACTED' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cLiveExtracted,
					SUM(CASE WHEN SubmissionStatus = 'SUBMITTED' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cLiveSubmitted,
					SUM(CASE WHEN SubmissionStatus = 'PROCESSING' AND SubmissionType IN ('Live', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cLiveProcessing,
					SUM(CASE WHEN SubmissionStatus = 'UNSUBMITTED' AND SubmissionType IN ('Test', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cTestUnsubmitted,
					SUM(CASE WHEN SubmissionStatus = 'ACCEPTED' AND SubmissionType IN ('Test', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cTestAccepted,
					SUM(CASE WHEN SubmissionStatus = 'REJECT' AND SubmissionType IN ('Test', 'AUDDIS Live') THEN 1 ELSE 0 END) AS cTestReject
			FROM		Table1.dbo.tbCBMTranSet
			WHERE		SubmissionStatus IN ('UNSUBMITTED', 'ACCEPTED', 'REJECT', 'EXTRACTED', 'SUBMITTED', 'PROCESSING')
					AND SubmissionType IN ('Live', 'Test', 'AUDDIS Live')
					AND SubmittedDate >= DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101')
					AND SubmittedDate < DATEADD(DAY, DATEDIFF(DAY, '18991231', GETDATE()), '19000101')
			GROUP BY	OAccountID
		) AS c ON c.OAccountID = a.OAccountID
WHERE		a.Country IN ('BACS', 'IRISH')

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2011-08-23 : 09:28:40

Table2.dbo.tbBOSS_OAccount

worried me a bit too ... but maybe that's just a find&replace goof-up in obfuscating for forum posting ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-23 : 09:30:50

And then you simple write a MERGE statement to deal with both INSERT and UPDATE, like this.

MERGE	Table1.dbo.FullReconcile AS tgt
USING	dbo.MyView AS src ON src.FileType = tgt.FileType
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.bLiveUnsubmitted = src.bLiveUnsubmitted,
				tgt.bLiveAccepted = src.bLiveAccepted,
				tgt.bLiveReject = src.bLiveReject,
				tgt.bLiveExtracted = src.bLiveExtracted,
				tgt.bLiveSubmitted = src.bLiveSubmitted,
				tgt.bLiveProcessing = src.bLiveProcessing,
				tgt.bTestUnsubmitted = src.bTestUnsubmitted,
				tgt.bTestAccepted = src.bTestAccepted,
				tgt.bTestReject = src.bTestReject,
				tgt.cLiveAccepted = src.cLiveAccepted,
				tgt.cLiveReject = src.cLiveReject,
				tgt.cLiveExtracted = src.cLiveExtracted,
				tgt.cLiveSubmitted = src.cLiveSubmitted,
				tgt.cLiveProcessing = src.cLiveProcessing,
				tgt.cTestUnsubmitted = src.cTestUnsubmitted,
				tgt.cTestAccepted = src.cTestAccepted,
				tgt.cTestReject = src.cTestReject
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					FileType,
					bLiveUnsubmitted,
					bLiveAccepted,
					bLiveReject,
					bLiveExtracted,
					bLiveSubmitted,
					bLiveProcessing,
					bTestUnsubmitted,
					bTestAccepted,
					bTestReject,
					cLiveAccepted,
					cLiveReject,
					cLiveExtracted,
					cLiveSubmitted,
					cLiveProcessing,
					cTestUnsubmitted,
					cTestAccepted,
					cTestReject
				)
			VALUES	(
					src.FileType,
					src.bLiveUnsubmitted,
					src.bLiveAccepted,
					src.bLiveReject,
					src.bLiveExtracted,
					src.bLiveSubmitted,
					src.bLiveProcessing,
					src.bTestUnsubmitted,
					src.bTestAccepted,
					src.bTestReject,
					src.cLiveAccepted,
					src.cLiveReject,
					src.cLiveExtracted,
					src.cLiveSubmitted,
					src.cLiveProcessing,
					src.cTestUnsubmitted,
					src.cTestAccepted,
					src.cTestReject
				);

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources