Move View data into table - 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

Move View data into table

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-14 : 09:52:26

I have a view that I created, and wanted to move that into a table.

Ive created the table with the exact columns to match the view.

I tried this

select * into master_table from main_view

I get this error:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'master_table' in the database.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 10:06:56

You need to do this...

INSERT INTO master_table
SELECT * FROM main_view

SELECT INTO will create the table.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-14 : 10:19:58

Now my master_table has 1 more row then my view, is this the reason why it throws an error?

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

I have an extra column in the master table.... i am going to fill in these values later...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 10:23:25

quote:
Originally posted by jjmusicpro

Now my master_table has 1 more row then my view, is this the reason why it throws an error?

No..as the error states...the master_table has already been created. So you will need to INSERT into the 'already created' master_table.

using SELECT INTO will create the master_table for you...but you dont want that as it has alreday been created.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-14 : 10:25:53

quote:
Originally posted by jjmusicpro

Now my master_table has 1 more row then my view, is this the reason why it throws an error?

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

I have an extra column in the master table.... i am going to fill in these values later...

Then specify the column names in the INSERT statement(ignore the column that is not present in the main_view) ..

INSERT INTO master_table (col1,col2,col3)
SELECT col1,col2,col3 FROM main_view

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources