Foreach insert statement based on where clause-CodePudding

I have a scenario where I have thousands of Ids (1-1000), I need to insert each of these Ids once into a table with another record.

For example, UserCars - has columns CarId and UserId

I want to INSERT each user in my Id WHERE clause against CarId 1.

INSERT INTO [dbo].[UserCars]
           ([CarId]
           ,[UserId])
     VALUES
           (
           1,
           **My list of Ids**  
            )

I'm just not sure of the syntax for running this kind of insert or if it is at all possible.

CodePudding user response：

As you write in the comments that my list of Ids is coming from another table, you can simply use select into with a select clause

See this for more information

insert into UserCars (CarID, UserID) 
select CarID, UserID 
from   othertable

In the select part you can use joins and whatever you need, complex queries are allowed as long as the columns in the result match the columns (CarID, UserID)

or even this to keep up with your example

insert into UserCars (CarID, UserID) 
select 1, UserID 
from   dbo.User

CodePudding user response：

if your data exists on a file, you can use BULK INSERT command, for example:

BULK INSERT UserCars
FROM '\\path\to\your\folder\users-cars.csv';

Just make sure to have the same columns structure both in the file and in the table (e.g. CarId,UserId).

Otherwise, follow @GuidoG comment to insert your data from another table:

insert into UserCars (CarID, UserID) select CarID, UserID from othertable