Can I generate Table Script in a Stored Procedure?

I have to load around 40 tables and I want to write one Stored Procedure that I pass a table name into. Data is coming from 3 different sources and to fill each of the 40 tables. I want to create a temp table that I can load first to make sure it succeeds first before I truncate and insert into the production table.

Is there a system procedure or something that will allow me to create a temp table from a table that alresdy exist in my database?

You can use SELECT INTO to create a new table based on a query like:

SELECT t.col1, t.col2, t.col3

INTO #t

FROM your_table AS t

Above query will create table with same structure as your_table without the defaults, constraints, indexes etc. Identity property on columns will be transferred though. You may also want to look at SSIS to automate your bulk loading process. You can also take a look at the link below for more pointers on how to improve the bulk load process:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Note that with above query, you can create permanent table too. If you create a temporary table then you can only use BULK INSERT statement since the temporary table is connection scoped. And I am not sure if SSIS can perform SELECT...INTO and bulk load from the client side within context of single connection (You will have to ask in the SSIS forum).

|||

Good to know you can create Temps like this.

I might be able to work with this let me know what you think.

Problem:

I’m getting my data from an AS400 using

Insert Into @.TableName

Select *

from Opendatasource(“Connection Info”).AS400ServerName.AS400Library.@.TableName

The table name and column structure in the AS400 are the same as in my database but the column type is different i.e. A lot of the AS400 columns are Char when I need them to be Decimal. I need to create the temp table with the same structure as my table because the majority of the time my process fails is because the AS400 has Char data when it should be Decimal

My Solution:

Select Top 1 *

Into #t

From @.TableName

Truncate Table #t

--Insert temp data from sites

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.California.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Nevada.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Arizona.@.TableName

Truncate Table @.TableName

--Insert data to Production

Insert Into @.TableName

Select *

From #t

can define UDF in DLL

Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
Tarvirdi
answered in microsoft.public.sqlserver.programming
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:eiDuCG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>

can define UDF in DLL

Dear Friends
can I write some functions in DLL and use it in msmsql as udf and in sql
statements? how?
any advice?
Thanks
TarvirdiHi
SQL Server 2005 allows you to create .NET CLR objects that can be used
inside SQL Server.
http://www.microsoft.com/sql/prodin...ation-demo.mspx
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>|||The best way to implement this depends on what version of SQL Server you are
using.
Extended Stored Procedure Architecture
http://msdn.microsoft.com/library/d...r />
_67vp.asp
Adding an Extended Stored Procedure to SQL Server
http://msdn2.microsoft.com/en-us/library/ms164653.aspx
For reasons of performance, security and maintainability, it is best to
avoid the use of external library function calls unless there is a very
special need. As an example, if you are basically wanting access to
functions for string parsing or financial calculations, then this could be
implemented using advanced SQL techniques or perhaps implemented at the
application level.
"M_Tarvirdi" <email@.tarvirdi.com> wrote in message
news:e0DgGG9AGHA.2664@.TK2MSFTNGP15.phx.gbl...
> Dear Friends
> can I write some functions in DLL and use it in msmsql as udf and in sql
> statements? how?
> any advice?
> Thanks
> Tarvirdi
>

Can CASE match more than once ?

Hi
I'm tring to write a statement to analyse what orders were open on the first
day of each month from a system and return one data set with the months
listed and all the orders open during that month. eg.
Mon Order_No
Jan 001
Jan 002
Jan 003
Feb 002
Feb 003
Feb 004
The orders all have an open and closed date, so I want to check for each
month if the first of the month falls between the open and closed date of
each order.
I've set up a dummy database for testing - what I'd like to know is whether
CASE statements be made to match more than once :
SELECT MyNewField =
CASE
WHEN data1 = 1 THEN 'Is One'
WHEN data1 > 1 then 'Not One'
end,
data2
FROM APW_Test
my table is as follows
data1
1
2
3
4
So I would hope to see one result for the number 1 (Is One) and two results
for the remaining numbers because they match both case statements. However,
CASE seems to match the first statement and then stop for each record.
Is there a way I can achieve the result I want fairly simply ?
Thanks in advance.
AndrewHi ... I made a mistake in my logic. What I meant was

> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 0 then 'Greater Than Zero'
so I expect 1 to appear twice. All else the same.
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||Andrew
you can simply use procedure/function to use if condition.
However post DDL,Sample data to help you better
Regards
R.D
"Andrew Webb" wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
>
> "Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
> news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>|||On Fri, 16 Sep 2005 08:39:06 +0100, "Andrew Webb"
<andrew.webb@.eme-med.co.uk> wrote:

> Hi ... I made a mistake in my logic. What I meant was
>
> so I expect 1 to appear twice. All else the same.
CASE returns only the first match.|||Andrew,
It sounds to me like you need a JOIN operation, not a CASE expression.
Post the CREATE TABLE and INSERT statements for some specific
data if you want a more careful answer, but this might be close:
select data1, DisplayAnswer
from T join (
select 'equal 1' as TestCondition, 'Is One' as DisplayAnswer
union all
select 'above 1', 'Not One'
) C
on (
TestCondition = 'equal 1' and data1 = 1
) or (
TestCondition = 'above 1' and data1 > 1
)
If you select only from your 4-row table, with no other table
joined in, you cannot obtain a result that contains any row
more than once.
Steve Kass
Drew University
"Andrew Webb" <andrew.webb@.eme-med.co.uk> wrote in message
news:uMs5zBpuFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm tring to write a statement to analyse what orders were open on the
> first day of each month from a system and return one data set with the
> months listed and all the orders open during that month. eg.
> Mon Order_No
> Jan 001
> Jan 002
> Jan 003
> Feb 002
> Feb 003
> Feb 004
> The orders all have an open and closed date, so I want to check for each
> month if the first of the month falls between the open and closed date of
> each order.
> I've set up a dummy database for testing - what I'd like to know is
> whether CASE statements be made to match more than once :
> SELECT MyNewField =
> CASE
> WHEN data1 = 1 THEN 'Is One'
> WHEN data1 > 1 then 'Not One'
> end,
> data2
> FROM APW_Test
> my table is as follows
> data1
> 1
> 2
> 3
> 4
> So I would hope to see one result for the number 1 (Is One) and two
> results for the remaining numbers because they match both case statements.
> However, CASE seems to match the first statement and then stop for each
> record.
> Is there a way I can achieve the result I want fairly simply ?
> Thanks in advance.
> Andrew
>|||On Fri, 16 Sep 2005 08:30:00 +0100, Andrew Webb wrote:

>Hi
>I'm tring to write a statement to analyse what orders were open on the firs
t
>day of each month from a system and return one data set with the months
>listed and all the orders open during that month. eg.
>Mon Order_No
>Jan 001
>Jan 002
>Jan 003
>Feb 002
>Feb 003
>Feb 004
>The orders all have an open and closed date, so I want to check for each
>month if the first of the month falls between the open and closed date of
>each order.
Hi Andrew,
You could use a calendar table or a table of integers to get this. I'll
give an example with a table of integers that's made up "on the fly".
You can expand it as needed, or make a real table of integers (as
explained on http://www.aspfaq.com/show.asp?id=2516).
DECLARE @.StartDate smalldatetime
,@.EndDate smalldatetime
SET @.StartDate = '20050101' -- Should be first of the month
SET @.EndDate = '20051201'
SELECT DATEADD(month, Numbers.n, @.StartDate) AS Mon,
Orders.Order_No
FROM Orders
INNER JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 9 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL
SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL
SELECT 14 UNION ALL SELECT 15) AS Numbers(n)
WHERE Orders.OpenDate < DATEADD(month, Numbers.n, @.StartDate)
AND Orders.CloseDate > DATEADD(month, Numbers.n, @.StartDate)
AND DATEADD(month, Numbers.n, @.StartDate) <= @.EndDate
ORDER BY Numbers.n, Orders.Order_No
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

can anyone help with this update statement?

I am wondering how to write a sql update statement that would:

Update table 1
set col 2 = 'YES'
where (select col 1 from table 1 where col 2 = 'YES')

Table 1 BEFORE:
Col 1 Col 2
A
A
A
B
B YES
B
C
C
C
D
D YES
D

Table 1 After:
Col 1 Col 2
A
A
A
B YES
B YES
B YES
C
C
C
D YES
D YES
D YES

Can anyone give me some ideas how to write this statement or point me in the right direction?

Thanks,

Blair

Try something along the lines of:

Code Snippet

Update [table 1]
set [col 2] = 'YES'
from [table 1] a -- "a" is an alias
where exists
( select [col 1]
from [table 1] b -- "b" is an alias
where b.[col 2] = 'YES'
and a.[col 1] = b.[col 1]
)

|||

Here is one way.

Code Snippet

update table1

set col2=t.col2

from table1 join (select * from table1 where col2='YES') [t] on table1.col1=t2.col1

|||

While the queries given will fix this data, and you could correct the problem somewhat by embedding those queries in a trigger, this is a classic sign of a poorly normalized table. If one value in the row, in this case Col 2, is determined by the value in another column Col1, then you have the high potential for data problems. (Which clearly you do, since you are writing this query.)

You should definitely consider having a table that represents whatever Table1 represents where Col1 is the key, and the Col2 values is its column. Then, one modification takes care of everything, and you don't end up with inconsistent data.

|||

The simplest syntax:

Code Snippet

Update Table1

Set Col2 = 'Yes'

where Col1 in (Select Col1 from Table1 where Col2 = 'Yes')

|||

This worked perfectly.

Thanks,

Blair

can anyone help me regarding tool

hi
is there any tool to write stored procedure
i have long queries to write instead of that i want a tool or editor where i can click or drag and drop to write procedure
i make my work to complete faster
if any please tell me the site to download
thanx
bye
ramesh

Hi Ramesh,
Maybe is this something your looking for:
http://www.softcities.com/Lattice.SPGen-Stored-Procedure-Generator/download/16114.htm
or
http://www.freevbcode.com/ShowCode.asp?ID=3412
I never used these programs but i just did a search on google for you.
mgg

Can anyone help me in this?

I want to write a function or procedure that deletes any characters
other than aplhabets.
At the same time i want to update a cloumn in the same table(say column
changecode with value 1 if my function deleted any of the characters)
I did however wrote a function to delete BUT.... I am not able to
update the table column 'changecode' .
Can anyone help me in this'Are you getting an error message ?
Could you post what you've done.
--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"sridhar" <sridharkola17@.gmail.com> wrote in message
news:1146812711.153743.134530@.i39g2000cwa.googlegroups.com...
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>|||Hello,
You would wand to create the function first, then use it in a sql statement
to update the table. For example, the following function would convert the
string:
--
CREATE FUNCTION FnAlphaString
(@.string varchar(100))
RETURNS VARCHAR(100)
as
BEGIN
DECLARE @.position int, @.newString varchar(100)
-- Initialize the current position and the string variables.
SET @.position = 1
SET @.NewString = ''
WHILE @.position <= DATALENGTH(@.string)
BEGIN
IF ( ASCII(SUBSTRING(@.string, @.position, 1)) < 65 OR
ASCII(SUBSTRING(@.string, @.position, 1)) > 122)
OR ( ASCII(SUBSTRING(@.string, @.position, 1)) > 90 AND
ASCII(SUBSTRING(@.string, @.position, 1)) < 97)
BEGIN
SET @.newString = @.newString
END
ELSE
BEGIN
SET @.newString = @.newString + SUBSTRING(@.string, @.position, 1)
END
SET @.position = @.position + 1
END
RETURN @.newString
END
---
Then update the table you needed:
Example:
Create TABLE TestString (strRname VARCHAR(100),
Changed bit)
INSERT INTO TestString VALUES('This is ^54 now&*~` a 8[] string',0)
INSERT INTO TestString VALUES('Thisisalreadyacleanstring',0)
update TestString set strRname = dbo.FnAlphaString(strRname), changed = 1
WHERE len(strRname) > len( dbo.FnAlphaString(strRname))
Thanks Kllyj64
"sridhar" wrote:
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>

Can anyone help me in this?

