Stored Procedure - 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

Stored Procedure

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-10 : 10:03:44

Hi,

I want to create a stored procedure to copy over all the data received from the select query to another table called tblprojectpatients. Assuming we have identical fields in the table and query

SELECT DISTINCT 
                      TOP (100) PERCENT t3.goldPatID AS ImpGoldPatid, t2.prac_no, t2.prac_eid, t1.goldpracid, t1.vision_patid, t1.yob, t1.mob, 
                      dbo.lkupPatientGender.Sex AS gender
FROM         GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
                      GPRDData.dbo.tblpatdetails AS t1 ON t2.GoldPracID = t1.goldpracid INNER JOIN
                      dbo.tblImport AS t3 ON t1.goldpatid = t3.goldPatID INNER JOIN
                      dbo.lkupPatientGender ON t1.gender = dbo.lkupPatientGender.Code
ORDER BY ImpGoldPatid

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-10 : 10:08:21

Just add an INSERT statement before the SELECT.

INSERT INTO tblprojectpatients

SELECT DISTINCT 
                      TOP (100) PERCENT t3.goldPatID AS ImpGoldPatid, t2.prac_no, t2.prac_eid, t1.goldpracid, t1.vision_patid, t1.yob, t1.mob, 
                      dbo.lkupPatientGender.Sex AS gender
FROM         GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
                      GPRDData.dbo.tblpatdetails AS t1 ON t2.GoldPracID = t1.goldpracid INNER JOIN
                      dbo.tblImport AS t3 ON t1.goldpatid = t3.goldPatID INNER JOIN
                      dbo.lkupPatientGender ON t1.gender = dbo.lkupPatientGender.Code

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-11 : 07:30:05

Tried this and it failed -

Error is - Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

<code>INSERT INTO dbo.tblProjectpatients
SELECT DISTINCT
TOP (100) PERCENT t3.ProjectID, t2.prac_eid, t2.prac_no, t1.goldpracid, t3.goldPatID, t1.vision_patid,t1.yob AS BirthYear,
t1.mob AS BirthMonth, dbo.lkupPatientGender.Sex, t3.category_name, t3.LoadRef
FROM GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
GPRDData.dbo.tblpatdetails AS t1 ON t2.GoldPracID = t1.goldpracid INNER JOIN
dbo.tblImport AS t3 ON t1.goldpatid = t3.goldPatID INNER JOIN
dbo.lkupPatientGender ON t1.gender = dbo.lkupPatientGender.Code
ORDER BY t3.goldPatID </code>

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 07:38:21

Have a look at your select-list and then have a look at the columns in your destination table.
They don't fit.

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-11 : 09:01:53

quote:
Originally posted by dr223

Tried this and it failed -

Error is - Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

<code>INSERT INTO dbo.tblProjectpatients
SELECT DISTINCT
TOP (100) PERCENT t3.ProjectID, t2.prac_eid, t2.prac_no, t1.goldpracid, t3.goldPatID, t1.vision_patid,t1.yob AS BirthYear,
t1.mob AS BirthMonth, dbo.lkupPatientGender.Sex, t3.category_name, t3.LoadRef
FROM GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
GPRDData.dbo.tblpatdetails AS t1 ON t2.GoldPracID = t1.goldpracid INNER JOIN
dbo.tblImport AS t3 ON t1.goldpatid = t3.goldPatID INNER JOIN
dbo.lkupPatientGender ON t1.gender = dbo.lkupPatientGender.Code
ORDER BY t3.goldPatID </code>

Well you had mentioned

quote:
Assuming we have identical fields in the table and query

Your assumption seems to be wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources