I am trying to insert data from one table into another table. Table1 doesn't have a uniqueidentifier, but Table2 does. I have tried to insert using NEWID(), but I get a syntax error. Can someone please tell me what I am doing wrong?
INSERT INTO Table2 (NEWID(), [Item Description], [Item Cost])
SELECT Description, Cost FROM Table1
WHERE Id = '1'
In case Table2 is not yet created you can use this query:
SELECT
NEWID() AS [ID]
,Description AS [Item Description]
,Cost AS [Item Cost]
INTO Table2
FROM Table1
WHERE Id = '1'
But, in case the schema for Table 2 has already been created, and it has a column for the identifier then you can use:
INSERT INTO Table2 ([ID], [Item Description], [Item Cost])
SELECT
NEWID()
, Description
, Cost
FROM Table1
WHERE Id = '1'
But if you haven't created the schema for Table2 yet, then I recommend using the following code to create both the schema for the table and populate the data in it, using data from Table1.
CREATE TABLE Table2 (
[ID] INT Identity
,[Item Description] AS NVARCHAR(MAX)
,[Item Cost] AS NUMERIC(18, 2))
INSERT INTO Table2([Item Description] , [Item Cost])
SELECT
Description
, Cost
FROM Table1
WHERE Id = '1'
Setting the ID column as Identity will auto-generate a UNIQUE identifier number for each row which you don't have to worry about populating. The number is incremental and it increments by default by 1 for each row, starting from 1.
You can also define a starting number and a incremental value by defining the [ID] column as [ID] INTEGER IDENTITY(1000, 2) and this will make the starting value 1000 and the increment will be 2 (but this is just FYI).
