INSERTED table performance

I have a table with one UPDATE trigger. When I execute a one row update
command
to the table, Graphical Query Plan reports very slow select from INSERTED
table (900ms).
However, if I check the same command using Profiler, everything goes quickly
(duration 0 ms). Why is that? Which one should I trust, profiler or query
plan?I trust Profiler more than the Graphical Query Plan. I have seen some quite
strange costs and percentages in the Graphical Query Plan, specially when
objects are involved that don't exist at the beginning of the query, like
the inserted and deleted tables, temporary tables and table variables
--
Jacco Schalkwijk
SQL Server MVP
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Pexi,
Something that surprise me when I first found out. Inserted and deleted do
not exist, but are virtual tables that are populated each time you query
them by scanning the transaction log to extract the before and after images.
This is why the suggestion is to fill temp tables #inserted and #deleted if
you need to make repeated use of these tables.
Regarding the difference in the timings, the best way to measure is to
create a test. Do a loop calling your UPDATE repeatedly and logging the
milliseconds in a table.
SET @.BeginTime = GetDate()
EXEC YourTestStatement
INSERT INTO TrackingTable Values(@.BeginTime, GetDate())
Afterward you can analyze the results (and publish an article).
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Thanks for the replies! You kind of confirm my thinking:
never trust the query plan - it just tells fairy tales sometimes :)
pexi
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>

inserted in dynamic query

Hi,
Is it possible to use inserted or deleted tables in a dynamic query in a
trigger?
Thanks.No, you can only reference the inserted and deleted tables within the contex
t
of the trigger.
What exactly are you trying to do? Perhaps there is a workaround.
"helpful sql" wrote:

> Hi,
> Is it possible to use inserted or deleted tables in a dynamic query in
a
> trigger?
> Thanks.
>
>

Inserted Identities

Hi i have a Query Like this:

INSERT INTO TABLE1
SELECT * FROM TABLE2

TABLE1 has a identity column,
now i want to know what identities have been inserted into TABLE1 after the Query executes.

Be Sure,
Hosseinhi try this

INSERT
INTO Table1
SELECT *
FROM Table2

-- assuming Col1 and Col2 are your unique column identifiersa
SELECT t1.TheIdentityColumn
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Col1 = t2.Col1
t1.Col2 = t2.Col2|||

You can do with @.@.ROWCOUNT.

Code Snippet

SET NOCOUNT ON;

Insert Into <Your Identity Table>

Select <some columns> from <some table>;

Select * From <Your Identity Table>Where identity_column > Scope_Identity() - @.@.Rowcount

|||

This use of SCOPE_IDENTITY() is not guaranteed to to work. It is possible and happens that rows can be inserted into the table in the middle of the sequence. If you are using SQL Server 2005, you can use the OUTPUT clause with your INSERT statement to fetch the identity columns of the inserted rows.

Rhamille's code will work if you have the alternate keys to the table.

|||I agree with Kent point.|||Here is how you can use the OUTPUT clause:

DECLARE @.table1 TABLE
(
IDCol INT
)

INSERT INTO Table1(fldlist)
OUTPUT INSERTED.IDCol INTO @.table1(IDCol)
SELECT * FROM Table2

SELECT * FROM @.table1 will give you the identity columns that were inserted.

Inserted and Deleted tables

Hi:

Can any of the experts please confirm the fact that Inserted and deleted tables in SQL Server 2005 are stored in tempdb?. If so, how can I query them in tempdb ( A code snippet would be useful).

Thanks

AK

Hi Ankith,

Inserted and deleted table are created in Trigger execution time and can't possible query them, only in trigger execution time.

Regards,

|||

Thanks for the reply. I still would like to know if they are stored in tempdb though in SQL Server 2005 Vs getting stored in memory in SQL Server 2000.

Any pointers?

Thanks

|||inserted/deleted are memory-resident tables. You cannot access them outside of the execution context.|||Thanks OJ. So what I might have read is probably talking of row versioning that uses tempdb. Thanks again for the clarification.|||You shouldn't be allowed to read internal worktable even if it resides in tempdb. If you could, this would be a major security hole. ;-)|||

Hi OJ:

<You shouldn't be allowed to read internal worktable even if it resides in tempdb. If you could, this would be a major security hole. ;-)>

