Article: Using SELECT to INSERT records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Using SELECT to INSERT records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-08-04 : 05:36:30

Edwin writes "Ok, this may be simple to some, but it sure is a puzzle to me. I want to move a set of data from one table to another table with a similar structure. What I figure was a shady solution: Selecting the data from the source table, then opening the other table and using a loop to populate the destination table. Question: Is there a way I can use just ONE insert statement to do all this? Sort of like incorporating the select statement into the insert statement? If there is one, then please give me some example code." We sure can do this in one statement (but looks like two).

Read Using SELECT to INSERT records

rahul
Starting Member

1 Post

Posted - 2002-04-14 : 01:44:35

Hello,

Can anyone tell me how to insert file into sql server database with servlet or jsp as scripting language?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-14 : 07:56:46

What kind of file are you inserting? Is it a comma-separated/tab-demilited, or other kind of text file? You can use DTS or BULK INSERT to import the file into your SQL Server tables. Books Online has more detail. You would have to upload the file to the SQL Server if you are going to use BULK INSERT.

MikeR
Starting Member

2 Posts

Posted - 2002-04-25 : 05:00:58

quote:

How can this be done when dealing with 2 different databases ? For example to copy a table from a sql-server to access ?

Use SQL Server's Data Transformation Services

Mike

stan
Starting Member

1 Post

Posted - 2003-02-18 : 13:07:35

Okay, this is just a syntax question, but I can't find the answer anywhere...

I know the syntax for inserting values into a table and I know the syntax for inserting the results of a select statement into a table.
But, I need to combine these two methods and I've done it before but can't remember how to do it!

I have so far:
INSERT INTO Members ( memberID, memberareaID )
VALUES(@memberID,(SELECT memberareaID FROM MemberAreas WHERE areadescription = '@areadescription'))

The only difference is that, previously, I had this working in SQL but now I'm doing it in MS Access so maybe that's why this is not working???

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-02-19 : 08:42:02

quote:

I know the syntax for inserting values into a table and I know the syntax for inserting the results of a select statement into a table.
But, I need to combine these two methods and I've done it before but can't remember how to do it!

I have so far:
INSERT INTO Members ( memberID, memberareaID )
VALUES(@memberID,(SELECT memberareaID FROM MemberAreas WHERE areadescription = '@areadescription'))

Try this:

INSERT INTO Members ( memberID, memberareaID )
SELECT @memberID, memberareaID FROM MemberAreas WHERE areadescription = @areadescription

OS

Edit: You might want to move this discussion from here, start a new thread.

Edited by - mohdowais on 02/19/2003 08:43:56

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-19 : 08:50:04

SELECT *
INTO new_table
FROM my_table

will create a duplication of my_table and insert all rows.
I would, however, create the table explicitly.

Bambola.

domagoj
Starting Member

5 Posts

Posted - 2003-02-19 : 08:51:00

OK. but is there statement that will actually MOVE (copy+delete) a record?

Bambola
Posting Yak Master

103 Posts

Posted - 2003-02-19 : 09:02:53

quote:

OK. but is there statement that will actually MOVE (copy+delete) a record?

I don't think so. You will have to do it yourself (within a transaction)

Bambola.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-19 : 17:00:10

A Logical MOVE and COPY would be treated a single unit of work with in the same transaction...something like:

BEGIN TRAN

INSERT INTO Table1 (key, col1, col2, ect) SELECT key, col1, col2, ect From Table2 Where key = something

Delete From Table2 Where key = something

COMMIT TRAN

jourdan
Starting Member

1 Post

Posted - 2003-05-13 : 17:19:42

how do you copy sone rows but in the same table??

regards

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 17:26:38

Exactly the same as if it were coming from another table... with the INSERT...SELECT syntax:

INSERT INTO yourTable (f1, f2, f3)
SELECT f1, f2, f3
FROM yourTable
WHERE ... some condition ....

- Jeff

rriordan
Starting Member

3 Posts

Posted - 2003-07-19 : 01:39:57

Hey. Thanks for looking at this.

I tried the generic insert from near the top of this thread to copy from one table to another. I have two tables that are EXACTLY the same, save for their name. When I tried the SQL:

INSERT INTO main_a
SELECT * from main_b

I get the following error (from Query Analyser RUN):
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

I am trying to avoid explicitly typing all the field names into the statement (there are about 50 of them). Can anyone help?

rriordan
Starting Member

3 Posts

Posted - 2003-07-19 : 01:43:24

Please disregard the above plea for help. Sorry. I redesigned main_a but forgot to do the same operations on main_b. My bad.

jimdalrymp
Starting Member

1 Post

Posted - 2005-07-21 : 12:57:57

What if you are inserting a large number of records from one table to another using INSERT SELECT and there is a failure on one or more of the records? Will the whole process be rolled back? If not, how do I know which records failed? This is the reason I use a cursor, but if there are ways to identify failing records, I would love to use a select statement instead. Thanks for the help.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-22 : 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 Post

Posted - 2006-04-11 : 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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-04-12 : 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 - 2007-01-11 : 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

1 Post

Posted - 2007-04-27 : 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 - 2007-07-05 : 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!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources