SQL INSERT statement using values in declared list

Question

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)

AHiggins · Accepted Answer · 2015-06-24 17:49:51Z

2

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

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

answered Jun 24, 2015 at 17:49

AHiggins

7,2357 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Goodman Over a year ago

Wow - that's beautifully simple. Thank you!

Collectives™ on Stack Overflow

SQL INSERT statement using values in declared list

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related