Right I agree with you. However what does the following paragraph mean?

URL is :http://www.sqlmag.com/Article/ArticleID/93465/sql_server_93465.html

The first impression i get when i read the paragraph is the tables are stored in tempdb in 2005. This is where I am confused. Can you please elaborate further?.

Thanks

AK

Triggers have long been a part of SQL Server and were the only feature prior to SQL Server 2005 that provided any type of historical (or versioned) data. Triggers can access two pseudo-tables called deleted and inserted. Inside the trigger, you can access these two tables as if they were real tables, but accessing them while not in a trigger results in an unknown object error. If the trigger is a DELETE trigger, the deleted table contains copies of all the rows deleted by the operation that caused the trigger to fire. If the trigger is an INSERT trigger, the inserted table contains copies of all the rows inserted by the operation that caused the trigger to fire. And if the trigger is an UPDATE trigger, the deleted table contains copies of the old versions of the rows, and the inserted table contains all the new versions. Before SQL Server 2005, SQL Server would determine which rows were included in these pseudo-tables by scanning the transaction log for all the log records belonging to the current transaction. Any log records containing data inserted in or deleted from the table to which the trigger was tied were included in the inserted or deleted tables.

In SQL Server 2005, these pseudo-tables are created by using RLV technology. When data-modification operations are performed on a table that has a relevant trigger defined, SQL Server creates versions of the old and new data in the version store in tempdb.This occurs whether or not either of the snapshot-based isolation levels has been enabled.When a SQL Server 2005 trigger accesses the deleted table, it retrieves the data from the version store.When a trigger needs to determine which rows in the table are new rows and accesses the inserted table, SQL Server again gets the inserted table rows from the version store.

|||The article describes how sqlserver physically create/maintain the inserted/deleted table. For a very long time now, tempdb has always been used as the workspace for sqlserver. It uses tempdb to hold the paged data that can't fit in the allowable memory - @.table variable is the best example of this. So, in the new sql2k5, instead of scanning the log to materialize the inserted/deleted table, it goes ahead and store a copy of updated data in tempdb. This will make the materialization faster because it does not have to scan the entire log.

Long story short, inserted and deleted table are very special table. Regardless of how they're materialized, they can only be accessed within the execution (trigger) context.|||

I am curious why you would want to do this in the first place. Are you simply trying to access the data before and after the record is created. In a trigger you can access the date using inserted and deleted as a table name.

select * from inserted

Also, it is interesting to point out that an update consists of both an insert and a delete.

|||Thanks OJ for your explanation.sql

Inserted & Deleted Tables!

Suppose a trigger gets fired when the following UPDATE query gets
executed:
---
UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
---
Now the Inserted table will have the new record '12345' in the Pwd
column & the Deleted table will have the old record '11111' in the Pwd
column. So will the record 'jack' exist in the UserID column of both
the Inserted table & the Deleted table that the trigger will be making
use of?
Thanks,
ArpanHi
Yes, the whole row, as it was before and after are in the respective tables,
not just the column that changed.
If you update the primary key of a table, then comparing the Inserted and
Deleted becomes very difficult.
Regards
--
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/
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123800474.961292.259990@.g49g2000cwa.googlegroups.com...
> Suppose a trigger gets fired when the following UPDATE query gets
> executed:
> ---
> UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
> ---
> Now the Inserted table will have the new record '12345' in the Pwd
> column & the Deleted table will have the old record '11111' in the Pwd
> column. So will the record 'jack' exist in the UserID column of both
> the Inserted table & the Deleted table that the trigger will be making
> use of?
> Thanks,
> Arpan
>|||On 11 Aug 2005 15:47:55 -0700, Arpan wrote:

>Suppose a trigger gets fired when the following UPDATE query gets
>executed:
>---
>UPDATE Users SET Pwd='12345' WHERE UserID='jack' AND Pwd='11111'
>---
>Now the Inserted table will have the new record '12345' in the Pwd
>column & the Deleted table will have the old record '11111' in the Pwd
>column. So will the record 'jack' exist in the UserID column of both
>the Inserted table & the Deleted table that the trigger will be making
>use of?
Hi Arpan,
Almost.
The exact correct way to put this is:
- The deleted table will hold 0, 1, or many rows that all have UserID
'jack' and Pwd '11111'. Impossible to tell what the other columns will
be.
- The inserted table will hold 0, 1, or many rows (but the same number
as the deleted table) that all have UserID 'jack' and Pwd '12345'; the
other columns will be the same as in the corresponding rows in the
deleted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert/select

I am trying to find an easier way to handle my insert/select statement. If
I have the following tables and query - Is there a way to do this in one
statement?
create table Master(
MasterKey int,
)
Create table SubTable1(
MasterKey int,
PK int,
Data varChar(1000)
)
Create table SubTable2(
MasterKey int,
PK int,
Data varChar(1000
)
Master Table
MasterKey Priority
1 0
2 0
3 1
4 0
5 0
6 0
SubTable1
Empty
SubTable2
MasterKey PK
1 1
1 1
1 2
2 3
2 3
3 4
3 4
3 4
4 4
4 4
4 5
4 5
What I want to be able to do is move data from SubTable2 to SubTable1
I tried to do something like:
Select @.MasterKey from Master where Priority = 1 (this would give me
a MasterKey of 3)
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK = 4
This would move/create 5 records with a @.MasterKey of 3 into the SubTable1.
This works as long as there is only
one MasterKey. But what if I want to create a 5 records for all (or a
potion) of the MasterKeys.
I could reexecute the command multiple times from a loop to get the results
I want, but I was curious if there was an easier way, using one SQL
Statement.
Thanks,
Tomtshad, it is not clear what you are trying to do. In the example you give
you would end up with 5 records in SubTable1 that all had MasterKey = 3 and
PK = 4, that doesn't seem to make much sense.
If you explain it better I can probably help. I think you may want to use an
IN list in the SELECT, so something like:
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK IN (4, 5, 6)
or otherwise you may need to use a subquery or a join, but I just can't tell
what you're trying to do.
Sean
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uIw5J5tjFHA.3756@.TK2MSFTNGP15.phx.gbl...
>I am trying to find an easier way to handle my insert/select statement. If
>I have the following tables and query - Is there a way to do this in one
>statement?
> create table Master(
> MasterKey int,
> )
> Create table SubTable1(
> MasterKey int,
> PK int,
> Data varChar(1000)
> )
>
> Create table SubTable2(
> MasterKey int,
> PK int,
> Data varChar(1000
> )
> Master Table
> MasterKey Priority
> 1 0
> 2 0
> 3 1
> 4 0
> 5 0
> 6 0
> SubTable1
> Empty
> SubTable2
> MasterKey PK
> 1 1
> 1 1
> 1 2
> 2 3
> 2 3
> 3 4
> 3 4
> 3 4
> 4 4
> 4 4
> 4 5
> 4 5
> What I want to be able to do is move data from SubTable2 to SubTable1
> I tried to do something like:
> Select @.MasterKey from Master where Priority = 1 (this would give
> me a MasterKey of 3)
> insert (MasterKey,PK,Data)
> Select @.MasterKey,PK,Data
> From SubTable2
> Where PK = 4
> This would move/create 5 records with a @.MasterKey of 3 into the
> SubTable1. This works as long as there is only
> one MasterKey. But what if I want to create a 5 records for all (or a
> potion) of the MasterKeys.
> I could reexecute the command multiple times from a loop to get the
> results I want, but I was curious if there was an easier way, using one
> SQL Statement.
> Thanks,
> Tom
>
>

Insert/ Update/ Delete slowness.

SQL2K sp4

Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas?
I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).Can you give a sample of the statements this script is running?

insert with exec sql server 2005

Hi,
This is a query that inserts the xmlcontents of a file into the table.

insert into
tbTrades
(
xmlContents
)
Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

Now I would like to add an extra field in the insert. something like:

declare @.FileName varchar(200)

set @.FileName = 'c:\1234.xml'

insert into
tbTrades
(
FileName,
xmlContents
)
@.FileName,
Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

This gives an error:
Incorrect syntax near '@.FileName'.

p.s. I am happy with the first query, just would like to get the second one to work too.
Thanks

how about:

Code Snippet

insert into

tbTrades

(

FileName,

xmlContents

)

Exec ('SELECT ''' + @.FileName + ''', Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.FilePath + ''', SINGLE_CLOB) as D')

Insert unique rows in temp table

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query. I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.

MemberID + Month + Year should ne unique in Temp table

I don't think what you are doing is valid because a local temp table scope is very limited, but if it valid it will be covered in the link below by one of the best minds in T-SQL. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Insert two rows into two tables at the same time from a formview

I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.

If you're passing all of the information into your Stored Procedure, then can't you simply retrieve the last ID inserted via SCOPE_IDENTITY? This assumes your using an identity column within tblPerson.

|||

Thanks for your reply. I'm not familiar with this command because I'm from a MySQL background. So I essentially I use the following

INSERT INTO tblPerson (name, username, password) VALUES (@.name, @.username, @.password);

INSERT INTO tblAddress (FK_tblPerson, address1, address2) VALUES (scope_identity(),@.address1, @.address2);

|||

Yes, except that I'd declare a variable and place the results of SCOPE_IDENTITY into it. Then I'd use that variable for my next INSERT.

INSERT smalldatetime problem

Hallo,

I am trying to insert date in a table in my database, where column type is smalldatetime. Query works fine if date format ismm.dd.yy:

INSERT INTO DateTable (DateValue) VALUES ('8.18.2007 22:00:00') works fine!

But if the time format is dd.mm.yy it does not work:

INSERT INTO DateTable (DateValue) VALUES ('18.8.2007 22:00:00') does not work!

The error message is:The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Is there any chanceto execute(dd.mm.yy)INSERT INTO DateTable (DateValue) VALUES ('18.8.2007 22:00:00') properly?

Thanx!
Marko

just do like this.....wat ever it may be the input type......

INSERT INTO DateTable (DateValue) VALUES datetime.parse('18.8.2007 22:00:00').tostring("yyyy-MM-dd");

Ramesh

|||

Actually, my query looks something like this:

INSERT INTO Table1(ID, Name, Date) VALUES(100, 'Tom', '18.8.2007')

So, I am not sure where to put datetime.parse and tostring(... Tried to type query(datetime.parse and tostring()...) in SQL Server Management Studio, but I got lot of error messages...

|||

I solved my probleme here:http://forums.asp.net/p/1148508/1866912.aspx

Thanx anyway!
Marko

|||

This would be a better approach (Done in VB.NET, but you should be able to convert it to C# easily). Area's marked with ... are missing, and are irrelevant:

dim conn as new sqlconnection(...)

dim cmd as new sqlcommand("INSERT INTO MyTable(col1) VALUES (@.val1)",conn)

cmd.Parameters.Add("@.val1",sqldbtype.datetime).Value=calCalendar1.selecteddate

conn.open

cmd.executenonquery

conn.close

The above shows how we can supply a datetime to T-SQL as a true datetime instead of first converting a datetime to a string (Which may be affected by either the webserver's current culture or the clients current culture), and then trying to parse the resulting string on SQL Server using it's current culture. By avoiding the datetime->string->datetime conversion process, culture becomes irrelevant.

|||

I will keep this in my record, and will use it for testing my application after it is finished!

Thanx!
Marko

Insert Select Help

I need a query that looks at one table and appends another if new
customer data is added. I think I need an Insert, Select statement using
the NOT IN clause.

I need to compare Division, CustomerNumber of the two tables.

Help, Example Appreciated. Thanks
Frank

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Frank Py" <fpy@.proactnet.com> wrote in message
news:3fd88fd6$0$199$75868355@.news.frii.net...
> I need a query that looks at one table and appends another if new
> customer data is added. I think I need an Insert, Select statement using
> the NOT IN clause.
> I need to compare Division, CustomerNumber of the two tables.
> Help, Example Appreciated. Thanks
> Frank
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You should post the DDL for your tables to get a more precise response, but
I guess you need something like this:

insert into dbo.TargetTable
(col1, col2, ...)
select col1, col2, ...
from dbo.SourceTable s
where not exists (
select *
from dbo.TargetTable t
where t.Division = s.Division and
t.CustomerNumber = s.CustomerNumber
)

If that guess isn't helpful, please post the DDL for both tables, including
keys.

Simon|||Yes, that's basically it! I used MS Access to help me with some of the
syntax (dont' tell anyone). This is what I ended up with and it seems to
test good:

INSERT INTO TmemberPasswords ( Division, CustomerNumber )
SELECT AR1_CustomerMaster.Division, AR1_CustomerMaster.CustomerNumber
FROM AR1_CustomerMaster
WHERE (((AR1_CustomerMaster.Division) Not In (Select
[TmemberPasswords].[Division] From [TmemberPasswords])) AND
((AR1_CustomerMaster.CustomerNumber) Not In (Select
[TmemberPasswords].[CustomerNumber] From [TmemberPasswords])));

Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Insert row in Query Analyzer

Hi there !

I have a table called customer which got
id = int
name = varchar
address = varchar
email= varchar

can you please write the syntax that insert the below data in the table using query Analyzer

id = 1
name = fadil
Address = London
email = fadil1977@.hotmail.com

Thank you for your time and help.INSERT INTO Customer
VALUES (1, 'fadil', 'London', 'fadil1977@.hotmail.com')

Insert records in multiple tables via store proc

Any help will be appreacited
I need to insert records into multiple tables via store proc. I wrote a query statement that does that, but I need to carry one value to the next piece of the script, which is easy via query analizer, but I do not know how to pass that value to the next step in the store proc. Please see the query I am using to give me some light. The case sample is 9731285 and needs to be carry out to each step in the store proc. Thank you!
DECLARE @.Casenumber as char(20
SET @.CASENUMBER = '9731285
INSERT INTO tblCaseDat
(CaseNumber, DisplayCaseNumber
VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim(Right(@.casenumber,18))
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor1'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor1')), '5
from tblcasedat
where casenumber = @.casenumber
G
declare @.casenumber char(20
select @.casenumber = '9731285
INSERT INTO tblname (longname
values (@.casenumber+' '+ 'Debtor2'
g
declare @.casenumber char (20
select @.casenumber = '9731285
INSERT INTO tblCasename (caseid, NameID, NameTypeID
(select caseid, (Select NameI
from tblnam
where longname =(@.casenumber+' '+ 'Debtor2')), '6
from tblcasedat
where casenumber = @.casenumber
Gthe batch separator (GO) resets any variable declarations.
hence, if you remove the 'GO'
remove the additional DECLARE / SET CaseNumber,
you can execution the entire set of statments as one
batch, which can be put into a stored proc,
also, an explicit BEGIN TRAN , COMMIT TRAN around the
entire set of inserts statements is probably warranted
>--Original Message--
>Any help will be appreacited.
>I need to insert records into multiple tables via store
proc. I wrote a query statement that does that, but I need
to carry one value to the next piece of the script, which
is easy via query analizer, but I do not know how to pass
that value to the next step in the store proc. Please see
the query I am using to give me some light. The case
sample is 9731285 and needs to be carry out to each step
in the store proc. Thank you!!
>DECLARE @.Casenumber as char(20)
>SET @.CASENUMBER = '9731285'
>INSERT INTO tblCaseData
> (CaseNumber, DisplayCaseNumber)
>VALUES (@.CASENUMBER, (left(@.casenumber, 2))+'-'+rtrim
(Right(@.casenumber,18)))
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor1')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor1')), '5'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>declare @.casenumber char(20)
>select @.casenumber = '9731285'
>INSERT INTO tblname (longname)
> values (@.casenumber+' '+ 'Debtor2')
>go
>declare @.casenumber char (20)
>select @.casenumber = '9731285'
>INSERT INTO tblCasename (caseid, NameID, NameTypeID)
> (select caseid, (Select NameID
> from tblname
> where longname =(@.casenumber+' '+ 'Debtor2')), '6'
> from tblcasedata
> where casenumber = @.casenumber)
>GO
>.
>

insert question

This is more of a sql query syntax question then anything but I need to run it on my handheld.

I have 3 data collection tables, and 3 staging tables that will be used to PUSH the data back to the SQL Server.

so here is what happens:

to populate staging table 1 I do

insert into stagingtable1 (id, col1, col2, col3) select newid, col1, col2, col3 from datacollection table1

staging table 2:

insert into stagingtable2 (id) select id from stagingtable1

staging table 3.

insert into stagingtable2 (id - (now this ID field needs to be the same id field as staging table 2 and staging table 1 have) col1, col2, col3, col4, from DataCollectionTable2

how can I create the insert SQL for my staging table 3?

revised:

there is no columns I can join on to get the uploadID to insert into stagingtable 3

INSERT Query, Guid AutoIncrement Help

Im still learning my way around SQL and queries and i was wondering :

How do you get a SQL Table to autoincrement a Guid? (is it "Is Identity?" or "RowGuid"...)

How would i create a new row with a new Guid, and insert into the values i want without specifying the Guid?

You would need to use NewID() to get the next random GUID.

INSERT INTO yourTable (col1, col2,...) VALUES (@.val1, NewID(), @.val3,...)

INSERT QUERY!

Hi all,
I am attempting to populate a table with the results of the following
queries.
Unsure of what syntax is required.
Help appreciated!!!!
INSERT INTO [Question] (ExamID, Question, Idx) ?
(SELECT MAX(ExamID) FROM [EasyLearning].[dbo].[Exam]),
(SELECT ex2_mult_Q FROM exam2 WHERE ex2_name = @.ExamName),
(SELECT Idx FROM Exam WHERE ExamID = (SELECT MAX(ExamID) FROM
[EasyLearning].[dbo].[Exam]) + 1)
Cheers AdamHi Adam,
INSERT INTO [Question] (ExamID, Question, Idx)
SELECT
(SELECT MAX(ExamID) FROM [EasyLearning].[dbo].[Exam]) AS ExamID,
(SELECT ex2_mult_Q FROM exam2 WHERE ex2_name = @.ExamName) AS
Question,
(SELECT Idx FROM Exam WHERE ExamID =
(
SELECT MAX(ExamID) FROM [EasyLearning].[dbo].[Exam]) + 1
)
) AS Idx
HTH, jens Suessmeyer.|||Look up INSERT.SELECT in Books Online, but start by building the SELECT quer
y
that returns all the values that you intend to insert.
Once the query returns the correct values, use it in the INSERT...SELECT
query.
If more than one row should be returned, make sure the values are properly
related - right now I see no relationship between the three selects - but if
you do, and it works as you expect, then that's good enough for me.
ML
http://milambda.blogspot.com/

Insert Query with stored procedure

Hi,

is it possible to create an "INSERT INTO .... "Select from stored
procedure" Query?

I want to create an temporary table. In this table I want to enter the data,
which I can get from an stored procedure.
But in the FROM-clause a stored procedure is not allowed?"Harald" <yixxu@.yahoo.de> wrote in message news:bfhh70$jt5$1@.online.de...
> Hi,
> is it possible to create an "INSERT INTO .... "Select from stored
> procedure" Query?
> I want to create an temporary table. In this table I want to enter the data,
> which I can get from an stored procedure.
> But in the FROM-clause a stored procedure is not allowed?

Use INSERT INTO/EXEC, e.g.,

INSERT INTO #TempTable (col1, col2, col3)
EXEC MyStoredProcedure 1, 2

Regards,
jag

Insert query with nested select and parameter

hey there, i'm trying to move one record from one table to the next,
so i'm doing this by doing an insert into table, then delete from the
previous table. Only thin g is on the insert i want to include a
parameter. Can't work how to do this. Do you think i need to do
another update query and insert the parameter that way, after the
insert is done?

ALTER PROCEDURE dbo.ReturnLoan

(
@.strBarcode varchar(100),
@.strLibrary varchar(100),
@.dateReturned datetime
)

AS
INSERT INTO Loan_History
(
Barcode,
UserID,
Date_Borrowed,
Library

)
Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHERE
Barcode = @.strBarcode
AND Library = @.strLibrary
;

DELETE FROM Loans
WHERE Barcode = @.strBarcode
AND Library = @.strLibrary ;

RETURNOn 19 Oct 2004 00:04:39 -0700, Mark wrote:

(snip)
> on the insert i want to include a
>parameter.

INSERT INTO Loan_History
(
Barcode,
UserID,
Date_Borrowed,
Library,
SomeOtherColumn

)
SELECTBarcode,
UserID,
Date_Borrowed,
Library,
@.YourParameterHere
FROMLoans
WHEREBarcode = @.strBarcode
ANDLibrary = @.strLibrary

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

INSERT query using linked servers is slow.

Good day everyone,
I am setting up a simple data mart on a server running SQL 2005. It gets
updated nightly from another server with SQL 2000. Originally both database
s
were on the same server under 2000. Now that they are on different servers,
some of the INSERT queries seem to be running for an abnormally long time (s
o
long that I end up having to kill them).
All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
from [Local-Tables]". Most only join together 3 or 4 tables, using key
fields. Only a couple of them are running long, the others complete in abou
t
the same time as before. The problem tables are not at all large compared t
o
the ones that work fine, and in some cases even have less activity (new
records).
I am pretty sure the two servers are linked correctly, as the majority of
these SQL commands still work fine. The remote query timeout parameter in
sp_configure has been set to 0, since the default of 600 was causing
problems. Other than that, no changes have been made on either server.
Any suggestions for other things I might check? Thanks in advance for your
help.Couple of things:
Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TRAN
on linked servers.
Have you checked all the usual rules which apply to large INSERTs? eg
- presumably there is no-one else logged in to the database when you are
doing these large inserts ie no danger of locking.
- are there a lot of indexes on the table you're inserting to; this will
slow things down
- are there any triggers firing? Think about disabling them
- is there any other audit stuff going on, traces etc?
- make sure all table names are fully qualified eg
server01.northwind.dbo.authors (presumably you have to do this anyway)
- if you've got IDENTITY columns, particularly as primary keys on the target
table, I believe these _can_ cause hotspots, although these are supposed to
be a minor concern on modern hardware
- think about breaking up your inserts, say 10,000 rows at a time so you can
keep track of their progress. I've seen techniques for doing this in loops
on the web using either SET ROWCOUNT or TOP
Hope that helps.
Let me know how you get on.
Damien
"Chris F" wrote:

> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both databa
ses
> were on the same server under 2000. Now that they are on different server
s,
> some of the INSERT queries seem to be running for an abnormally long time
(so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in ab
out
> the same time as before. The problem tables are not at all large compared
to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for you
r
> help.
>|||Chris
How much data do you insert?
Consider script out all indexes (remove them) and run the INSERT ,now that
after inserting re-create all indexes
"Chris F" <ChrisF@.discussions.microsoft.com> wrote in message
news:18089707-C1D6-4296-B937-C17D34DCAE65@.microsoft.com...
> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both
> databases
> were on the same server under 2000. Now that they are on different
> servers,
> some of the INSERT queries seem to be running for an abnormally long time
> (so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in
> about
> the same time as before. The problem tables are not at all large compared
> to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for
> your
> help.
>|||Thanks for your help.
Actually I am not using any form of BEGIN TRAN, since I keep getting a 7391
error in all cases. I am running a stored procedure consisting of several
delete and insert statements. Now that they are through the backlog (from
not having run for several days) all but one of the procedures have
acceptable run times.
Going down your list of checks, there are no other users, the only index is
the PK, no triggers, no audits/traces, no identity columns. I have been
fully qualifying the table on the remote server but not the local one where
the stored proc kicks off, I can try this and see if it helps. Will also
look at breaking up the query (I need to wait until the current run finishes
,
I found out over the wend that it does eventually). The only table we ar
e
still having problems with is the largest in the DB.
"Damien" wrote:
> Couple of things:
> Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TR
AN
> on linked servers.
> Have you checked all the usual rules which apply to large INSERTs? eg
> - presumably there is no-one else logged in to the database when you are
> doing these large inserts ie no danger of locking.
> - are there a lot of indexes on the table you're inserting to; this will
> slow things down
> - are there any triggers firing? Think about disabling them
> - is there any other audit stuff going on, traces etc?
> - make sure all table names are fully qualified eg
> server01.northwind.dbo.authors (presumably you have to do this anyway)
> - if you've got IDENTITY columns, particularly as primary keys on the targ
et
> table, I believe these _can_ cause hotspots, although these are supposed t
o
> be a minor concern on modern hardware
> - think about breaking up your inserts, say 10,000 rows at a time so you c
an
> keep track of their progress. I've seen techniques for doing this in loop
s
> on the web using either SET ROWCOUNT or TOP
>
> Hope that helps.
> Let me know how you get on.
>
> Damien
> "Chris F" wrote:
>