Insert Into Temp Table problem

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 2005 Forums

Transact-SQL (2005)

Insert Into Temp Table problem

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-29 : 13:29:03

/*create a temp table to store the pending paq's*/
Declare @PAQPending Table
(
pi_enterprise_id char(5),
pi_practice_id char(4),
pi_signoff_status char(1),
pi_use_documents_flag int,
pi_use_notes_flag int,
pi_use_images_flag int,
pi_use_ics_flag int,
pi_use_labs_flag int,
pi_provider_type char(1)
)
/*insert results of paq stored procedure into the temp table*/
Insert @PAQPending (pi_enterprise_id,pi_practice_id,pi_signoff_status,
pi_use_documents_flag,pi_use_notes_flag,pi_use_images_flag,
pi_use_ics_flag,pi_use_labs_flag,pi_provider_type)

EXEC emr_PAQ_Pending_Report '00001', '0001', 'P', 1, 1, 1, 1, 1, 'O'

select * from PAQPending

I am getting this error which doesn't make sense because the columns I insert are the same that I declared. What else could this mean?

Msg 213, Level 16, State 7, Procedure emr_PAQ_Pending_Report, Line 502
Insert Error: Column name or number of supplied values does not match table definition.

Thanks in Advance!
Sherri

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-29 : 13:34:46

The columns also have to match the output of this:
EXEC emr_PAQ_Pending_Report '00001', '0001', 'P', 1, 1, 1, 1, 1, 'O'

both the output column order as well as the datatypes

Be One with the Optimizer
TG

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-29 : 13:36:35

Ok I know this is a big piece of code but I wasn't sure how much you need to see to help but I am bolding the part that I think you are referring to. Its towards the bottom. Can you confirm? Thanks.

USE [NGProd]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[emr_PAQ_Pending_Report](
@pi_enterprise_id char(5), -- Enterprise ID
@pi_practice_id char(4), -- Practice ID
/*@pi_provider_id uniqueidentifier, -- Provider ID*/
@pi_signoff_status char(1), -- 'P'ending / 'R'ejected
@pi_use_documents_flag int, -- Use Documents: 1/0 (bit)
@pi_use_notes_flag int, -- Use Notes: 1/0 (bit)
@pi_use_images_flag int, -- Use Images: 1/0 (bit)
@pi_use_ics_flag int, -- Use ICS: 1/0 (bit)
@pi_use_labs_flag int, -- Use Labs: 1/0 (bit)
@pi_provider_type char(1) -- 'E'ncounter / 'O'rdering
)
AS

set nocount on
set transaction isolation level read uncommitted

DECLARE @temp_paq TABLE (
[Item Type] varchar(1) NOT NULL,
[person_id] uniqueidentifier NOT NULL,
[enc_id] uniqueidentifier NULL,
[enc_timestamp] datetime NULL,
[Unique ID] uniqueidentifier NOT NULL,
[Item Name] varchar(255) NULL,
[Item Desc] varchar(255) NOT NULL,
[Format] varchar(10) NOT NULL,
[created_by] int NOT NULL,
[create_timestamp] datetime NOT NULL,
[modified_by] int NOT NULL,
[modify_timestamp] datetime NOT NULL,
[app_created_by] varchar(8) NULL,
[perm_pos] int NULL
)
-- Patient Documents

IF @pi_use_documents_flag = 1
BEGIN
INSERT INTO @temp_paq
SELECT 'D' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
d.document_id As 'Unique ID',
d.document_desc As 'Item Name',
d.document_file As 'Item Desc',
d.file_format As 'Format',
d.created_by,
d.create_timestamp,
d.modified_by,
d.modify_timestamp,
d.app_created_by,
s.perm_pos
FROM patient_encounter e
INNER JOIN patient_documents d
ON e.enterprise_id = d.enterprise_id
AND e.practice_id = d.practice_id
AND e.person_id = d.person_id
AND e.enc_id = d.enc_id
LEFT OUTER JOIN security_items s
ON d.document_desc = s.description
AND s.item_type = 'D'
AND s.delete_ind = 'N'
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND d.paq_provider_id = @pi_provider_id*/
AND d.signoff_status = @pi_signoff_status

INSERT INTO @temp_paq
SELECT 'D' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
d.document_id As 'Unique ID',
d.document_desc As 'Item Name',
d.document_file As 'Item Desc',
d.file_format As 'Format',
d.created_by,
d.create_timestamp,
d.modified_by,
d.modify_timestamp,
d.app_created_by,
s.perm_pos
FROM patient_encounter e
INNER JOIN patient_documents d
ON e.enterprise_id = d.enterprise_id
AND e.practice_id = d.practice_id
AND e.person_id = d.person_id
AND e.enc_id = d.enc_id
LEFT OUTER JOIN security_items s
ON d.document_desc = s.description
AND s.item_type = 'D'
AND s.delete_ind = 'N'
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND e.rendering_provider_id = @pi_provider_id */
AND d.paq_provider_id IS NULL
AND d.signoff_status = @pi_signoff_status

