Solved

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

Posted on 2014-09-15
19
113 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
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 69

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 69

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 69

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 69

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 500 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 41
Stored Proc - Rewrite 42 61
Parse this column 6 27
Casting is giving error in sql server 3 12
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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…

820 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