Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql 2008 Table duplication

Posted on 2011-09-14
10
Medium Priority
?
169 Views
Last Modified: 2012-05-12
If I wanted to create a table and make the columns equal the data from another table, can i do this??

Example:

I have a table called plantname_foods_USA_INC_Foodinfo
Under that table I have a column called contract_information_grower
The data in that column is:   grower 1

I want to create a table called plantname
a column called Farm
and data that equals the data from the contract_information_grower column in the plantname_foods_USA_INC_Foodinfo table.

Thanks!!

0
Comment
Question by:HDM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36539370
select contract_information_grower as Farm
into plantname
from plantname_foods_USA_INC_Foodinfo
where 1 = 0
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36539379
The above will create the single-column table empty ... to create it with the actual data, remove the WHERE clause.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36539383
also, the above assumes that the table plantname doesn't exist yet.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:HDM
ID: 36539399
Will this create a "Live" link??

If the data in the first table changes will it reflect to the new table??
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36539406
no, what you are talking about is a view ... do this:

create view plantname
as
select contract_information_grower as Farm
from plantname_foods_USA_INC_Foodinfo
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36539407
... then you can do this:  select * from plantname
and it will reflect changes in the actual table plantname_foods_USA_INC_Foodinfo
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36539416
... and of course you can include additional columns (if you want) from the original table.

OR, if what you are looking for is all the distinct values from the original column (e.g. without repeats) then include the distinct keyword:

create view plantname
as
select distinct contract_information_grower as Farm
from plantname_foods_USA_INC_Foodinfo
0
 

Author Comment

by:HDM
ID: 36539454
It will take a me a few to assimulate that info.

Thanks!!
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 36539493
Take your time!  Here's more ...

From your perspective a view is really nothing more than a named query.  So you can include in a view pretty much anything you can include in a SELECT query ... this includes GROUP BY, MIN, MAX, COUNT, as well as table joins.  In your case, if you want to include additional columns in your simple view, that's easy enough (this is just an example)...

create view plantname  -- fyi - use alter instead of create to change an existing view
as
select T.contract_information_grower as Farm,  T.columnA as MyNewNameA,  T.columnB as MyNewNameB
from plantname_foods_USA_INC_Foodinfo T

GO

select * from plantname
0
 

Author Closing Comment

by:HDM
ID: 36539722
SWEET!!  Found "Alias"  :-)

That fixes EVERYTHING.  :-)

Thanks SO much!!
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question