END

-- Patient Notes
IF @pi_use_notes_flag = 1
BEGIN
INSERT INTO @temp_paq
SELECT 'N' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
n.note_id As 'Unique ID',
RTRIM(n.table_name) + '.' + RTRIM(n.field_name) As 'Item Name',
CONVERT(CHAR(36), n.note_id) + '.rtf' As 'Item Desc',
'RTF' As 'Format',
n.created_by,
n.create_timestamp,
n.modified_by,
n.modify_timestamp,
NULL,
t.perm_pos
FROM patient_encounter e
INNER JOIN patient_notes n
ON e.enc_id = n.enc_id
LEFT OUTER JOIN templates t
ON substring(n.table_name,1,len(n.table_name)-1) = t.template_name
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND n.paq_provider_id = @pi_provider_id*/
AND n.signoff_status = @pi_signoff_status

INSERT INTO @temp_paq
SELECT 'N' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
n.note_id As 'Unique ID',
RTRIM(n.table_name) + '.' + RTRIM(n.field_name) As 'Item Name',
CONVERT(CHAR(36), n.note_id) + '.rtf' As 'Item Desc',
'RTF' As 'Format',
n.created_by,
n.create_timestamp,
n.modified_by,
n.modify_timestamp,
NULL,
t.perm_pos
FROM patient_encounter e
INNER JOIN patient_notes n
ON e.enc_id = n.enc_id
LEFT OUTER JOIN templates t
ON substring(n.table_name,1,len(n.table_name)-1) = t.template_name
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND e.rendering_provider_id = @pi_provider_id */
AND n.signoff_status = @pi_signoff_status
AND n.paq_provider_id IS NULL
END

-- Patient Images
IF @pi_use_images_flag = 1
BEGIN
INSERT INTO @temp_paq
SELECT 'I' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
i.image_id As 'Unique ID',
i.image_desc As 'Item Name',
i.image_file As 'Item Desc',
'IMG' As 'Format',
i.created_by,
i.create_timestamp,
i.modified_by,
i.modify_timestamp,
NULL,
0
FROM patient_encounter e
INNER JOIN patient_images i
ON e.enc_id = i.enc_id
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND i.paq_provider_id = @pi_provider_id*/
AND i.signoff_status = @pi_signoff_status

INSERT INTO @temp_paq
SELECT 'I' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
i.image_id As 'Unique ID',
i.image_desc As 'Item Name',
i.image_file As 'Item Desc',
'IMG' As 'Format',
i.created_by,
i.create_timestamp,
i.modified_by,
i.modify_timestamp,
NULL,
0
FROM patient_encounter e
INNER JOIN patient_images i
ON e.enc_id = i.enc_id
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND e.rendering_provider_id = @pi_provider_id */
AND i.paq_provider_id IS NULL
AND i.signoff_status = @pi_signoff_status

END

-- ICS Images
IF @pi_use_ics_flag = 1
BEGIN
INSERT INTO @temp_paq
SELECT 'S' As 'Item Type',
i.person_id,
e.enc_id,
e.enc_timestamp,
i.document_id As 'Unique ID',
CASE (d.description)
WHEN '' THEN t.description
WHEN NULL THEN t.description
ELSE d.description
END As 'Item Name',
g.file_name As 'Item Desc',
'ICS' As 'Format',
i.created_by,
i.create_timestamp,
i.modified_by,
i.modify_timestamp,
NULL,
0
FROM patient_ics_images i
INNER JOIN document d
ON d.document_id = i.document_id
AND d.delete_ind = 'N'
INNER JOIN doc_type_mstr t
ON t.doc_type_id = d.doc_type_id
AND t.delete_ind = 'N'
INNER JOIN page g
ON g.document_id = d.document_id
AND g.sequence_nbr = 1
INNER JOIN person p
ON p.person_id = i.person_id
LEFT OUTER JOIN patient_encounter e
ON e.enc_id = i.enc_id
WHERE i.signoff_status = @pi_signoff_status
AND i.enterprise_id = @pi_enterprise_id
AND i.practice_id = @pi_practice_id
/*AND i.provider_id = @pi_provider_id */

