Establishing a Field Count during Table

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

SQL Server 2000 Forums

SQL Server Development (2000)

Establishing a Field Count during Table

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 18:28:54

Been banging my head at this one:

[green]if exists
(select * from Xerxes.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_COBMembr_Test]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tab_COBMembr_Test

CREATE TABLE tab_COBMembr_Test
(
[COBID] varchar (10) NULL,
[Name] varchar (30) NULL,
[MemberNum] varchar (16) NULL,
[MedPrimary] varchar (1) NULL,
[StartDate] varchar (10) NULL,
[TermDate] varchar (10) NULL,
[COUNTNAME] float NULL
)

INSERT INTO tab_COBMembr_Test
SELECT COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, Count(Name) AS CountName
FROM tab_COBMembr_Work
GROUP BY COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
;[/green]
and getting this:

Server: Msg 213, Level 16, State 4, Line 18
Insert Error: Column name or number of supplied values does not match table definition.

If there's a problem on this, I can't see it. Can you?

Any help would be appreciated.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-27 : 18:36:19

It works fine for me. Here is what I used:



CREATE TABLE tab_COBMembr_Test
(
[COBID] varchar (10) NULL,
[Name] varchar (30) NULL,
[MemberNum] varchar (16) NULL,
[MedPrimary] varchar (1) NULL,
[StartDate] varchar (10) NULL, 
[TermDate] varchar (10) NULL, 
[COUNTNAME] float NULL
)

INSERT INTO tab_COBMembr_Test
SELECT 'COBID', 'Name', 'MemberNum', 'M', 'STARTDATE', 'TERMDATE', 1

select * from tab_COBMembr_Test

drop table tab_COBMembr_Test

BTW, you should always specify the columns in your insert:

INSERT INTO Table1 (Column1, Column2, Column3) <-- here
SELECT Column1, Column2, Column3
FROM Table1

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 18:37:43

Thanks!

<--best I can do for a salute....

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-28 : 09:48:35

Tara,

Whoops...hold on a minute. I looked at this again and I saw that you used the numeral 1 in place of where I was attempting to establish a count(*). My question is...why can't I use a count(*) there?

Thanks...

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-28 : 11:35:32

Well you can. I just don't have your tab_COBMembr_Work table to do the real insert. So I had to fake it. The count should work fine.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-28 : 14:29:18

quote:
Originally posted by tduggan

Well you can. I just don't have your tab_COBMembr_Work table to do the real insert. So I had to fake it. The count should work fine.

Tara

Well Tara, it ain't workin' for me

Here's the code re-written to include the field list for the INSERT table:

if exists
(select * from Xerxes.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_COBMembr_Test]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tab_COBMembr_Test

CREATE TABLE tab_COBMembr_Test
(
[COBID] varchar (10) NULL,
[Name] varchar (30) NULL,
[MemberNum] varchar (16) NULL,
[MedPrimary] varchar (1) NULL,
[StartDate] varchar (10) NULL,
[TermDate] varchar (10) NULL,
[CountName] float NULL
)
;
INSERT INTO tab_COBMembr_Test(COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, CountName)
SELECT COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, Count(Name) AS CountName
FROM tab_COBMembr_Work
GROUP BY COBID, Name, MemberNum
ORDER BY CountName DESC
;

I'm still getting this:

Server: Msg 207, Level 16, State 1, Line 18
Invalid column name 'CountName'.

Here's a couple sample lines of tab_COBMembr_Work (the feeder table)

COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
00YYY Clovis 000069 Y 05/15/2004 08/31/2004
00YYY Clovis 000069 N 09/20/2004 10/31/2004
00YYY Clovis 000069 Y 11/10/2004 12/31/2004
00ZZZ Julius 000274 N 07/04/2004 10/18/2004
00ZZZ Julius 000274 N 11/04/2004 12/11/2004
00ZZZ Julius 000274 N 12/31/2004 01/18/2005
00RRR Borris 003333 Y 05/18/2004 06/03/2004

Thanks for all your efforts...

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-28 : 14:47:40

Tara,

I discovered my error and learned something: You can't create a counter variable during an INSERT

Here's the corrected code.

if exists
(select * from Xerxes.dbo.sysobjects
where id = object_id(N'[Xerxes].[dbo].[tab_COBMembr_Test]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tab_COBMembr_Test

CREATE TABLE tab_COBMembr_Test
(
[COBID] varchar (10) NULL,
[Name] varchar (30) NULL,
[MemberNum] varchar (16) NULL,
[MedPrimary] varchar (1) NULL,
[StartDate] varchar (10) NULL,
[TermDate] varchar (10) NULL,
[CountName] float NULL
)
;
-----perform INSERT first
INSERT INTO tab_COBMembr_Test(COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE)
SELECT COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
FROM tab_COBMembr_Work;

-----create counter on target table
SELECT COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE, Count(Name) AS CountName
FROM tab_COBMembr_Test
GROUP BY COBID, Name, MemberNum, MedPrimary, STARTDATE, TERMDATE
ORDER BY CountName DESC
;

Thanks for your trouble anyways.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources