Insert from query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Insert from query

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-02-08 : 06:42:30

How do I use Enterprise manager to insert records from one table into a blank table ? I open my table (with data) as a query and change it to an Insert From query but it doesn't seem to build the query correctly.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 06:50:17

How about using this in query Analyser?

Insert into emptyTable(columns)
Select columns from sourceTable

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-08 : 06:51:59

Why do you want it to do from Enterprise manager only..

Try this if the table does not exist..
SELECT *
INTO Newtable
FROM OldTable

If table already exist then you can use
Insert into NewTable
Select * from OldTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 07:01:54

The first query doesnt make the target table to have all indices that source table has.
The second is better if you explicitely specify the columns than *

Madhivanan

Failing to plan is Planning to fail

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 07:24:54

quote:
Originally posted by shallu1_gupta
Why do you want it to do from Enterprise manager only..

This is a nice feature I use quite much when creating test scenarios for my applictions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 08:04:51

>>This is a nice feature I use quite much when creating test scenarios for my applictions.

Well. Hereafter make use of Query Analyser with Queries

Madhivanan

Failing to plan is Planning to fail

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 08:28:28

quote:
Originally posted by madhivanan
Well. Hereafter make use of Query Analyser with Queries

You mean using "Script Object to..." function?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-08 : 08:47:42

No. As I specified in my first reply

Madhivanan

Failing to plan is Planning to fail

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 09:18:11

Ahhh...off course, that is what I do ;)

1. copy data to new table with "insert into test_table(...) select"
2. perform test case
3. drop test_table

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-02-08 : 10:35:39

Sorry I have not checked your replies sooner. I have tried

Insert into Table1
Select * from tblFilesnew

and get this error

Insert Error: Column name or number of supplied values does not match table definition.

I have many, many fields. Surely I do not have to ype them all out....

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 10:44:43

quote:
Originally posted by Pinto
Insert Error: Column name or number of supplied values does not match table definition.

I have many, many fields. Surely I do not have to ype them all out....

Use shallu1_gupta's statements

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-02-08 : 10:49:44

I thought I had ....

Insert into Table1
Select * from tblFilesnew

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 11:05:31

quote:
Originally posted by Pinto

I thought I had ....

Nope, then you have to specify the <columns>.

Insert into emptyTable(col1, col2, col3)
Select col1, col2, col3
from sourceTable

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-02-08 : 11:09:40

Oh well, I have about 50 fields so better start typing............thanks, sveroa

sveroa
Starting Member

14 Posts

Posted - 2006-02-08 : 11:15:07

quote:
Originally posted by Pinto

Oh well, I have about 50 fields so better start typing............thanks, sveroa

Nope, use the statement from 'Shallu'

>Try this if the table does not exist..
>SELECT *
>INTO Newtable
>FROM OldTable

then you don't have to specify the column names.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 01:26:00

quote:
Originally posted by Pinto

Sorry I have not checked your replies sooner. I have tried

Insert into Table1
Select * from tblFilesnew

and get this error

Insert Error: Column name or number of supplied values does not match table definition.

I have many, many fields. Surely I do not have to ype them all out....

It seems you didnt read my first reply clearly

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources