0

I have two separate tables used for categories.

One is Categories(ID, Title, Description), and the other is SubCategories(ID, UpperID, Title, Description)

I want to insert the records from categories to SubCategories with upperID=0. I've looked at SQL SELECT INTO but don't know how to use it for existing tables.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
HasanG
  • 12,734
  • 29
  • 100
  • 154
  • Do you want to maintain initial IDs of Categories ? and if so, are they not present in the SubCategories ? – Gabriele Petrioli Jul 29 '10 at 08:51
  • I want to join this tables because I want to be able to set a category or subcategory id for another record (in case its a factory) in database – HasanG Jul 29 '10 at 09:14

2 Answers2

6
Insert Into dbo.SubCategories (UpperId, Title, Description)

Select 0, Title, Description
From dbo.Categories

This assumes that the ID column in both tables is an Identity column and that the ID in Categories should not be transferred to the SubCategories table

codingbadger
  • 42,678
  • 13
  • 95
  • 110
  • 2
    adding to the answer, if you want to pass the ID as well (*and it is non-conflicting with existing keys*) use the `SET IDENTITY_INSERT SubCategories ON` – Gabriele Petrioli Jul 29 '10 at 08:58
  • I was going to add this in but as I'm not 100% sure what the OP wants I decided to leave it out to avoid confusion. Still a good point to make though. – codingbadger Jul 29 '10 at 09:17
  • Used: INSERT INTO dbo.Categories (UpperId, Title, Description) SELECT UpperID, Title, Description FROM dbo.SubCategories; Because subcategories had ID's from categories so now I don't need to change catID's :) – HasanG Jul 29 '10 at 09:22
0
INSERT INTO SubCategories(ID, UpperID, Title, Description)

SELECT ID, 0, Title, Description FROM Categories

assuming that Id is not Identity field.

Azhar
  • 20,500
  • 38
  • 146
  • 211
  • 1
    -, this will give you an error with mssql ...! take a look at barry's answer (which has correct syntax) –  Jul 29 '10 at 08:58
  • NO... if you are talking about Identity then I have explained about it. – Azhar Jul 29 '10 at 09:03
  • 1
    nope... it's not about identity ... it's just your invalid syntax: `INSERT INTO TABLE (Column1, Column2, ...) VALUES (SubSelect)` is not valid. it must state `INSERT INTO TABLE (Column1, Column2, ...) SubSelect` –  Jul 29 '10 at 09:05