Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

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

Hello,

base on the syntax in sql I'm trying to insert few columns from table A into table B so it goes:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

http://www.w3schools.com/sql/sql_insert_into_select.asp

Now, how this syntax should change when I have a primary key and will be fed by Sql Server itself?  currently I'm getting error for that as


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.
0
akohan
Asked:
akohan
  • 9
  • 4
  • 3
  • +2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
Ramkisan JagtapLead DeveloperCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
akohanAuthor Commented:
NULL doesn't help. Have you tried it yourself?
0
 
akohanAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
insert into Table2 ( [VisitorDate] )
 select  Date1
 from Table1
0
 
akohanAuthor Commented:
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).
0
 
akohanAuthor Commented:
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 ?
0
 
Scott PletcherSenior DBACommented:
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?
0
 
akohanAuthor Commented:
yes it is PK and it is integer (incremental). Not sure which part of it is not  clear. can you tell me?
0
 
Scott PletcherSenior DBACommented:
"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?
0
 
Anthony PerkinsCommented:
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.
0
 
akohanAuthor Commented:
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]

Open 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]

Open in new window


I hope this makes it clear.

Thanks!
0
 
Ramkisan JagtapLead DeveloperCommented:
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]
0
 
Anthony PerkinsCommented:
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?
0
 
akohanAuthor Commented:
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.
0
 
akohanAuthor Commented:
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?!
0
 
Anthony PerkinsCommented:
To get back to your original question:
base on the syntax in sql I'm trying to insert few columns from table A into table B so it goes:
INSERT      dbo.Visitor(
            ID, c_IP, [Description])
SELECT      ID, c_IP, 'YourDescriptionGoeshere'
FROM      dbo.RawDataMetrics
WHERE      <someconditiongoeshere>
0
 
akohanAuthor Commented:
Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now