SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using SELECT to INSERT records
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 07/22/2005 :  07:27:17  Show Profile  Reply with Quote
"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.
Go to Top of Page

scuffell
Starting Member

1 Posts

Posted - 04/11/2006 :  11:43:17  Show Profile  Reply with Quote
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?
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 04/12/2006 :  06:20:59  Show Profile  Reply with Quote
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.
Go to Top of Page

Sim
Starting Member

12 Posts

Posted - 01/11/2007 :  13:23:11  Show Profile  Reply with Quote
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

Go to Top of Page

rroades
Starting Member

USA
1 Posts

Posted - 04/27/2007 :  12:10:02  Show Profile  Reply with Quote
[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?
Go to Top of Page

monkeeofevil
Starting Member

2 Posts

Posted - 07/05/2007 :  16:09:23  Show Profile  Send monkeeofevil an AOL message  Send monkeeofevil an ICQ Message  Click to see monkeeofevil's MSN Messenger address  Send monkeeofevil a Yahoo! Message  Reply with Quote
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!
Go to Top of Page

monkeeofevil
Starting Member

2 Posts

Posted - 07/05/2007 :  16:45:21  Show Profile  Send monkeeofevil an AOL message  Send monkeeofevil an ICQ Message  Click to see monkeeofevil's MSN Messenger address  Send monkeeofevil a Yahoo! Message  Reply with Quote
Well I am retarded. You can use Data Transformation Services -> Import inside of Enterprise Manager to do this. My bad!
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/05/2007 :  21:35:00  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000