I want to write a function or procedure that deletes any characters
other than aplhabets.
At the same time i want to update a cloumn in the same table(say column
changecode with value 1 if my function deleted any of the characters)
I did however wrote a function to delete BUT.... I am not able to
update the table column 'changecode' .
Can anyone help me in this'Are you getting an error message ?
Could you post what you've done.
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"sridhar" <sridharkola17@.gmail.com> wrote in message
news:1146812711.153743.134530@.i39g2000cwa.googlegroups.com...
> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>|||Hello,
You would wand to create the function first, then use it in a sql statement
to update the table. For example, the following function would convert the
string:
--
CREATE FUNCTION FnAlphaString
(@.string varchar(100))
RETURNS VARCHAR(100)
as
BEGIN
DECLARE @.position int, @.newString varchar(100)
-- Initialize the current position and the string variables.
SET @.position = 1
SET @.NewString = ''
WHILE @.position <= DATALENGTH(@.string)
BEGIN
IF ( ASCII(SUBSTRING(@.string, @.position, 1)) < 65 OR
ASCII(SUBSTRING(@.string, @.position, 1)) > 122)
OR ( ASCII(SUBSTRING(@.string, @.position, 1)) > 90 AND
ASCII(SUBSTRING(@.string, @.position, 1)) < 97)
BEGIN
SET @.newString = @.newString
END
ELSE
BEGIN
SET @.newString = @.newString + SUBSTRING(@.string, @.position, 1)
END
SET @.position = @.position + 1
END
RETURN @.newString
END
---
Then update the table you needed:
Example:
Create TABLE TestString (strRname VARCHAR(100),
Changed bit)
INSERT INTO TestString VALUES('This is ^54 now&*~` a 8[] string',0)
INSERT INTO TestString VALUES('Thisisalreadyacleanstring',0)
update TestString set strRname = dbo.FnAlphaString(strRname), changed = 1
WHERE len(strRname) > len( dbo.FnAlphaString(strRname))
Thanks Kllyj64
"sridhar" wrote:

> I want to write a function or procedure that deletes any characters
> other than aplhabets.
> At the same time i want to update a cloumn in the same table(say column
> changecode with value 1 if my function deleted any of the characters)
> I did however wrote a function to delete BUT.... I am not able to
> update the table column 'changecode' .
> Can anyone help me in this'
>

Can an SQL Server stored procedure write output to an excel formated file?

Hi,
To clarify the previous post.
I would like to write a stored procedure that a client can call. The
stored procedure will then output its result in an excel file.
Thanks,
chariaYou have things a little reversed. You should have the stored procedure
return the data. How you call the report will determine how it is rendered
(HTML, CSV, PDF, Excel etc). When integrating reports you can use URL
integration or webservices. Neither of which would be that easy from a
stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<cpeters5@.gmail.com> wrote in message
news:1120582350.023702.281780@.g43g2000cwa.googlegroups.com...
> Hi,
> To clarify the previous post.
> I would like to write a stored procedure that a client can call. The
> stored procedure will then output its result in an excel file.
> Thanks,
> charia
>

can a view of a table imrpove table input performance?

I have a table (sql Server2k) which users connect to through ODBC - read
only. I have to input/write a lot of data to this table everyday. I
observed that it takes a lot longer to populate this table than one which is
not public. I am guessing that people have this table open through ODBC. I
f
I had the users connect to a view of this table (Select * From tbl1) would
this resolve the performance issue?
I realize that people would have to refresh the view each day to get the
most recent data, but I can send out a memo advising them of this.
Any suggestions appreciated. Thanks,
RichUnless it is an indexed view, a view doesn't actually store data. So when
you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT
... view definition) which, long story short, means you're still accessing
the base tables...
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@.microsoft.com...
>I have a table (sql Server2k) which users connect to through ODBC - read
> only. I have to input/write a lot of data to this table everyday. I
> observed that it takes a lot longer to populate this table than one which
> is
> not public. I am guessing that people have this table open through ODBC.
> If
> I had the users connect to a view of this table (Select * From tbl1) would
> this resolve the performance issue?
> I realize that people would have to refresh the view each day to get the
> most recent data, but I can send out a memo advising them of this.
> Any suggestions appreciated. Thanks,
> Rich|||I guess I was thinking about a snapshot view. Is there such a thing?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unless it is an indexed view, a view doesn't actually store data. So when
> you say SELECT * FROM view, you're actually running SELECT * FROM (SELECT
> ... view definition) which, long story short, means you're still accessin
g
> the base tables...
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D734E5C5-3EB6-4767-BDD0-EA4C12640EAA@.microsoft.com...
>
>|||So to create an indexed view is it:
Create Index view1Ind
On view1(columnID)
While I am at it, is it possible to have on the most elemental index on a
table and have the rest of the indexes on the view? where the view would
contain all of the data in the table?
"Tibor Karaszi" wrote:

