Error to create table - 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

Error to create table

Author

Topic

scmay
Starting Member

22 Posts

Posted - 2007-06-18 : 00:53:10

CREATE PROCEDURE dbo.spCreateObBabyEpisodeArchive AS

DECLARE @TableName as varchar(400)
DECLARE @SQL as varchar(8000)

SET @TableName = 'ObBabyEpisode' + CONVERT(varchar,REPLACE (CONVERT(VARCHAR, GETDATE(), 106) , ' ', ''))
SET @SQL = 'CREATE TABLE ' + @TableName + '
(
AdmitSource INT NOT NULL DEFAULT 0,
Anaesthesia3rd INT NOT NULL DEFAULT 0,
Anaesthetist3rd INT NOT NULL DEFAULT 0,
Apgar1 INT NOT NULL DEFAULT 0,
Apgar10 INT NOT NULL DEFAULT 99,
Apgar5 INT NOT NULL DEFAULT 0,
ApgarOther VARCHAR(20) NOT NULL DEFAULT '' '',
AttAccoucher VARCHAR(30) NOT NULL DEFAULT '' '',
AttAccoucherRank VARCHAR(30) NOT NULL DEFAULT '' '',
AttMidwife VARCHAR(30) NOT NULL DEFAULT '' '',
AttMidwifeRank VARCHAR(30) NOT NULL DEFAULT '' '',
AttOthers VARCHAR(30) NOT NULL DEFAULT '' '',
AttOthersRank VARCHAR(30) NOT NULL DEFAULT '' '',
Augmentation VARCHAR(30) NOT NULL DEFAULT '' '',
BabyAdmitDr INT NOT NULL DEFAULT 0,
BabyDisDate VARCHAR(12) NOT NULL DEFAULT '' '',
BabyDisDest INT NOT NULL DEFAULT 0,
BabyDisFeed INT NOT NULL DEFAULT 0,
BabyDisHosp INT NOT NULL DEFAULT 0,
BabyDisStaff INT NOT NULL DEFAULT 0,
BabyDisTime VARCHAR(7) NOT NULL DEFAULT '' '',
BabyEdd VARCHAR(12) NOT NULL DEFAULT '' '',
BabyGender VARCHAR(20) NOT NULL DEFAULT '' '',
BabyLos INT NOT NULL DEFAULT 0,
BabyNum INT NOT NULL DEFAULT 0,
BabyPostMeds VARCHAR(30) NOT NULL DEFAULT '' '',
BabyRecStatus VARCHAR(50) NOT NULL DEFAULT '' '',
BabyRegStaff INT NOT NULL DEFAULT 0,
BabyReqMandatoryFields VARCHAR(250) NOT NULL DEFAULT '' '',
BabyScnNicu VARCHAR(6) NOT NULL DEFAULT '' '',
BabyTreatment VARCHAR(80) NOT NULL DEFAULT '' '',
BabyUrno VARCHAR(20) NOT NULL DEFAULT '' '',
BabyWard INT NOT NULL DEFAULT 0,
BirthAnaes VARCHAR(30) NOT NULL DEFAULT '' '',
BirthDate VARCHAR(12) NOT NULL DEFAULT '' '',
BirthDefect VARCHAR(80) NOT NULL DEFAULT '' '',
BirthLength DECIMAL NOT NULL DEFAULT 0,
BirthMode INT NOT NULL DEFAULT 0,
BirthPlace INT NOT NULL DEFAULT 0,
BirthStatus VARCHAR(55) NOT NULL DEFAULT '' '',
BirthTime VARCHAR(7) NOT NULL DEFAULT '' '',
BirthTrauma VARCHAR(30) NOT NULL DEFAULT '' '',
BirthType INT NOT NULL DEFAULT 0,
BirthWeight VARCHAR(10) NOT NULL DEFAULT '' '',
CaesarType VARCHAR(20) NOT NULL DEFAULT '' '',
CmpAccPaid DECIMAL NOT NULL DEFAULT 0,
CmpAccRcvd DECIMAL NOT NULL DEFAULT 0,
CmpCategory INT NOT NULL DEFAULT 0,
CmpDisDate VARCHAR(12) NOT NULL DEFAULT '' '',
CmpDisFeed INT NOT NULL DEFAULT 0,
CmpFeedChange VARCHAR(6) NOT NULL DEFAULT '' '',
CmpFeedReason INT NOT NULL DEFAULT 0,
CmphomeConsult INT NOT NULL DEFAULT 0,
CmpHospConsult INT NOT NULL DEFAULT 0,
CmpNotHome INT NOT NULL DEFAULT 0,
CmpOutPhone INT NOT NULL DEFAULT 0,
CmpOutreachTot INT NOT NULL DEFAULT 0,
CmpPhoneConsult INT NOT NULL DEFAULT 0,
CmpProblem INT NOT NULL DEFAULT 0,
CmpProvider INT NOT NULL DEFAULT 0,
CmpReadmitBaby INT NOT NULL DEFAULT 0,
CmpReadmitMother INT NOT NULL DEFAULT 0,
CmpReferBaby INT NOT NULL DEFAULT 0,
CmpReferMother INT NOT NULL DEFAULT 0,
CmpVisitTot INT NOT NULL DEFAULT 0,
CordAnalysisTime varchar(25) NOT NULL DEFAULT '' '',
CordBaseExcess varchar(6) NOT NULL DEFAULT '' '',
CordBlood VARCHAR(6) NOT NULL DEFAULT '' '',
CordComps VARCHAR(55) NOT NULL DEFAULT '' '',
CordInsertion VARCHAR(55) NOT NULL DEFAULT '' '',
CordLactate VARCHAR(6) NOT NULL DEFAULT '' '',
CordPh VARCHAR(6) NOT NULL DEFAULT '' '',
CordStemBlood VARCHAR(6) NOT NULL DEFAULT '' '',
CordVessels INT NOT NULL DEFAULT 0,
DischWeight varchar(10) NOT NULL DEFAULT '' '',
DisFeedReason INT NOT NULL DEFAULT 0,
EndDate3 VARCHAR(12) NOT NULL DEFAULT '' '',
EndTime3 VARCHAR(6) NOT NULL DEFAULT '' '',
EpisodeID INT NOT NULL ,
EstGest DECIMAL NOT NULL DEFAULT 0,
EstResps INT NOT NULL DEFAULT 0,
ExclusiveBF VARCHAR(6) NOT NULL DEFAULT '' '',
FirstFeedDate VARCHAR(12) NOT NULL DEFAULT '' '',
FirstFeedMode INT,
FirstFeedTime VARCHAR(6) NOT NULL DEFAULT '' '',
FoetMon VARCHAR(30) NOT NULL DEFAULT '' '',
ForcepType VARCHAR(100) NOT NULL DEFAULT '' '',
HeadCircm DECIMAL,
HearTestDate VARCHAR(12) NOT NULL DEFAULT '' '',
HearTestResult VARCHAR(20) NOT NULL DEFAULT '' '',
HepBvDate VARCHAR(12) NOT NULL DEFAULT '' '',
HepBvTime VARCHAR(6) NOT NULL DEFAULT '' '',
HindleakDate VARCHAR(55) NOT NULL DEFAULT '' '',
HindleakTime VARCHAR(6) NOT NULL DEFAULT '' '',
ID INT NOT NULL ,
ImmGnDate VARCHAR(12) NOT NULL DEFAULT '' '',
ImmGnTime VARCHAR(6) NOT NULL DEFAULT '' '',
InductionDate VARCHAR(12) NOT NULL DEFAULT '' '',
InductionTime VARCHAR(6) NOT NULL DEFAULT '' '',
InductMain INT,
InductMode VARCHAR(30) NOT NULL DEFAULT '' '',
InductOther VARCHAR(250) NOT NULL DEFAULT '' '',
IntendedChanged VARCHAR(55) NOT NULL DEFAULT '' '',
IntendedPlace VARCHAR(30) NOT NULL DEFAULT '' '',
IntendedSpecify VARCHAR(55) NOT NULL DEFAULT '' '',
IntendedYn VARCHAR(6) NOT NULL DEFAULT '' '',
KonakDate VARCHAR(12) NOT NULL DEFAULT '' '',
KonakRoute VARCHAR(10) NOT NULL DEFAULT '' '',
LabAnalgesia VARCHAR(30) NOT NULL DEFAULT '' '',
LabComps VARCHAR(60) NOT NULL DEFAULT '' '',
LabDrugs VARCHAR(30) NOT NULL DEFAULT '' '',
LabourTime1 VARCHAR(6) NOT NULL DEFAULT '' '',
LabourTime2 VARCHAR(6) NOT NULL DEFAULT '' '',
LabourTime3 VARCHAR(6) NOT NULL DEFAULT '' '',
LastUpdate VARCHAR(55) NOT NULL DEFAULT getdate(),
Liquor INT NOT NULL DEFAULT 0,
MembDate VARCHAR(30) NOT NULL DEFAULT '' '',
MembRupture VARCHAR(55) NOT NULL DEFAULT '' '',
MembTime VARCHAR(6) NOT NULL DEFAULT '' '',
NeoMorb VARCHAR(80) NOT NULL DEFAULT '' '',
NewBornScr VARCHAR(12) NOT NULL DEFAULT '' '',
ObsAccClass VARCHAR(10) NOT NULL DEFAULT '' '',
ObsAdmitDate VARCHAR(12) NOT NULL DEFAULT '' '',
ObsAdmitTime VARCHAR(6) NOT NULL DEFAULT '' '',
OnsetDate1 VARCHAR(12) NOT NULL DEFAULT '' '',
OnsetDate2 VARCHAR(12) NOT NULL DEFAULT '' '',
OnsetLab VARCHAR(30) NOT NULL DEFAULT '' '',
OnsetTime1 VARCHAR(6) NOT NULL DEFAULT '' '',
OnsetTime2 VARCHAR(6) NOT NULL DEFAULT '' '',
OpdelAnaesthetist INT NOT NULL DEFAULT 0,
OpdelDilation DECIMAL NOT NULL DEFAULT 0,
OpdelMain INT NOT NULL DEFAULT 0,
OpdelNature VARCHAR(40) NOT NULL DEFAULT '' '',
OpdelOther VARCHAR(30) NOT NULL DEFAULT '' '',
Oxytocic3 VARCHAR(30) NOT NULL DEFAULT '' '',
PassedMec VARCHAR(6) NOT NULL DEFAULT '' '',
PassedUrine VARCHAR(6) NOT NULL DEFAULT '' '',
PdcuBatchError VARCHAR(250) NOT NULL DEFAULT '' '',
PdcuBatchID INT NOT NULL DEFAULT '' '',
PeriAnaes INT NOT NULL DEFAULT '' '',
PeriStatus INT NOT NULL DEFAULT '' '',
PeriSuturedBy INT NOT NULL DEFAULT '' '',
PlacentaAbnorm VARCHAR(100) NOT NULL DEFAULT '' '',
PlacentaDelMode VARCHAR(55) NOT NULL DEFAULT '' '',
PlacentaMem VARCHAR(55) NOT NULL DEFAULT '' '',
Position VARCHAR(40) NOT NULL DEFAULT '' '',
Presentation INT NOT NULL DEFAULT '' '',
RefHospital INT NOT NULL DEFAULT '' '',
RegistrarFlag INT NOT NULL DEFAULT '' '',
Resus VARCHAR(60) NOT NULL DEFAULT '' '',
ScalpLactateDate VARCHAR(50) NOT NULL DEFAULT '' '',
ScalpLactateTime VARCHAR(30) NOT NULL DEFAULT '' '',
ScalpLactateValue VARCHAR(30) NOT NULL DEFAULT '' '',
SkinContact VARCHAR(6) NOT NULL DEFAULT '' '',
SkinContactDate VARCHAR(12) NOT NULL DEFAULT '' '',
SkinContactReasonGT30 INT NOT NULL DEFAULT '' '',
SkinContactTime VARCHAR(6) NOT NULL DEFAULT '' '',
TotLabourTime VARCHAR(6) NOT NULL DEFAULT '' '',
UrNo VARCHAR(20) NOT NULL,
VCordBaseExcess VARCHAR(6) NOT NULL DEFAULT '' '',
VCordLactate VARCHAR(6) NOT NULL DEFAULT '' '',
VCordPh VARCHAR(6) NOT NULL DEFAULT '' ''
)'
EXEC @SQL
GO

When I run the query, I get this error
Server: Msg 203, Level 16, State 2, Line 172
The name 'CREATE TABLE ObBabyEpisode18Jun2007
(
AdmitSource INT NOT NULL DEFAULT 0,
Anaesthesia3rd INT NOT NULL DEFAULT 0,
Anaesthetist3rd INT NOT NULL DEFAULT 0,
Apgar1 INT NOT NULL DEFAULT 0,
Apgar10 INT NOT NULL DEFAULT 99,
Apgar5 INT NOT NULL DEFAULT 0,
ApgarOther VARCHAR(20) NOT NULL DEFAULT ' ',
AttAccoucher VARCHAR(30) NOT NULL DEFAULT ' ',
AttAccoucherRank VARCHAR...

Line 172 Points to EXEC @SQL

I cannot understand what is the error. Any clues?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-18 : 00:58:46

[code]
EXEC (@SQL)
[/code]

Why do you need to create the table dynamically ? Why not put the date/time as a column in the table ?

scmay
Starting Member

22 Posts

Posted - 2007-06-18 : 01:30:51

quote:
Originally posted by khtan

EXEC (@SQL)

Why do you need to create the table dynamically ? Why not put the date/time as a column in the table ?

KH

I need to create a script to archive ObBabyEpisode. Transferring records from ObBabyEpisode to ObBabyEpisode<date> table which is the archive table. Date would depend on the date that the script is run. Would you recommend a better solution? How can I put the date as a column when I want it to be part of the table name?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-18 : 01:38:05

quote:
How can I put the date as a column when I want it to be part of the table name?

The idea is to avoid dynamic table name. If you have the date as part of the primary key of the column, you don't have to create one table for each day. You only have to create the archive table once. When you need to archive the data, just move from the original table to the archive table. When inserting the record into the archive table, just need to pass in the date to archive_date column

create table ObBabyEpisode_archive
(
    archive_date datetime,
    . . . 
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources