| Author |
 Topic  |
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
 Posted - 07/22/2005 : 07:27:17
|
"Will the whole process be rolled back"...yes.
Look into using a smaller commit point....and continually reduce same if you get a failure...so you can find the 1st failing record.
You need to investiate use of the WHILE statement
Search here for WHILE + COMMIT...there's been some discussions on this approach before.
|
 |
|
|
scuffell
Starting Member
1 Posts |
Posted - 04/11/2006 : 11:43:17
|
When I run INSERT into view1 select * from #temptable where both have the same column values and there is an INSTEAD OF insert trigger on the view, I only get the last record inserted into the database. Is this because my trigger is not designed for an INSERT INTO SELECT FROM transaction? |
|
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 04/12/2006 : 06:20:59
|
no probably your trigger is only programmed to insert 1 record...instead of inserting a set of records...TRIGGERS get launched once ber insert statement, not once per record being inserted.
supply the detail of the TRIGGER please and you may get more progress. |
|
|
|
Sim
Starting Member
12 Posts |
Posted - 01/11/2007 : 13:23:11
|
I believe I am attempting to do something similar and would appreciate any help.
I need to insert rows into a table with both static and variable data.
The jist of it is:
insert into archive
actionid,statusid,archiveid
200,101,"variable data from select statement"
All rows will have 200 and 101 but I will have different archiveids that will come from my select statement. I want to insert all archiveids that meet my conditions but all rows will have the same action_id and status_id.
It occurred to me to build a temp table for the static data but I am unsure how to bring this together.
Please help
|
|
|
|
rroades
Starting Member
USA
1 Posts |
Posted - 04/27/2007 : 12:10:02
|
[quoteIf your tables are EXACTLY the same and they do not have any constraints that would prevent a direct insert, then you may use:
INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)
[/quote]
I need to just APPEND new records, and did the above with a WHERE as follows:
arinvc.cinvno not in (select arinvc.cinvno from arinvc)
I get an error on the ODBC: error calling Prepare from the command text.
Any thoughts? |
|
|
|
monkeeofevil
Starting Member
2 Posts |
Posted - 07/05/2007 : 16:09:23
|
I have a similar situation where I need to migrate *some* data from one table in one database over to another table running on *a different* database.
I understand how an insert-select works, but I'm not so sure about the syntax for this query. We are running SQL Server 2003.
What I have been attempting is this:
INSERT INTO members
(email, fname, lname, prefix, title, sex, join_date, suffix)
SELECT 'email' AS email, 'fname' AS fname, 'lname' AS lname, 'prefix' AS prefix, 'title' AS title, 'sex' AS sex, 'join_date' AS join_date, 'suffix' AS suffix
FROM dbo.GWP_IMPORT.[01_GULF_WEB_PANEL_LIST]
I get this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.GWP_IMPORT.[01_GULF_WEB_PANEL_LIST]'
Obviously this means that it doesn't know where to find the 01_GULF_WEB_PANEL table.
Members is in one database , and Gulf Web Panel List table is in another database ( on the same server )... what am i doing wrong here? It's probably something stupid. A quick reply would be much appreciated!
|
|
|
|
monkeeofevil
Starting Member
2 Posts |
Posted - 07/05/2007 : 16:45:21
|
| Well I am retarded. You can use Data Transformation Services -> Import inside of Enterprise Manager to do this. My bad! |
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 07/05/2007 : 21:35:00
|
Just from reading your statement I would guess that GWP_IMPORT was the database name. In that case the statement would read like:
INSERT INTO members
(email, fname, lname, prefix, title, sex, join_date, suffix)
SELECT 'email' AS email, 'fname' AS fname, 'lname' AS lname, 'prefix' AS prefix, 'title' AS title, 'sex' AS sex, 'join_date' AS join_date, 'suffix' AS suffix
FROM dbo.GWP_IMPORT.[01_GULF_WEB_PANEL_LIST]
The FROM clause needs to be database.owner.object.
===============================================
Creating tomorrow's legacy systems today.
One crisis at a time. |
|
|
|
Topic |
|
Article: Using SELECT to INSERT records
