Need to change a table name from all sp's

Hey guys. I've a few sp's. This sp's are doing some inserts and updates in a
table
I need to setup a job to run every4 hours. And at
at 12 am, it's supposed to insert in table1
at 4 am, it's supposed to insert in table2 and truncate table1.
at 8 am, it's supposed to insert in table1
Please help me on writing this script. I can get the table name from
sysobjects and the text from sysomments but haven't reached farther than
that. Can someone think of a way to do this? Thank you..."Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:6B9DF2A1-0509-453E-9BC8-FD40A378DDF0@.microsoft.com...
> Hey guys. I've a few sp's. This sp's are doing some inserts and updates in
> a
> table
> I need to setup a job to run every4 hours. And at
> at 12 am, it's supposed to insert in table1
> at 4 am, it's supposed to insert in table2 and truncate table1.
> at 8 am, it's supposed to insert in table1
> Please help me on writing this script. I can get the table name from
> sysobjects and the text from sysomments but haven't reached farther than
> that. Can someone think of a way to do this? Thank you...
Why not setup 3 separate jobs, each that run once a day.
Job 1 at 12 am
Job 2 at 4 am
Job 3 at 8 am|||because the table being created is called by another stored proc. This store
d
proc is called by a report. That's why.|||"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:BF02A629-DFB7-44DD-9341-80B0D458C3B8@.microsoft.com...
> because the table being created is called by another stored proc. This
> stored
> proc is called by a report. That's why.
I don't understand what you're doing here.|||This is what's happening.
There is a report being accessed from a website. This report accesses a sp.
This sp has a select query inside it which goes to some tables and gets the
stuff. The table name I need to change is one of these tables. But as i said
in the first part, i need to update this table all the time and start from
scratch WITHOUT DOWN TIME. That's y i need to do wut i asked. Now, u think
there is a way where i can run a script to change these sp's so, that they
reflect different table name every 4 hours? Thank you.|||Based on your narrative it is hard to understand what you are trying to
accomplish.
On a cursory inspection, you are attempting something which normal
developers in general circumstances would not do. Can you elaborate you
requirements perhaps with a repro? Please refer to www.aspfaq.com/5006 for
more details on how you can present more information for others to help you.
Anith|||Allright, I'll paste part of the sp here. I really dont know what you guys
wnat me to paste here since my concern is with 5 sp's. instead of manually
going and replacing the names of a table, i want a script that would change
the names of a table.
Anyways, I'm pasting part of the script here...
----
--
ALTER Procedure
[dbo].[Report_PopulateTable_ITAssetComponent]
/* Input parameter to specify the user language ID */
@.inLanguageID int
AS
/* Getting Fixed ITAsset Components*/
SELECT ta.itassetobjectid as ItAssetObjectID,
ta.OrgUnitID as OrgUnitID,
OU.ObjectName as theOrgUnitName,
DO.ObjectName as theAssetName,
DA.SerialNumber as theSerialNumber,
DA.Manufacturer as theManufacturer,
DA.ModelNumber as theModelNumber,
DITT.ITAssetTypeName as theITAssetTypeName,
DIA.OSServicePack as theOSServicePack
into #FixedITAsset
FROM datObjects OU
join datObjects DO (nolock) on OU.ObjectID = DO.parentObjectID
join datAssets DA (nolock) on DO.ObjectID = DA.AssetObjectID
join datITAssets DIA (nolock) on DA.AssetObjectID= DIA.ITAssetObjectID
join ##tempassets ta (nolock) on DIA.ITAssetObjectID =
ta.itassetobjectid
join defITAssetTypes DITT (nolock) on DITT.ITAssetTypeID
=DIA.ITAssettypeID
WHERE
DO.parentObjectID =
(SELECT ParentObjectid FROM datObjects WHERE ObjectID =
DIA.ITAssetObjectID)
/*Get OrgUnit Name as per the Language and insert in the work table*/
SELECT @.theComponentName = string
FROM datStrings WHERE StringID=1226 AND LanguageID=@.inLanguageID
INSERT INTO DatStdItAssetComponents
(OrgUnitID,itassetobjectid,AssetName,Col
umnName,Value,LanguageID)
select
OrgUnitID,ITAssetObjectID,theAssetName,'
4271',theOrgUnitName,@.inLanguageID
from #FixedITAsset
----
--
That's an example of what's happening. This sp is about 7 pages long and
there are a few like these. Do you see where it says
'DatStdItAssetComponents'? I've to replace that with DatStdItAssetComponents
1
after 4 hours from now and back to 'DatStdItAssetComponents' 8 hours from
now. Keep alternating between the two table names. I've pasted only one
insert statement here but there are a lot more.
My point is to rplace the table name with another table name every 4 hours
by a SCRIPT. Thank you.|||"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:A07841F1-E18F-47E9-9E01-68FF441747AB@.microsoft.com...
> Allright, I'll paste part of the sp here. I really dont know what you guys
> wnat me to paste here since my concern is with 5 sp's. instead of manually
> going and replacing the names of a table, i want a script that would
> change
> the names of a table.
> Anyways, I'm pasting part of the script here...
> ----
--
> ALTER Procedure
> [dbo].[Report_PopulateTable_ITAssetComponent]
> /* Input parameter to specify the user language ID */
> @.inLanguageID int
> AS
> /* Getting Fixed ITAsset Components*/
> SELECT ta.itassetobjectid as ItAssetObjectID,
> ta.OrgUnitID as OrgUnitID,
> OU.ObjectName as theOrgUnitName,
> DO.ObjectName as theAssetName,
> DA.SerialNumber as theSerialNumber,
> DA.Manufacturer as theManufacturer,
> DA.ModelNumber as theModelNumber,
> DITT.ITAssetTypeName as theITAssetTypeName,
> DIA.OSServicePack as theOSServicePack
> into #FixedITAsset
> FROM datObjects OU
> join datObjects DO (nolock) on OU.ObjectID = DO.parentObjectID
> join datAssets DA (nolock) on DO.ObjectID = DA.AssetObjectID
> join datITAssets DIA (nolock) on DA.AssetObjectID= DIA.ITAssetObjectID
> join ##tempassets ta (nolock) on DIA.ITAssetObjectID =
> ta.itassetobjectid
> join defITAssetTypes DITT (nolock) on DITT.ITAssetTypeID
> =DIA.ITAssettypeID
> WHERE
> DO.parentObjectID =
> (SELECT ParentObjectid FROM datObjects WHERE ObjectID =
> DIA.ITAssetObjectID)
> /*Get OrgUnit Name as per the Language and insert in the work table*/
> SELECT @.theComponentName = string
> FROM datStrings WHERE StringID=1226 AND LanguageID=@.inLanguageID
> INSERT INTO DatStdItAssetComponents
> (OrgUnitID,itassetobjectid,AssetName,Col
umnName,Value,LanguageID)
> select
> OrgUnitID,ITAssetObjectID,theAssetName,'
4271',theOrgUnitName,@.inLanguageID
> from #FixedITAsset
> ----
--
> That's an example of what's happening. This sp is about 7 pages long and
> there are a few like these. Do you see where it says
> 'DatStdItAssetComponents'? I've to replace that with
> DatStdItAssetComponents1
> after 4 hours from now and back to 'DatStdItAssetComponents' 8 hours from
> now. Keep alternating between the two table names. I've pasted only one
> insert statement here but there are a lot more.
> My point is to rplace the table name with another table name every 4 hours
> by a SCRIPT. Thank you.
Strange requirements but what do I know...
Why not just create a job that alters the stored procedure?
Look up Alter Procedure in Books Online.
You would need 2 jobs.
Job 1 runs at 4, job 2 runs at 8, job 1 runs at 12...|||Hello Tejas,
I am sure there is a better way ... but you could pass in the table name
as a variable into the stored procedure. You will then need to create all
your inserts etc. dynamically using that variable as your table name.
Hope that helps,
Sandeep

