April 22, 2009 at 1:02 am
Hello
I currently Have 300 staff members in SQL ASP membership I need to add 3094 Customers that are in the customers role...
I have an existing Customers Table that contains all the customers I was wondering if there is a way from SQL to run a script that copies the required info from the customer table and inserts it into membership then inserts the related userid into the customers table???
it needs to append the records
else is it possible to have this as a profile table???
April 22, 2009 at 4:33 am
fairly straight forward. You need to take advantage of the OUTPUT command. this simple functionality kicks butt when playing with more than one row.
build a temp table to catch the new userID, as well as any other data you need.
CREATE TABLE #NEWUSERIDS(newuserid int,customerID)
the insert or update has the virtual tables INSERTED and DELETED...when you use the OUTPUT command, they are exposed outside of a trigger for usage.
then use the table via the OUTPUT...read about it in BOL:
INSERT INTO Membership(customerID,OTHERCOLUMNS)
OUTPUT INSERTED.UserID,Inserted.customerID
INTO #NEWUSERIDS
SELECT customerID,OTHERCOLUMNS FROM Customers Where ID BETWEEEN 1 AND 3092
--now use the new table to update back:
UPDATE Customers
SET Customers.UserID =#NEWUSERIDS.newuserid
FROM #NEWUSERIDS
WHERE Customers.CustomerID = NEWUSERIDS.CustomerID --3092 rows
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply