What should I do with the PK when Inserting from table A to table B

>I'm trying to insert few columns from table A into table B
You're going to have to give us the schemas of Visitors and the other table for us to answer this question.
Table name, columns names, is there a relationship between the two...

>Now, how this syntax should change when I have a primary key and will be fed by Sql Server itself?
>Cannot insert the value NULL into column 'VisitorID', table 'MyDb.Visitors'
Normally {anything}ID is the primary key for {anything}, whether it's intentionally fed, or more then likely it's an identity field that incriments 1, 2, 3, etc. on insert.  You insert values in all other columns, and SQL automatically assigns the next value in line.

Also, in case no one has told you, experts here suck at mind reading, so you'll have to spell out all relevant details for us.

Have you set column 'VisitorID' as auto increment? if not please do so.
For that you can set the 'Identity' property as 1 in properties of the column.

VisitorID is most likely already an identity.  You should leave it out of the INSERT completely, and SQL will assign the next value:

INSERT INTO Visitors ( dataCol2, dataCol3, dataCol4 )
SELECT name, address, city, ...
FROM table1

--instead of:
--INSERT INTO Visitors ( VisitorID, dataCol2, dataCol3, dataCol4 )
--SELECT NULL, name, address, city, ...
FROM table1

NULL doesn't help. Have you tried it yourself?

OK let me explain more this is the table1 (source) and this is table2 (destination)

table1 structure is:

ID int incremental
Date1  varchar(10)
Time1 varchar(11)
ClientIP varchar(15)
and more ..

table2 structure is:  (only some of them)

VisiorID int incremental  PK
Date1 varchar(10)  /* I changed it from date to varchar just to see if that would work. */
Time1 varchar(11)  /* I changed it from time to varchar just to see if that would work.*/
ClientIP varchar(15)

then I have

insert into Table2 ( VisitorID, [VisitorDate])
select NULL, Date1
from Table1

Throws:

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

Any idea?

insert into Table2 ( [VisitorDate] )
 select  Date1
 from Table1

Yes, I did that too:

insert into Visitors ( [VisitorDate])
select [VisitorDate]
from Table1

and it thorws:

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

It keeps checking for VisitorID (PK).

There has to be a value to pass in there so to keep SQL Server  know VisitorID will be fed by itself and user IS NOT supposed to provide that.

anybody ?

What exactly does:

" Visi[t]orID int incremental " mean?

I thought "incremental" meant "identity" but clearly that's not the case.

So what does "incremental" mean?

yes it is PK and it is integer (incremental). Not sure which part of it is not  clear. can you tell me?

"incremental"?  What specifically does that mean to you?  Just that the value you supply to the table tends to increase or that something in the db itself -- identity, trigger, whatever -- increments it for you?

akohan,

yes it is PK and it is integer (incremental). Not sure which part of it is not  clear. can you tell me?
When someone asks you for the schema for your table (see http:#a40324451) they :
A. Need you to post the CREATE TABLE YourTable )... etc.
B. They really want to help you.

If you don't post appropriate feedback,  you will find members here will fast lose interest and move on to some other question where the author shows that he really needs help.

Thanks Anthony for the hint. Believe or not I didn't notice Jim's comment and yes that is my fault.
Just to clarify it.

This is the destination table:

CREATE TABLE [dbo].[Visitor](
	[ID] [int] NOT NULL,
	[c_IP] [varchar](15) NOT NULL,
	[Description] [varchar](50) NULL,
 CONSTRAINT [PK_Visitor] PRIMARY KEY CLUSTERED 
(
	[c_IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Select allOpen in new window

And the source table has this structure:

CREATE TABLE [dbo].[RawDataMetrics](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[VisitorDate] [varchar](10) NULL,
	[VisitorTime] [varchar](11) NULL,
	[c_ip] [varchar](15) NULL,
	[s_ip] [varchar](15) NULL,
	[s_port] [varchar](5) NULL,
	[cs_method] [varchar](8) NULL,
	[sc_status] [varchar](5) NULL,
	[sc_substatus] [varchar](5) NULL,
	[sc_bytes] [varchar](10) NULL,
	[cs_bytes] [varchar](10) NULL,
	[time_taken] [varchar](10) NULL,
	[cs_version] [varchar](20) NULL,
	[cs_host] [varchar](50) NULL,
 CONSTRAINT [PK_RawDataMetrics] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Select allOpen in new window

I hope this makes it clear.

Thanks!

Please try correcting your destination table query as:

CREATE TABLE [dbo].[Visitor](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [c_IP] [varchar](15) NOT NULL,
      [Description] [varchar](50) NULL,
 CONSTRAINT [PK_Visitor] PRIMARY KEY CLUSTERED
(
      [c_IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Thank you for posting the schema.  Can you tell us where VisitorID is coming from in the following error?
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'VisitorID', table 'MyDb.Visitors'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Assuming your are doing an INSERT INTO Visitor, is there a TRIGGER on the same table?

Thanks to you all for help.
I am back to my computer so I can follow your instructions and resolve this.

Ramkisan,
I will apply your change to the target table.


Now, to answer Anthony's question, please consider ID column in Visitor table as VisitorID. I have renamed it from ID to VisitorID.

Ramkisan,

I made the table you were referring to but after running the query it threw the error I was expecting.

insert into Visitor
   (ID)
    select distinct Id
      from RawDataMetrics

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Visitor'. Cannot insert duplicate key in object 'dbo.Visitor'. The duplicate key value is (172.16.10.197).
The statement has been terminated.


I guess, all I need to know is how to insert few column from table A in table B and few of column of table A again in table C? knowing that our ID in table A is PK and having that ID in other tables as FK.  it shouldn't  be a complex matter. or is it?!

Thanks