CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Eric Eskildsen
Eric Eskildsen

Reputation: 4769

Most concise way to insert a series of values for each row?

What's the most concise way to insert a series of values for each row in a table? Can it be done without using a loop or cursor?

For example, assuming cats, dogs, and squirrels all share behaviors 1, 2, and 3:

Table 1:

===================
  Id  |  Name    
===================
   1  |  Cat
-------------------
   2  |  Dog
-------------------
   3  |  Squirrel

Table 2:

=============================
  AnimalId  |  BehaviorId
=============================
         1  |  1
-----------------------------
         1  |  2
-----------------------------
         1  |  3
-----------------------------
         2  |  1
-----------------------------
         2  |  2
-----------------------------
         2  |  3
-----------------------------
         3  |  1
-----------------------------
         3  |  2
-----------------------------
         3  |  3
-----------------------------

Upvotes: 1

Views: 97

Answers (2)

mohan111
mohan111

Reputation: 8865

declare @t table (Id int,Name varchar(10))
insert into @t(Id,Name)values (1,'cat'),(2,'dog'),(3,'Squirrel')

declare @tt table (AnimalId int,BehaviourId int)

INSERT INTO @tt(AnimalId,BehaviourId)
select tt.ID,t.ID from @t t
CROSS APPLY (SELECT ID from @t )  tt 


Select * from @tt

Upvotes: 1

Luc
Luc

Reputation: 1491

It can be done using a simple insert statement:

insert into table_2 (AnimalId,BehavoirId)
select t1.AnimalId , t3.BehavoirId from table_1 t1
cross join table_3 t3

Assuming that the behavoirs are in table_3

Upvotes: 3

Related Questions