Avatar of wally_davis
Flag for United States of America asked on

Unable to Insert Date from one table to another

My two tables look like this:
1. Table name = main, Columns are Mac (PK, bigint, not null), Created (datetime, null) and Modified (datetime, null)
2. Second table, Table name = Uptime, Columns are Uptime_ID (PK, bigint, not null), Mac (FK, nchar(19), not null), LastResponse (smalldatetime, null), Status (nchar(10) and Flag (varchar(10).
Trust Relationship --> Main.MAC = PK and Uptime.MAC = FK. Cascade on Update and Insert are set on both tables.
The problem I'm having is that when I attempt to run this routine bellow in code snippet.
I also reseed the Uptime table and when I run a Select * From Main where Mac = '' it doesn't find any NULL data.

I'm stuck so I really need an experts advice on what else would be preventing the Main.Mac from updating the Uptime.Mac table.
Thank you,
declare @MAC nchar(18)
SELECT Mac from Main
	INSERT INTO uptime ( Mac ) Values( @MAC )
I get this error --> (16563 row(s) affected)
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'MAC', table 'platform_validation_tool.dbo.Uptime'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Open in new window

Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon
Paulo Pimenta

Somehow, there are columns (Mac) in table "Main" which have NULL values, and table "uptime" does not alolow NULL values on "Mac" column.
You can do either one of two things:
1) alter table "uptime" so that column "Mac" allows NULL values.
2) Excute the following query:
SELECT Mac from Main
	INSERT INTO uptime ( Mac ) Values( ISNULL(@MAC, '') )

Open in new window


View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

your example will not fill the @mac value at all, so it will remain null :D
SELECT mac from Main just returns a resultset.
You could have done SELECT @mac = mac FROM Main
but that would just have worked for a single row.

You are gonna have to go with ee_rlee's solution


Makes sense. You can't Insert nothing and then run the Select command. My mistake. Thank for the help, it works great!!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck