CODEWITHSUNDEEP
sql-servert-sql
gttommo
gttommo

Reputation: 181

T-SQL - how to insert while looping through a table

I have a table that holds organization data. I also have a new table that is going to hold info about shipping containers. As an initial test I want to populate the new Shipping_Containers table by giving one container to each organization. So I thought about getting the organization ids and looping through them and doing an insert into Shipping_Containers for each organization id, something like this:

select * into #orgs from Organization_1
while (select id from #orgs ) IS NOT NULL
begin
    insert into Shipping_Containers (name, org) 
    values('test_name', id)
end

I know there is probably a lot wrong with that but firstly it is giving this error:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'id'.

I'm using SQL Server 2008 R2 and I'm new to T-SQL so any guidance here would be appreciated, thanks.

Upvotes: 1

Views: 1108

Answers (1)

peterm
peterm

Reputation: 92835

Try

INSERT INTO Shipping_Containers (name, org)
SELECT 'test_name', id
  FROM Organization_1

Upvotes: 7

Related Questions