I have a table that adds customers to a user, UserCustomerXRef.

I want to add multiple records to this table, attaching multiple customerIDs to a single username.

To get this list of customers I would use a select command like

SELECT CustomerID FROM Customers WHERE Country = 'US'

This gives me a list of 200 records.

I know how to create a single record using values like

INSERT INTO UserCustomerXRef VALUES ('Rroe', '10001', GetDate(), 'N')

I know how to create a subset of a table with multiple records by selecting columns from another table.

INSERT INTO CustomersUS 
SELECT (CustomerID, Name, Address1, City) 
FROM Customers WHERE Country = 'US'

What I don't know how to do is the create a query from both, where I supply the 'Rroe', 'N', and GetDate() values, and the CustomerID comes from the Customers table.

What I usually do is this:

SELECT ('('   CHAR(39)   'Rroe'   CHAR(39)   ', '   CHAR(39)   CustomerID 
  CHAR(39)   ', GetDate(), '   CHAR(39)   'N'   CHAR(39)   '),') 
FROM Customers WHERE Country = 'US'

This gives me output like

('Rroe  ', '900001', GetDate(), 'N'),
('Rroe  ', '900019', GetDate(), 'N'),
('Rroe  ', '900027', GetDate(), 'N'),
('Rroe  ', '900035', GetDate(), 'N'),
('Rroe  ', '900043', GetDate(), 'N'),
('Rroe  ', '900050', GetDate(), 'N'),
('Rroe  ', '900068', GetDate(), 'N'),
('Rroe  ', '900076', GetDate(), 'N'),
('Rroe  ', '900092', GetDate(), 'N'),
('Rroe  ', '900100', GetDate(), 'N'),
('Rroe  ', '900118', GetDate(), 'N'),

I copy this to the query window, type INSERT INTO UserCustomerXRef VALUES above it, delete the comma after the last line, and run it.

This works, but surely there's a query that will do this. Something like

INSERT INTO UserCustomerXRef VALUES 
('Rroe', SELECT CustomerID FROM Customers, GetDate(), 'N') 
WHERE Customers.Country = 'US"

CodePudding user response：

Although you put some effort in your question it is hard to understand what you really want to achieve. I think you are just looking for the correct syntax to insert a mixture of "static" data and data based on a query.

INSERT INTO UserCustomerXRef 
(
    Username,
    Link,
    Deleted,
    Last_Modified
) 
SELECT 
    'Rroe', 
    C.CustomerID, 
    'N',
    GETDATE()
) 
FROM Customers C
WHERE C.Country = 'US';

CodePudding user response：

Well, I was poking around and found I had already created an answer in our company SQL library.

INSERT INTO UserCustomerXRef 
SELECT 'Rroe', CustomerID, GetDate(), 'N'
FROM Customers WHERE Deleted = 'N'
AND Country = 'US'