INSERT INTO @temp_paq
SELECT 'S' As 'Item Type',
i.person_id,
e.enc_id,
e.enc_timestamp,
i.document_id As 'Unique ID',
CASE (d.description)
WHEN '' THEN t.description
WHEN NULL THEN t.description
ELSE d.description
END As 'Item Name',
g.file_name As 'Item Desc',
'ICS' As 'Format',
i.created_by,
i.create_timestamp,
i.modified_by,
i.modify_timestamp,
NULL,
0
FROM patient_ics_images i
INNER JOIN document d
ON d.document_id = i.document_id
AND d.delete_ind = 'N'
INNER JOIN doc_type_mstr t
ON t.doc_type_id = d.doc_type_id
AND t.delete_ind = 'N'
INNER JOIN page g
ON g.document_id = d.document_id
AND g.sequence_nbr = 1
INNER JOIN person p
ON p.person_id = i.person_id
LEFT OUTER JOIN patient_encounter e
ON e.enc_id = i.enc_id
WHERE i.signoff_status = @pi_signoff_status
AND i.enterprise_id = @pi_enterprise_id
AND i.practice_id = @pi_practice_id
/*AND e.rendering_provider_id = @pi_provider_id */
AND i.provider_id IS NULL

END

-- Patient Lab Results
IF @pi_signoff_status = 'R'
SELECT @pi_use_labs_flag = 0

IF @pi_use_labs_flag = 1
BEGIN

-- Create and Populate @lab_flag table variable so don't have to do function call
declare @lab_flag table
(abnorm_flags char(2) not null, result char(1) not null)

insert into @lab_flag values ('HH' , 5 )
insert into @lab_flag values ( '>' , 5 )
insert into @lab_flag values ( 'H' , 4 )
insert into @lab_flag values ( 'LL' , 3 )
insert into @lab_flag values ( 'L' , 3 )
insert into @lab_flag values ( '<' , 3 )
insert into @lab_flag values ( 'AA' , 2 )
insert into @lab_flag values ( 'A' , 2 )
insert into @lab_flag values ( 'I' , 2 )
insert into @lab_flag values ( 'U' , 2 )
insert into @lab_flag values ( 'D' , 2 )
insert into @lab_flag values ( 'B' , 2 )
insert into @lab_flag values ( 'W' , 2 )
insert into @lab_flag values ( 'R' , 2 )
insert into @lab_flag values ( 'N' , 1 )

-- Use Ordering Provider
IF @pi_provider_type = 'O'
BEGIN
INSERT INTO @temp_paq
SELECT 'L' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
l.order_num As 'Unique ID',
l.test_desc,
MAX(isnull(lt.result,0)) As 'Item Desc',
'LAB' As 'Format',
l.created_by,
l.create_timestamp,
l.modified_by,
l.modify_timestamp,
NULL,
0
FROM patient_encounter e
INNER JOIN lab_nor l
ON e.enc_id = l.enc_id
LEFT OUTER JOIN lab_results_obr_p r
ON l.order_num = r.ngn_order_num
LEFT OUTER JOIN lab_results_obx x
ON r.unique_obr_num = x.unique_obr_num
LEFT OUTER JOIN @lab_flag lt
ON x.abnorm_flags = lt.abnorm_flags
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND l.paq_provider_id = @pi_provider_id*/
AND l.ngn_status = 'Assigned'
AND l.delete_ind = 'N'
GROUP BY e.person_id, e.enc_id,
e.enc_timestamp, l.order_num,
l.test_desc,
l.created_by, l.create_timestamp,
l.modified_by, l.modify_timestamp

INSERT INTO @temp_paq
SELECT 'L' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
l.order_num As 'Unique ID',
l.test_desc,
MAX(isnull(lt.result,0)) As 'Item Desc',
'LAB' As 'Format',
l.created_by,
l.create_timestamp,
l.modified_by,
l.modify_timestamp,
NULL,
0
FROM patient_encounter e
INNER JOIN lab_nor l
ON e.enc_id = l.enc_id
LEFT OUTER JOIN lab_results_obr_p r
ON l.order_num = r.ngn_order_num
LEFT OUTER JOIN lab_results_obx x
ON r.unique_obr_num = x.unique_obr_num
LEFT OUTER JOIN @lab_flag lt
ON x.abnorm_flags = lt.abnorm_flags
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND l.ordering_provider = @pi_provider_id */
AND l.paq_provider_id IS NULL
AND l.ngn_status = 'Assigned'
AND l.delete_ind = 'N'
GROUP BY e.person_id, e.enc_id,
e.enc_timestamp, l.order_num,
l.test_desc,
l.created_by, l.create_timestamp,
l.modified_by, l.modify_timestamp
END
ELSE
BEGIN

-- Use Encounter Provider
INSERT INTO @temp_paq
SELECT 'L' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
l.order_num As 'Unique ID',
l.test_desc,
MAX(isnull(lt.result,0)) As 'Item Desc',
'LAB' As 'Format',
l.created_by,
l.create_timestamp,
l.modified_by,
l.modify_timestamp,
NULL,
0
FROM patient_encounter e
INNER JOIN lab_nor l
ON e.enc_id = l.enc_id
INNER JOIN person p
ON e.person_id = p.person_id
LEFT OUTER JOIN lab_results_obr_p r
ON l.order_num = r.ngn_order_num
LEFT OUTER JOIN lab_results_obx x
ON r.unique_obr_num = x.unique_obr_num
LEFT OUTER JOIN @lab_flag lt
ON x.abnorm_flags = lt.abnorm_flags
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND l.paq_provider_id = @pi_provider_id*/
AND l.ngn_status = 'Assigned'
AND l.delete_ind = 'N'

GROUP BY e.person_id, e.enc_id,
e.enc_timestamp, l.order_num,
l.test_desc,
l.created_by, l.create_timestamp,
l.modified_by, l.modify_timestamp

INSERT INTO @temp_paq
SELECT 'L' As 'Item Type',
e.person_id,
e.enc_id,
e.enc_timestamp,
l.order_num As 'Unique ID',
l.test_desc,
MAX(isnull(lt.result,0)) As 'Item Desc',
'LAB' As 'Format',
l.created_by,
l.create_timestamp,
l.modified_by,
l.modify_timestamp,
NULL,
0
FROM patient_encounter e
INNER JOIN lab_nor l
ON e.enc_id = l.enc_id
INNER JOIN person p
ON e.person_id = p.person_id
LEFT OUTER JOIN lab_results_obr_p r
ON l.order_num = r.ngn_order_num
LEFT OUTER JOIN lab_results_obx x
ON r.unique_obr_num = x.unique_obr_num
LEFT OUTER JOIN @lab_flag lt
ON x.abnorm_flags = lt.abnorm_flags
WHERE e.enterprise_id = @pi_enterprise_id
AND e.practice_id = @pi_practice_id
/*AND e.rendering_provider_id = @pi_provider_id */
AND l.paq_provider_id IS NULL
AND l.ngn_status = 'Assigned'
AND l.delete_ind = 'N'
GROUP BY e.person_id, e.enc_id,
e.enc_timestamp, l.order_num,
l.test_desc,
l.created_by, l.create_timestamp,
l.modified_by, l.modify_timestamp

END
END -- Labs

SELECT t."Item Type", t.person_id, t.enc_id, t.enc_timestamp,
t."Unique ID", t."Item Name", t."Item Desc", t."Format",
p.last_name, p.first_name, p.middle_name,
t.created_by, t.create_timestamp, t.modified_by, t.modify_timestamp,
t.app_created_by, t.perm_pos
FROM @temp_paq t
JOIN person p
ON p.person_id = t.person_id set nocount off
set transaction isolation level read committed

quote:
Originally posted by TG

The columns also have to match the output of this:
EXEC emr_PAQ_Pending_Report '00001', '0001', 'P', 1, 1, 1, 1, 1, 'O'

both the output column order as well as the datatypes

Be One with the Optimizer
TG

Thanks in Advance!
Sherri

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-29 : 13:43:07

Not sure why you need someone to confirm this. Count the columns in your SELECT statmement yourself. I see 17. There are only 9 in your table variable and INSERT column list.

Be One with the Optimizer
TG

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-29 : 13:49:41

Well I need to confirm because I am not that smart at this stuff yet. Thanks for your help.

quote:
Originally posted by TG

Not sure why you need someone to confirm this. Count the columns in your SELECT statmement yourself. I see 17. There are only 9 in your table variable and INSERT column list.

Be One with the Optimizer
TG

Thanks in Advance!
Sherri

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-29 : 13:54:57

Sorry if that was "snippy"

I guess I've got the Friday afternoon, I wanna go home, blues.

You just need to make your table variable columns and your insert column list match the SELECT list of the stored procedure.

Be One with the Optimizer
TG

sross81
Posting Yak Master

228 Posts

Posted - 2009-05-29 : 14:02:08

No problem. I am ready for the weekend too! I think I am getting this whole thing figured out. I learn something new everyday with this stuff :).

quote:
Originally posted by TG

Sorry if that was "snippy" I guess I've got the Friday afternoon, I wanna go home, blues.

You just need to make your table variable columns and your insert column list match the SELECT list of the stored procedure.

Be One with the Optimizer
TG

Thanks in Advance!
Sherri

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources