CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Matt Goodman
Matt Goodman

Reputation: 182

SQL INSERT statement using values in declared list

I'm pretty new to SQL...looking for a simple solution. I have a query where I declare a list of foreign keys, like this:

DECLARE @CustomerFkList table (Fk int);

BEGIN
INSERT @CustomerFkList (Fk) VALUES (12345), (67890), (65432)
END

Later on in the query I want to INSERT one record into a specific table for each of the values in my list, where the foreign key value goes into a foreign key field. What's the best approach for this?

Here is the full code - what I have so far. I know that the last two lines are wrong:

USE CustomerDatabase;
DECLARE @CustomerFkList table (Fk int);

BEGIN
INSERT @CustomerFkList (Fk) VALUES (x), (x), (x), (etc.)
END

BEGIN
UPDATE CustomerLifeCycleStatus SET EndDate=GETDATE() 
    WHERE (CustomerFk IN (SELECT Fk FROM @CustomerFkList)
            AND LifeCycleStatus = 'Active' 
            AND EndDate IS NULL)
END 

/* ------- below is what I need to iterate through ---------- */
INSERT INTO CustomerLifeCycleStatus (CustomerFk, LifeCycleStatus, StartDate) 
    VALUES (@CustomerFkList, 'Retired', GETDATE());

Help please? (I'm using SQL-Server 2008 R2, Microsoft SQL Server Management Studio)

Upvotes: 1

Views: 1128

Answers (1)

AHiggins
AHiggins

Reputation: 7219

No need to iterate, just insert it all with a single statement: 

INSERT INTO CustomerLifeCycleStatus (CustomerFk, LifeCycleStatus, StartDate) 
SELECT FK, 'Retired', GETDATE()
FROM @CustomerFKList

Upvotes: 2

Related Questions