CODEWITHSUNDEEP
sqlsql-server
amit kohan
amit kohan

Reputation: 1618

Insert data from one table to another table while the target table has a primary key

In SQL Server I have a table as RawTable (temp) which gets fed by a CVS, let's say it has 22 columns in it. Then, I need to copy existing records (ONLY FEW COLUMNs NOT ALL) into another table as Visitors which is not temporary table.

Visitor table has an ID column as INT and that is primary key and incremental.

RawData table

id  PK, int not null
VisitorDate  Varchar(10)
VisitorTime  Varchar(11)

Visitors table

VisitorID, PK, big int, not null
VisitorDate,  Varchar(10), null
VisitorTime   Varchar(11), null

So I did:

insert into [dbo].[Visitors] ( [VisitorDate], [VisitorTime])    
select [VisitorDate], [VisitorTime]
from RawTable   /*this is temp table */

Seems SQL Server doesn't like this method so it throws

Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'VisitorID', table 'TS.dbo.Visitors'; column does not allow nulls. INSERT fails. The statement has been terminated.

How can I keep Sql Server not to complain about the primary key? this column as you know better will be fed by sql server itself.

Any idea?

Upvotes: 0

Views: 3981

Answers (1)

Nick H.
Nick H.

Reputation: 1616

Just because your visitors table has an ID column that is the primary key doesn't mean that the server will supply your ID values for you. if you want SQL to provide the ID's then you need to alter the table definition and make the visitorsId column an IDENTITY column.

Otherwise, you can psuedo-create these id's during the insert with the ROW_NUMBER function - 

DECLARE @maxId INT;

SELECT @maxId = (SELECT MAX(visitorsId) FROM dbo.visitors);

INSERT INTO  [dbo].[Visitors] ( [visitorsId],[VisitorDate], [VisitorTime])    
SELECT @maxId + ROW_NUMBER() OVER (ORDER BY visitorDate), [VisitorDate], [VisitorTime]
from RawTable   /*this is temp table */

Upvotes: 1

Related Questions