Create a Temp Table

If I am going to create a temp table and insert data into it, would I
be better of creating the indexes after I do the insert?
Does it matter?If the amount of rows to be inserted is huge, sure. Better to test it.
AMB
"Bob" wrote:

> If I am going to create a temp table and insert data into it, would I
> be better of creating the indexes after I do the insert?
> Does it matter?
>

Create a log from insert and delete tables

How can I create a log with informations about create and delete tables from
my database?
I use a SQl Server 2000 and Visual Interdev where I can acess all my databas
e
using Data Connection creating, modifying and deleting tables.
I wanna create a log with the information about create and delete tables to
know what is happen in my database.> How can I create a log with informations about create and delete tables
> from
> my database?
> I use a SQl Server 2000 and Visual Interdev where I can acess all my
> database
> using Data Connection creating, modifying and deleting tables.
> I wanna create a log with the information about create and delete tables
> to
> know what is happen in my database.
The SQL Profiler, a tool shipped with SQL Server, is your friend here. Do
please check it in Books OnLine - I think this is what you need.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

create a Insert trigger

I need to create a trigger on tblBag_data to add new rows to tblBag_databak, but I'm getting a syntax error.

CREATE TRIGGER trgtblBag_databak ON [tblBag_data]
FOR INSERT
AS

If @.@.trancount > 0
insert tblBag_databak
--return
end

Thanks for your help.Why are you checking @.@.trancount?

What kind of insert is that?

You have an END with no BEGIN

How about...

CREATE TRIGGER trgtblBag_Data ON tblBag_data
FOR INSERT
AS

INSERT INTO tblBag_databak (supply column list here)
SELECT supply column list here
FROM inserted
GO

But why are you keeping duplicate data in 2 tables?

I track updates and deletes, and leave the "base" rows in the INSERT table...|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
Why are you checking @.@.trancount?

What kind of insert is that?

You have an END with no BEGIN

How about...

CREATE TRIGGER trgtblBag_Data ON tblBag_data
FOR INSERT
AS

INSERT INTO tblBag_databak (supply column list here)
SELECT supply column list here
FROM inserted
GO

Hi,

Thank you so much for your quick reply. It is working.
I need one table to show all the records and another used to be update.

Thanks again.|||Well it's funny...

I try to keep historical data, so I can track activity against a table...

I have on Current table, and 1 historical table...all the updates and deletes are dtored in the historical, and all the current (Inserts, and new updates) are stored in current...

Notice the addition of "administrative" columns to track when the event occured. And don't be to put off by the trigger...I'm using connection pooling and do stuff a little different with User Id's

Anyway, good luck

CREATE TABLE [dbo].[Company] (
[Company_Name] [char] (30) NOT NULL ,
[Active_Ind] [bit] NULL ,
[Psft_Company_Id] [char] (3) NULL ,
[FEIN] [char] (10) NULL ,
[Has_NonQual_Taxes] [bit] NULL ,
[Has_Qual_Taxes] [bit] NULL ,
[Created_By] [char] (8) NOT NULL ,
[Created_Ts] [datetime] NOT NULL ,
[Updated_By] [char] (8) NULL ,
[Updated_Ts] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company_H] (
[HIST_ADD_TS] [datetime] NOT NULL ,
[HIST_ADD_TYPE] [char] (1) NOT NULL ,
[HIST_ADD_BY] [char] (8) NOT NULL ,
[HIST_ADD_SYSUSER_BY] [char] (8) NOT NULL ,
[Company_Name] [char] (30) NOT NULL ,
[Active_Ind] [bit] NULL ,
[Psft_Company_Id] [char] (3) NULL ,
[FEIN] [char] (10) NULL ,
[Has_NonQual_Taxes] [bit] NULL ,
[Has_Qual_Taxes] [bit] NULL ,
[Created_By] [char] (8) NOT NULL ,
[Created_Ts] [datetime] NOT NULL ,
[Updated_By] [char] (8) NULL ,
[Updated_Ts] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Company_Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Company] WITH NOCHECK ADD
CONSTRAINT [DF__Company__Created__619B8048] DEFAULT (getdate()) FOR [Created_Ts]
GO

ALTER TABLE [dbo].[Company_H] WITH NOCHECK ADD
CONSTRAINT [DF__Company_H__HIST___59C55456] DEFAULT (getdate()) FOR [HIST_ADD_TS]
GO

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

create a function

hi, I try to do insert a stored procedure's result into a temp table,
then I try to put it inside the function, but it doesn't work.
The reason I want to do this is because we have 200 stored procedures which
need to reuse the same code,
so by putting these logics into a function, the code would be cleaner..
How can I get around this?
CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
@.LId INT)
RETURNS TABLE
AS
CREATE TABLE #tempList
(
UId INT,
CompanyId NVARCHAR(10),
LId INT,
CompanyName NVARCHAR(50)
.....
)
INSERT INTO #tempList
Exec GetListInfo @.UId, @.CompanyId ,@.LId
RETURN (SELECT * FROM #tempList)You can't execute a stored proc within a function -- you'll have to do this
within another stored proc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uxpTfkAIFHA.2936@.TK2MSFTNGP15.phx.gbl...
> hi, I try to do insert a stored procedure's result into a temp table,
> then I try to put it inside the function, but it doesn't work.
> The reason I want to do this is because we have 200 stored procedures
which
> need to reuse the same code,
> so by putting these logics into a function, the code would be cleaner..
> How can I get around this?
>
> CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
> @.LId INT)
> RETURNS TABLE
> AS
> CREATE TABLE #tempList
> (
> UId INT,
> CompanyId NVARCHAR(10),
> LId INT,
> CompanyName NVARCHAR(50)
> .....
> )
> INSERT INTO #tempList
> Exec GetListInfo @.UId, @.CompanyId ,@.LId
>
> RETURN (SELECT * FROM #tempList)
>|||You can't call an stored procedure from within a function. Put the SP
logic into the body of the function instead if you can.
David Portas
SQL Server MVP
--|||you can't put temp table in the function also.
correct me if I'm wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23bBHPtAIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> you can't put temp table in the function also.
> correct me if I'm wrong.
You can use a table variable in a function -- logically the same thing.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||That's correct. However you can use table variables in a function.
Table variables provide a lot of the same functionality as temp tables
but you can't insert the result of an SP into a table variable even if
it was possible to execute the SP.
David Portas
SQL Server MVP
--|||Hi David,
Well, I know that will work, but if I make a change in the logics in
the future,
then I will have to change all 200 procedures. I try to avoid this problem.
If function works, then all I need to do is to change the logic in the
function. Too bad there is no way to get around it.
=(
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||Oh never mind.. ignore the previous question,
I misunderstood it. you're right, put the sp logic into function will work.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uOGLGtBIFHA.572@.tk2msftngp13.phx.gbl...
> Hi David,
> Well, I know that will work, but if I make a change in the logics
in
> the future,
> then I will have to change all 200 procedures. I try to avoid this
problem.
> If function works, then all I need to do is to change the logic in the
> function. Too bad there is no way to get around it.
> =(
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
>

Coying data from one table to another in the same SQL 2005 Server

Having a little bit of problem copying data from a new table to an old table.
First I tried:
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
And got this error:
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON.
Then I tried:
SET IDENTITY_INSERT [IMArchive].[dbo].[messages] ON
INSERT INTO [IMArchive].[dbo].[messages] SELECT * FROM
[LcsLog].[dbo].[messages]
An I got this error:
Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'IMArchive.dbo.messages'
can only be specified when a column list is used and IDENTITY_INSERT is ON
All I'm trying to do to is to copy data from 'messages' table. Any ideas
what I'm doing wrong? Thanks in advance...The problem is SELECT *. Just use INSERT INTO and list all columns excluding
the IDENTITY column, like this:
INSERT INTO [IMArchive].[dbo].[messages]
(<column_list>)
SELECT <column_list>
FROM [LcsLog].[dbo].[messages]
That way the IDENTITY column will get populated automatically.
If you want to copy the IDENTITY column, then add it to the <column_list>
and use SET IDENTITY_INSERT ON.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thank you for your help. I tried below and got "Msg 102, Level 15, State 1,
Line 13
Incorrect syntax near ','." error. What am I missing?
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT ([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
FROM [LcsLog].[dbo].[messages]
"Plamen Ratchev" wrote:
> The problem is SELECT *. Just use INSERT INTO and list all columns excluding
> the IDENTITY column, like this:
> INSERT INTO [IMArchive].[dbo].[messages]
> (<column_list>)
> SELECT <column_list>
> FROM [LcsLog].[dbo].[messages]
> That way the IDENTITY column will get populated automatically.
> If you want to copy the IDENTITY column, then add it to the <column_list>
> and use SET IDENTITY_INSERT ON.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
>|||artunc,
lose the parentheses around your column list in your select statement
-st|||Try this:
INSERT INTO [IMArchive].[dbo].[messages]
([date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2])
SELECT
[date]
,[fromid]
,[toid]
,[cs_call_id]
,[contenttypeid]
,[computerid]
,[body]
,[reserved1]
,[reserved2]
FROM [LcsLog].[dbo].[messages]
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting a different error...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>|||Is this from the same insert? The error indicates violation of FOREIGN KEY
constraint. You can add a WHERE filter to select only rows that do not
violate the constraint. Another option is to disable the FOREIGN KEY (see
ALTER TABLE ... NOCHECK CONSTRAINT ...), but that can lead to breaking the
data integrity.
Posting the create table statements with all constraints will help to get
better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||That worked, thank you. Now I'm getting this errror:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_messages_1". The conflict occurred in database "LcsLog", table
"dbo.users", column 'userid'.
The statement has been terminated.
"whobut" wrote:
> artunc,
> lose the parentheses around your column list in your select statement
> -st
>
>

counting the inserts and updates on a table in a sql server database

Hello,

Can someone point me to getting the total number of inserts and updates on a table
over a period of time?

I just want to measure the insert and update activity on the tables.

Thanks.

- VishOn a single statement you can capture the @.@.rowcount into a variable and
write it to a log table. But if I'm reading this correctly, you don't want
to do this through the existing code base. Inserts are usually easy if
there is a primary or unique key. Assuming no deletes, simply how many new
keys are there since the last count. Or if the key is incrementing by one
what's the max value - the previous max value. Updates are more vague. How
many rows were updated or how many updates occurred These activities are
usually accommodated for in the initial table design with flag and
last_mod_date columns. Without auditing written into every piece of code or
proper schema design it's an ugly intensive task to rub to data sets
together (using checksums or straight comparisons) to find differences.

Danny

"Viswanatha Thalakola" <vthalakola@.yahoo.com> wrote in message
news:d762e418.0411301845.7504b0b4@.posting.google.c om...
> Hello,
> Can someone point me to getting the total number of inserts and updates on
> a table
> over a period of time?
> I just want to measure the insert and update activity on the tables.
> Thanks.
> - Vish|||On 30 Nov 2004 18:45:55 -0800, Viswanatha Thalakola wrote:

>Hello,
>Can someone point me to getting the total number of inserts and updates on a table
>over a period of time?
>I just want to measure the insert and update activity on the tables.
>Thanks.
>- Vish

Hi Vish,

The easiest way to do this is to add some counting logic to the stored
procedures that do the inserting, updating and deleting. But if you can't
or won't change those tables (or if you allow direct data modifications,
without using stored procedures), you have two other options:

1. Set up a profiler trace. Catch the trace results in a table or in a
file, then use either SQL queries (if in a table) or text manipulation
tools (if in a file) to count the number of inserts, updates, etc. I must
add that I don't know the exact format and I'm not sure either if the
number of rows affected is included in the trace data (it it isn't, you
can't use this approach).

2. Create triggers for each table you need to monitor. Have these triggers
copy @.@.rowcount in a local variable as the first statement (that yields
the number of rows affected by the statement that fired the trigger) and
save that value to a table.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> 1. Set up a profiler trace. Catch the trace results in a table or in a
> file, then use either SQL queries (if in a table) or text manipulation
> tools (if in a file) to count the number of inserts, updates, etc. I must
> add that I don't know the exact format and I'm not sure either if the
> number of rows affected is included in the trace data (it it isn't, you
> can't use this approach).

It isn't, but you can catch number of page writes. Still, though, not a
wholly reliable number.

Then again, I assume that the aim is not to save exact numbers, but get
some approxamite statistics, so some Profiler method is proably better
than adding triggers to the system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp