| Author |
 Topic  |
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 08/04/2000 : 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 |
|
|
Anonymous
Starting Member
0 Posts |
|
|
bjcramer
Starting Member
USA
0 Posts |
Posted - 04/26/2001 : 11:11:36
|
| If your fields don't exactly match... I recently ran into a similar situation as the original post. However, my destination table had a required field that wasn't present in the source table schema.
As an example, the destination table has a required field that identifies the business unit who added the record, and the source table tracks business groups (business units are subsets of the business groups) by another identifier.
I had to populate the bu_id column with a business unit id. So, I modified my source table select statement to accomodate the insert.
Here's the code:
insert into BU_SECTION_LINE
(bu_id, section_id, name, short_desc, date, meta_keyword,
auth_req, graphic_request, graphic_completed, city, state_province,
country, region, attr_flag_1, attr_flag_2, attr_flag_3, old_id,
old_table_name, attr_flag_4)
select 24 bu_id, section_id, name, short_desc, date, meta_keyword,
auth_req, graphic_request, graphic_completed, city,
state_province, country, region, attr_flag_1, attr_flag_2,
attr_flag_3, old_id, old_table_name, NULL attr_flag_4
from GROUP_SECTION
where group_section_id > 1
GROUP_SECTION does not contain the required column bu_id, but it is required by BU_SECTION_LINE. So I gave it the desired value and aliased the value with the BU_SECTION_LINE name. You may also recognize that the attr_flag_4 has NULL in front of it in the select statement. It isn't a required field, but the insert must include it.
Any field can be filled in this way. |
 |
|
|
jraha
Starting Member
USA
16 Posts |
Posted - 05/23/2001 : 13:29:09
|
When I try to use this type of syntax
insert #tempTable (usernum)
select usernum from tUsers
I get the following error message:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temptable'.
This only happens on one of my databases though. Creating the #tempTable implicitly works on other databases that I'm using. What setting am I missing?
Thanks,
Joel
|
|
|
|
k.i.p.
Starting Member
USA
2 Posts |
Posted - 05/23/2001 : 15:13:45
|
Joel,
Try insert into
That might work.
Regards,
Luke
Edited by - k.i.p. on 05/23/2001 15:30:58 |
|
|
|
Funkenstein
Starting Member
Norway
1 Posts |
Posted - 05/27/2001 : 19:59:15
|
| How can this be done when dealing with 2 different databases ? For example to copy a table from a sql-server to access ? |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/28/2001 : 09:38:50
|
into in insert into is optional i.e. doesn't affect the query.
insert #tempTable (usernum)
select usernum from tUsers
gives Invalid object name '#temptable'
This is because the temp table doesn't exist
either select usernum into #tempTable from tUsers or create the temp table first.
How can this be done when dealing with 2 different databases.
insert dbname1.owner1.tbl1 (cols...) select (cols...) from dbname2.owner2.tbl2
For example to copy a table from a sql-server to access
Could probably do this by creating a linked server to the access database and fully qualifying the table name
linkedserver.database.owner.table.
|
|
|
|
rahul
Starting Member
1 Posts |
Posted - 04/14/2002 : 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
USA
15559 Posts |
Posted - 04/14/2002 : 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 - 04/25/2002 : 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 Posts |
Posted - 02/18/2003 : 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
United Arab Emirates
1456 Posts |
Posted - 02/19/2003 : 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
Italy
103 Posts |
Posted - 02/19/2003 : 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 - 02/19/2003 : 08:51:00
|
OK. but is there statement that will actually MOVE (copy+delete) a record?
|
|
|
|
Bambola
Posting Yak Master
Italy
103 Posts |
Posted - 02/19/2003 : 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 - 02/19/2003 : 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 Posts |
Posted - 05/13/2003 : 17:19:42
|
how do you copy sone rows but in the same table??
regards
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/13/2003 : 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 - 07/19/2003 : 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 - 07/19/2003 : 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 Posts |
Posted - 07/21/2005 : 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. |
|
|
|
Topic |
|
Article: Using SELECT to INSERT records
