0

I have two tables. One table (A) has n rows of data and the other table (B) is empty. I want to insert n rows into table B, 1 row for each row in table A. Table B will have a couple of fields from table A in it, including a foreign key from table A. In the end I want one row in B for each row in A. How can I write some TSQL to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sachin Kainth
  • 45,256
  • 81
  • 201
  • 304
  • 1
    Please consider [SQL Insert into … values ( SELECT … FROM … )](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) – kush Nov 20 '12 at 15:15

4 Answers4

4
INSERT INTO TableB (ColA, ColB, ColC)
SELECT ColA, ColB, ColC
FROM TableA

Pinal Dave has wrote a great article on other alternative methods here:

http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Curtis
  • 101,612
  • 66
  • 270
  • 352
  • The problem with this is there are 8 columns and I only want 2 to come from table A and the others are hard coded. – Sachin Kainth Nov 20 '12 at 15:18
  • @SachinKainth: so how is this a problem?? `SELECT ColA, ColB, 1, 2, 3, 4, 5, 6 FROM .....` – marc_s Nov 20 '12 at 15:20
  • I've put this code in a stored procedure and this sp takes a int param called NumInserts. I want to insert n*NumInsert rows. So, if n is 10 and NumInserts is 5 I want to run this code 5 * 10 (50) times. In other words for each row in table A I want to insert 5 rows in B. How would I do that? – Sachin Kainth Nov 20 '12 at 15:25
  • @Sachin (a) why didn't you include that information in your question in the first place? (b) why did you accept this answer if it didn't do what you want? – Aaron Bertrand Nov 20 '12 at 16:21
1

If I understood you correctly, you need to use something like SELECT INTO: http://www.w3schools.com/sql/sql_select_into.asp

dstronczak
  • 2,406
  • 4
  • 28
  • 41
1

You can do this:

;WITH CTE
AS
(
   SELECT *, ROW_NUMBER() OVER(ORDER BY PrimaryKey) row_num
   FROM tableA
)
INSERT INTO Tableb(...)
SELECT ...
FROM CTE
WHERE row_num = 1;
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
1

This can be done by using following query.

INSERT INTO B(PriKey, RelationshipWithA, Description)
SELECT ROW_NUMBER(), A.PrimaryKey, A.Description
FROM A

If you want a more adequate example, you can provide me with specific tablenames and columns that you wish to enter.

Rvcl
  • 64
  • 5