Solved

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

Posted on 2014-09-15
19
99 Views
Last Modified: 2015-02-11
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
Comment
Question by:akohan
  • 9
  • 4
  • 3
  • +2
19 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
 
LVL 12

Expert Comment

by:Ramkisan Jagtap
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:akohan
Comment Utility
NULL doesn't help. Have you tried it yourself?
0
 

Author Comment

by:akohan
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
insert into Table2 ( [VisitorDate] )
 select  Date1
 from Table1
0
 

Author Comment

by:akohan
Comment Utility
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
 

Author Comment

by:akohan
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:akohan
Comment Utility
yes it is PK and it is integer (incremental). Not sure which part of it is not  clear. can you tell me?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
"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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Comment

by:akohan
Comment Utility
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
 
LVL 12

Expert Comment

by:Ramkisan Jagtap
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Comment

by:akohan
Comment Utility
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
 

Author Comment

by:akohan
Comment Utility
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:akohan
Comment Utility
Thanks
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now