Create table, insert into - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create table, insert into

Author

Topic

FeelingAbitLikeAlice
Starting Member

4 Posts

Posted - 2005-11-18 : 15:04:29

I was having an issue summing up the clientcred.defmonthlypayment column on a user by user basis when I was joining it with another table to get only "active" objects. It would return exponentially large numbers when joined to the other table. So I was trying to drop it into a temp table so i could query against it to get the results I needed. But it keeps telling me that 'null' values arent allowed in the 'clientid' column when I instucted it to put the results into another column and to allow 'null' values in that column.

error message at the bottom

-------------------------------------------------------------

CREATE TABLE #accts (
clientid int primary key,
clientid1 varchar(6)null,
monthlyacctpayment varchar(10)null,
grp1 int null,
clientid2 varchar(6)null,
monthlyacctfees varchar(10)null,
grp2 int null
)

INSERT into #accts
(clientid1,monthlyacctpayment,grp1)
SELECT distinct clientcred.clientid as 'clientid1',
CAST(sum(clientcred.defmonthlypayment) as varchar)'monthlyacctpayment',
GROUPING(clientcred.clientid) 'grp1'
FROM clientcred
GROUP BY clientcred.clientid WITH ROLLUP

INSERT into #accts
(clientid2,monthlyacctfees,grp2)
SELECT clients.clientid as 'clientid2',
CAST(sum(clients.monthlyacctfee) as varchar)'monthlyacctfees',
GROUPING(clients.clientid) 'grp2'
FROM clients
GROUP BY clients.clientid WITH ROLLUP

Select clients.clientid,monthlyacctfees,monthlyacctpayment from #accts,clients
where clients.active='-1' and clients.clientid=#accts.clientid

drop table #accts

--------------------------------------------------------------
Results

Server: Msg 515, Level 16, State 2, Line 14
Cannot insert the value NULL into column 'clientid', table 'tempdb.dbo.#accts______________________________________________________________________________________________________________00000001CFA5'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Server: Msg 515, Level 16, State 2, Line 23
Cannot insert the value NULL into column 'clientid', table 'tempdb.dbo.#accts______________________________________________________________________________________________________________00000001CFA5'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Why is it trying to drop the requested info into the clients column when I state that it should be dropped into the specified column?

I apologize for any statements or references that are wrong now but thats why I posted in the 'new to SQL Server' section. Thanks everyone

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-18 : 15:16:12

Do you have some sample data from the clientcred and clients tables for testing?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-18 : 15:25:18

A primary key can not contain nulls


USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2) SELECT 'a'
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

FeelingAbitLikeAlice
Starting Member

4 Posts

Posted - 2005-11-18 : 15:41:09

quote:
Originally posted by X002548

A primary key can not contain nulls

[/code]

Brett

8-)

Im not trying to place nulls into the primary key though, I figured that one out, which is why I added the clientid primary key. Im trying to place the results of my 'sum' aggregates into columns in the new table so i can query against it. The issue is that it keeps trying to place some of my data results into a the Primary key column of my new table and Icant figure out why. I have the wrong syntax somewhere in my script and I cant find it.

-----------------------------------------------------------
This result error

Server: Msg 515, Level 16, State 2, Line 14
Cannot insert the value NULL into column 'clientid', table 'tempdb.dbo.#accts______________________________________________________________________________________________________________00000001CFA5'; column does not allow nulls. INSERT fails.
The statement has been terminated.
---------------------
Specifies this line of code

INSERT into #accts
----------------
and I cant figure out why that insert function is trying to place data into the Primary Key column???????

Thanks

PS: I know that 'line 14' doesnt count out in my post but I cleaned it up a bit when i posted.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-11-18 : 15:50:06

quote:
Originally posted by X002548

A primary key can not contain nulls

USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2) SELECT 'a'
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Hey, Brett.....hit me in the head with a hammer! I can't believe I missed that: "A primary key can not contain nulls"!

Arrrgh!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-18 : 16:19:10

quote:
Originally posted by FeelingAbitLikeAlice
Im not trying to place nulls into the primary key though

Yes that is exacrly what you are trying to do.

Do you see the clientid in the column list for your INSERT?

Niether do I...that means you are trying to insert a row with no value for ClientID, hence nulls....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

FeelingAbitLikeAlice
Starting Member

4 Posts

Posted - 2005-11-18 : 16:31:31

quote:
Do you see the clientid in the column list for your INSERT?

Niether do I...that means you are trying to insert a row with no value for ClientID, hence nulls....

Ok, Im not trying to do that but that is what it is doing, can you explain this to me a litte bit more. I dont understand where Im going wrong.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-18 : 16:39:10

If you leave a column, out of the column list, and then insert a row, it will assume null for that column.

Do you use Query Analyzer?

Cut, paste and execute this code


USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

INSERT INTO myTable99(Col2) SELECT 'a'
GO

SELECT * FROM  myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

FeelingAbitLikeAlice
Starting Member

4 Posts

Posted - 2005-11-18 : 18:49:53

Excellent, thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources