February 16, 2010 at 7:16 am
Let's start with 2 tables CustomerTable and CustomerToInsertTable. The scenario is- we are getting a complete list of all the customers from a vendor from CustomerToInsertTable and we want to update CustomerTable from CustomerToInsertTable the by inserting the rows that don't already exist. However we also need to insert those new rows in other multiple tables.
STEP 1 : The script below will create new tables.
CREATE TABLE [CustomerTable]
(
RowID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
CustomerKey INT,
CustomerName NVARCHAR(50) NOT NULL,
UserName NVARCHAR(25) NOT NULL,
Password NVARCHAR(25) NOT NULL
)
CREATE TABLE CustomerToInsertTable
(
CustomerID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
CustomerName NVARCHAR(50) NOT NULL,
UserName NVARCHAR(25) NOT NULL,
Password NVARCHAR(25) NOT NULL,
InsertedIdentityValue INT NULL
)
STEP 2 : Now let's insert 16 new rows to the CustomerToInsertTable.
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES ('Windell L.', '1108', '14117')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Rad', '1114', '8927')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Charles F.', '1127', '12287')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Andrew R.', '1128', '9605')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Steven R.', '1191', '8420')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Robert', '1200', '18971')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Ronald F.', '1205', '13730')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Deidra', '1209', '9902')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Rena', '1264', '9611')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Ann L', '1290', '9548')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Robert', '1302', '9083')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Jerry H.', '1313', '14735')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password)VALUES('Joan', '1322', '10211')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password) VALUES('Mark', '1355', '10028')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password)VALUES('Terry', '1358', '8564')
INSERT INTO CustomerToInsertTable(CustomerName, UserName, Password)VALUES('Bret', '1373', '10067')
STEP 3 : Copy top 5 rows to CustomerTable from CustomerToInsertTable.
INSERT [CustomerTable](CustomerKey, CustomerName, UserName, Password)
SELECT TOP 5 CustomerID, CustomerName, UserName, Password
FROM CustomerToInsertTable ORDER BY CustomerID
STEP 4 :
So we are having 16 rows in CustomerToInsertTable and only 5 in CustomerTable. We need to insert rows in the CustomerTable by getting new rows from the CustomerToInsertTable and also save them in a temporary table, that can be used to insert or update any other tables.
Here I have updated the CustomerToInsertTable.InsertedIdentityValue with the CustomerTable.RowID.
--Declare Temp Table @InsertedRows
DECLARE @InsertedRows TABLE (RowID INT, CustomerKey INT, CustomerName NVARCHAR(50) )
--Insert Non existing rows in CustomerTable From CustomerToInsertTable
INSERT CustomerTable (CustomerKey, CustomerName, UserName, Password)
OUTPUT inserted.RowID,
inserted.CustomerKey,
inserted.CustomerName
INTO @InsertedRows
SELECT CustomerID, CustomerName, UserName,Password
FROM CustomerToInsertTable AS CIT
WHERE NOT EXISTS (SELECT 1 FROM CustomerTable WHERE CustomerKey = CIT.CustomerID)
--Get the new rows inserted
SELECT * FROM @InsertedRows
--Update CustomerToInsertTable
UPDATE CustomerToInsertTable
SET InsertedIdentityValue = IR.RowID
FROM CustomerToInsertTable CIT
JOIN @InsertedRows IR ON IR.CustomerKey = CIT.CustomerID
February 16, 2010 at 8:38 am
What are you trying to achieve ? What problems are you facing ?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 16, 2010 at 9:42 am
you need sql 2008 ( which is this forum ) and use the output statement ( see merge for further info )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply