Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-15
19
Medium Priority
?
127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +2
19 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40324451
>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
ID: 40324691
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 70

Expert Comment

by:Scott Pletcher
ID: 40326533
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

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

Author Comment

by:akohan
ID: 40326708
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 70

Expert Comment

by:Scott Pletcher
ID: 40326711
insert into Table2 ( [VisitorDate] )
 select  Date1
 from Table1
0
 

Author Comment

by:akohan
ID: 40326733
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
ID: 40326735
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 70

Expert Comment

by:Scott Pletcher
ID: 40326743
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
 

Author Comment

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

Expert Comment

by:Scott Pletcher
ID: 40326800
"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
ID: 40326941
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
ID: 40331718
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
ID: 40331755
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
ID: 40333911
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
ID: 40337375
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
ID: 40337564
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 2000 total points
ID: 40338091
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
ID: 40604126
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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