> That would be creating an index on the view, an indexed view.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:8500A93B-2C02-452B-A8F2-E45BA68B6F82@.microsoft.com...
>|||On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote:

>So to create an indexed view is it:
>Create Index view1Ind
>On view1(columnID)
Hi Rich,
To create an indexed view, you must use a unique clustered index:
CREATE UNIQUE CLUSTERED INDEX view1Ind
ON view1(columnID)
(assuming that columnID is unique in the view, of course).
Once you have this index in place, you can define additional views on
the index, but these can't be clustered.

>While I am at it, is it possible to have on the most elemental index on a
>table and have the rest of the indexes on the view? where the view would
>contain all of the data in the table?
I guess you can, but what's the point? If the view is an exact copy of
the table, you'll gain some performance by dropping the view and
indexing the table instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Basically, I want to have my cake and eat it. I have about 8-9 indexes on
this one table. But data input (only I input the data programatically -
batch operation) is becoming slow on this one table because people are
opening the table through ODBC connection. I wasnt users to be able to quer
y
the table with as little lag as possible, but I also what to be able to writ
e
data to the table without the lag.
By the way, when I create a new index using the GUI tool in table desing, it
has a selection option of "Do not automatically recompute statistics". I
have been checking this option on for each index I creat. What would yield
more performance? to have this option checked on or off?
Thanks,
Rich
"Hugo Kornelis" wrote:

> On Fri, 26 Aug 2005 11:59:06 -0700, Rich wrote:
>
> Hi Rich,
> To create an indexed view, you must use a unique clustered index:
> CREATE UNIQUE CLUSTERED INDEX view1Ind
> ON view1(columnID)
> (assuming that columnID is unique in the view, of course).
> Once you have this index in place, you can define additional views on
> the index, but these can't be clustered.
>
> I guess you can, but what's the point? If the view is an exact copy of
> the table, you'll gain some performance by dropping the view and
> indexing the table instead.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||> Basically, I want to have my cake and eat it.
That tend to be difficult in real life. Indexed views is not a way to make m
odifications have less
lag. The opposite. Then you modify the tables, the index on the view also ne
ed to be maintained,
which can be more costly compared to a similar index on the base table (depe
nding on the
circumstances).
How real time does the reporting tables need to be. Why not create snapshots
which you update
regularly?
In general, you do *not* want to turn off auto-update statistics. Statistics
are not updated (in
general) when you modify. They are updated when you SELECT, if they are out-
of-date. This is so that
the optimizer has good information to go on so it can pick a good plan. See
http://msdn.microsoft.com/library/e...l/statquery.asp for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:54F21B87-C722-45AD-A4E2-BB7DDD3A140F@.microsoft.com...
> Basically, I want to have my cake and eat it. I have about 8-9 indexes on
> this one table. But data input (only I input the data programatically -
> batch operation) is becoming slow on this one table because people are
> opening the table through ODBC connection. I wasnt users to be able to qu
ery
> the table with as little lag as possible, but I also what to be able to wr
ite
> data to the table without the lag.
> By the way, when I create a new index using the GUI tool in table desing,
it
> has a selection option of "Do not automatically recompute statistics".
I
> have been checking this option on for each index I creat. What would yiel
d
> more performance? to have this option checked on or off?
> Thanks,
> Rich
> "Hugo Kornelis" wrote:
>|||you also can:
select * into new_table from old_table;
load data into new_table
drop table old_table;
sp_rename new_table|||This is an interesting idea. Thanks. I could invoke DTS in code (large
table) Actually, well, thinking about it more, I am inserting a few thousan
d
records a day, millions of records. Even with DTS that might be too slow.
But it is an idea. When the table is large enough I may start using OLAP on
it.
"AK" wrote:

> you also can:
> select * into new_table from old_table;
> load data into new_table
> drop table old_table;
> sp_rename new_table
>