> Allright, I'll paste part of the sp here. I really dont know what you
> guys
> wnat me to paste here since my concern is with 5 sp's. instead of
> manually
> going and replacing the names of a table, i want a script that would
> change
> the names of a table.
> Anyways, I'm pasting part of the script here...
> ---
> --
> ALTER Procedure
> [dbo].[Report_PopulateTable_ITAssetComponent]
> /* Input parameter to specify the user language ID */
> @.inLanguageID int
> AS
> /* Getting Fixed ITAsset Components*/
> SELECT ta.itassetobjectid as ItAssetObjectID,
> ta.OrgUnitID as OrgUnitID,
> OU.ObjectName as theOrgUnitName,
> DO.ObjectName as theAssetName,
> DA.SerialNumber as theSerialNumber,
> DA.Manufacturer as theManufacturer,
> DA.ModelNumber as theModelNumber,
> DITT.ITAssetTypeName as theITAssetTypeName,
> DIA.OSServicePack as theOSServicePack
> into #FixedITAsset
> FROM datObjects OU
> join datObjects DO (nolock) on OU.ObjectID = DO.parentObjectID
> join datAssets DA (nolock) on DO.ObjectID = DA.AssetObjectID
> join datITAssets DIA (nolock) on DA.AssetObjectID=
> DIA.ITAssetObjectID
> join ##tempassets ta (nolock) on DIA.ITAssetObjectID =
> ta.itassetobjectid
> join defITAssetTypes DITT (nolock) on DITT.ITAssetTypeID
> =DIA.ITAssettypeID
> WHERE
> DO.parentObjectID =
> (SELECT ParentObjectid FROM datObjects WHERE ObjectID =
> DIA.ITAssetObjectID)
> /*Get OrgUnit Name as per the Language and insert in the work table*/
> SELECT @.theComponentName = string
> FROM datStrings WHERE StringID=1226 AND LanguageID=@.inLanguageID
> INSERT INTO DatStdItAssetComponents
> (OrgUnitID,itassetobjectid,AssetName,Col
umnName,Value,LanguageID)
> select
> OrgUnitID,ITAssetObjectID,theAssetName,'
4271',theOrgUnitName,@.inLangua
> geID
> from #FixedITAsset
> ---
> --
> That's an example of what's happening. This sp is about 7 pages long
> and there are a few like these. Do you see where it says
> 'DatStdItAssetComponents'? I've to replace that with
> DatStdItAssetComponents1 after 4 hours from now and back to
> 'DatStdItAssetComponents' 8 hours from now. Keep alternating between
> the two table names. I've pasted only one insert statement here but
> there are a lot more.
> My point is to rplace the table name with another table name every 4
> hours by a SCRIPT. Thank you.
>|||On Thu, 26 Jan 2006 15:17:02 -0800, Tejas Parikh wrote:

>Allright, I'll paste part of the sp here. I really dont know what you guys
>wnat me to paste here since my concern is with 5 sp's. instead of manually
>going and replacing the names of a table, i want a script that would change
>the names of a table.
Hi Tejas,
Maybe it's just me, but I honestly don't understand why you can't make
two copies of the stored procedure, then set up a job to run the first
at for instance 4AM, noon, 8PM and the second at midnight, 8AM, 4PM.
Self-modifying code is maintenance hell.
Hugo Kornelis, SQL Server MVP

Need suggestions on text file parsing into database

I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions?

Thanks!

Hi keggdirdle,

As far as I can see, we cannot make the database recognize tab delimited text files automatically.

Whatever technology you use (stored procedure, ssis, or bulk upload), they all depends on fixed format for data.

In this case, I don't think it is possible without your own code.