-3

Ok so I have just created a table _amazonProduct with some columns:

ID
ProductID
ProductPrice

Now I have a product table with many columns, more than 50.

The column I want to insert is Id from product table into ProductID in _amazonProduct. All other values will be set to null.

Some things I have tried.

UPDATE [Azure_Test].[dbo].[_amazonProduct]
SET [Azure_Test].[dbo].[_amazonProduct].[ProductID] = [Azure_Test].[dbo].[Product].[Id]
FROM [Azure_Test].[dbo].[_amazonProduct]
JOIN [Azure_Test].[dbo].[Product] ON [Azure_Test].[dbo].[_amazonProduct].[ProductID] = [Azure_Test].[dbo].[Product].[Id]

Also tried:

SELECT [Azure_Test].[dbo].[Product].[Id]
INTO [Azure_Test].[dbo].[_amazonProduct].[ProductID]
FROM [Azure_Test].[dbo].[Product]

Also tried:

UPDATE [Azure_Test].[dbo].[_amazonProduct]
SET [ProductID] = (
    SELECT [Id]
    FROM [Azure_Test].[dbo].[Product]
)

Anyone able to help me out, not sure exactly how to go about this.

Cheers

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Web Dev Guy
  • 1,693
  • 3
  • 18
  • 42

1 Answers1

1

try： --Copy strtuct:

SELECT *
INTO [Azure_Test].[dbo].[_amazonProduct]
FROM [Azure_Test].[dbo].[Product] WHERE 1=0
GO
sp_rename '_amazonProduct.ID','ProductID','COLUMN'
GO

--Insert into new table:

insert into [Azure_Test].[dbo].[_amazonProduct](ProductID)
 select ID FROM [Azure_Test].[dbo].[Product]

Also you ca use dynamic script。

Nolan Shang
  • 2,312
  • 1
  • 14
  • 10
  • Perfect the second statement is exactly what I needed, it inserted each ProductID, auto incremented the ID and sett other values to null. Thanks for the help champ :) – Web Dev Guy Oct 31 '17 at 01:12