October 1, 2007 at 11:58 am
H folks,
I have a need to write a stored procedure to insert a record in a table. The table has 255 columns, :w00t:
To insert a record in this table, I have to specify each column's value in the sp.
I am wondering if there is any smarter way to write/call the sp? repeating each column will be kind of silly.
Thanks.
October 1, 2007 at 12:04 pm
I don't see what you mean by repeat...
All I can say to you is that QA has the ability to generate the insert query templates. You can use that as a starting point. The rest will have to be done manually.
October 1, 2007 at 12:07 pm
Honestly your best bet really is to have 255 parameters. :blink: You could pass in a single parameter as either a long string or as XML and parse it out and insert it inside the procedure, however I don't think you'd save any performance or annoyance.
October 1, 2007 at 12:19 pm
You have a table with 255 columns? Sounds like a design issue with the table, not the procedure.
October 1, 2007 at 12:22 pm
Ya that too. If you post the table DDL, then maybe we can have a look an point you out in the right direction... assuming there's something wrong with the table.
October 1, 2007 at 12:56 pm
halifaxdal,
Try searching the web, for Codesmith. There should be some generic stored procedures generation templates which you can use. Codesmith should be able to help you generate CRUD statements/procedures. (C - Create/Insert, R - Retrieve/Select, U - Update, D - Delete).
Besides this, you can write your own "SELECT statements" to generate CRUD statement/procedures by using dbo.sysobjects and dbo.syscolumns.
Regards,
Wameng Vang
MCTS
October 1, 2007 at 1:15 pm
halifaxdal,
There is no smart way to deal with stupid design.
255 columns in a table is a clear sign of stupid design.
This thing must be fixed first.
_____________
Code for TallyGenerator
October 1, 2007 at 1:31 pm
Let me explain a little bit why there are 255 columns:
cn
instanceType
nTSecurityDescriptor
objectCategory
objectClass
objectSid
sAMAccountName
accountExpires
accountNameHistory
aCSPolicyName
adminCount
adminDescription
adminDisplayName
allowedAttributes
allowedAttributesEffective
allowedChildClasses
allowedChildClassesEffective
altSecurityIdentities
assistant
badPasswordTime
badPwdCount
bridgeheadServerListBL
c
canonicalName
catalogs
co
codePage
comment
company
controlAccessRights
countryCode
createTimeStamp
dBCSPwd
defaultClassStore
defaultLocalPolicyObject
department
description
desktopProfile
destinationIndicator
directReports
displayName
displayNamePrintable
distinguishedName
division
dNSHostName
dSASignature
dSCorePropagationData
dynamicLDAPServer
employeeID
extensionName
facsimileTelephoneNumber
flags
fromEntry
frsComputerReferenceBL
fRSMemberReferenceBL
fSMORoleOwner
garbageCollPeriod
generationQualifier
givenName
groupMembershipSAM
groupPriority
groupsToIgnore
homeDirectory
homeDrive
homePhone
homePostalAddress
info
initials
internationalISDNNumber
ipPhone
isCriticalSystemObject
isDeleted
isPrivilegeHolder
l
lastKnownParent
lastLogoff
lastLogon
legacyExchangeDN
lmPwdHistory
localeID
localPolicyFlags
location
lockoutTime
logonCount
logonHours
logonWorkstation
machineRole
managedBy
managedObjects
manager
masteredBy
maxStorage
memberOf
mhsORAddress
middleName
mobile
modifyTimeStamp
mS-DS-ConsistencyChildCount
mS-DS-ConsistencyGuid
mS-DS-CreatorSID
mSMQDigests
mSMQDigestsMig
mSMQSignCertificates
mSMQSignCertificatesMig
msNPAllowDialin
msNPCallingStationID
msNPSavedCallingStationID
msRADIUSCallbackNumber
msRADIUSFramedIPAddress
msRADIUSFramedRoute
msRADIUSServiceType
msRASSavedCallbackNumber
msRASSavedFramedIPAddress
msRASSavedFramedRoute
name
netbootGUID
netbootInitialization
netbootMachineFilePath
netbootMirrorDataFile
netbootSCPBL
netbootSIFFile
networkAddress
nonSecurityMemberBL
ntPwdHistory
o
objectGUID
objectVersion
operatingSystem
operatingSystemHotfix
operatingSystemServicePack
operatingSystemVersion
operatorCount
otherFacsimileTelephoneNumber
otherHomePhone
otherIpPhone
otherLoginWorkstations
otherMailbox
otherMobile
otherPager
otherTelephone
otherWellKnownObjects
ou
pager
partialAttributeDeletionList
partialAttributeSet
personalTitle
physicalDeliveryOfficeName
physicalLocationObject
policyReplicationFlags
possibleInferiors
postalAddress
postalCode
postOfficeBox
preferredDeliveryMethod
preferredOU
primaryGroupID
primaryInternationalISDNNumber
primaryTelexNumber
profilePath
proxiedObjectName
proxyAddresses
pwdLastSet
queryPolicyBL
registeredAddress
replPropertyMetaData
replUpToDateVector
repsFrom
repsTo
revision
rid
rIDSetReferences
sAMAccountType
scriptPath
sDRightsEffective
securityIdentifier
seeAlso
serverReferenceBL
servicePrincipalName
showInAddressBook
showInAdvancedViewOnly
sIDHistory
siteGUID
siteObjectBL
sn
st
street
streetAddress
subRefs
subSchemaSubEntry
supplementalCredentials
systemFlags
telephoneNumber
teletexTerminalIdentifier
telexNumber
terminalServer
textEncodedORAddress
thumbnailLogo
thumbnailPhoto
title
tokenGroups
tokenGroupsGlobalAndUniversal
tokenGroupsNoGCAcceptable
unicodePwd
url
userAccountControl
userCert
userCertificate
userParameters
userPassword
userPrincipalName
userSharedFolder
userSharedFolderOther
userSMIMECertificate
userWorkstations
uSNChanged
uSNCreated
uSNDSALastObjRemoved
USNIntersite
uSNLastObjRem
uSNSource
volumeCount
wbemPath
wellKnownObjects
whenChanged
whenCreated
wWWHomePage
x121Address
There is a need to replicate ad to sql, in our ad server, currently there are 255 properties, that's why there should be 255 columns in sql. Why there are so many properties in ad and why some of the name looks silly, I don't know, it's out of my control.
Anyway, I've finished the stupid sp, not much smart way, but not too silly way either.
October 1, 2007 at 1:49 pm
You did what you could, but this is obviously a bad design.
October 1, 2007 at 2:05 pm
This must be divided into tables:
cn
instanceType
nTSecurityDescriptor
- Instance
objectCategory
objectClass
objectSid
- Object
sAMAccountName
accountExpires
accountNameHistory
aCSPolicyName
- Account
adminCount
adminDescription
adminDisplayName
allowedAttributes
allowedAttributesEffective
allowedChildClasses
allowedChildClassesEffective
- AccountAdmin (not sure, names are not clear)
altSecurityIdentities
assistant
badPasswordTime
badPwdCount
- AccountSecurity
bridgeheadServerListBL
c
canonicalName
catalogs
co
codePage
- ???
etc.
I see at least 15 tables here.
But I can only guess here, without knowing of real meaning of data in columns I must be wrong about some of them.
_____________
Code for TallyGenerator
October 1, 2007 at 7:08 pm
There ws a time that because of boredness I made a script that make "almost" an exact copy of a table. I mean the schema of a table and later on made some adjustments that not just copy the schema of the table but also the data inside the table including PK and "some" constraints. in your case if you want to insert values not in all columns maybe you could try this to get all the data column and datatypes and delete those that are not needed to be inserted with values.
NOTE: Already edited the script for your need. Just msg me if there are datatypes that are not covered by the script so that I can work for it.
Ohh and BTW although i gave you a script doesnt mean i agree with the format of your table. They are right a table with 200+ columns is not a good table structure. But anyways you created 1 hope that script helps..
"-=Still Learning=-"
Lester Policarpio
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply