November 18, 2011 at 11:06 am
Hi, I am trying to create a table selecting from a view and the view has alias names to most of the columns defnined in it. When i do select * into tab1 from vw1 i get the table defition for alias columns as
ALTER TABLE [dbo].[tab1 ] ADD [column1] [char](2) NULL;
is this still ok ? if i want ot have a table definition with alias names defined in the view how do i do that?
November 18, 2011 at 12:07 pm
I am not sure what you question is. You are trying to use select into to create a new table. What does the alter table statement have to do with that? If you can explain clearly what is it you are trying to do we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2011 at 12:22 pm
when i created new table with select into and when i check the definition of the new table by doing "script as create" i see alter stmts in the definition where in i was expecting to just columns with create table stmt.
November 18, 2011 at 12:32 pm
That is odd. I have never seen that happen. Can you just make your select into use the code from the view instead? Might be easier than trying to figure out what went wrong.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2011 at 1:06 pm
NO even that would do tha same.
November 18, 2011 at 1:09 pm
How can a select into statement make the create table contain alter statements??
select ColumnListIncludingAnyAliases into MyNewTable
from ThisIsNotTheViewButARealTable
join another table
join yet another...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2011 at 1:24 pm
Tara-1044200 (11/18/2011)
.....when i check the definition of the new table by doing "script as create" i see alter stmts in the definition ......
Please confirm how you are perfoming "script as create"...assume this is via SSMS?
please confirm SQL version you are running
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 18, 2011 at 1:28 pm
because i am selecting it from a view which has something like
select [col1] a,[col2] b from ....
when do script as for the same it has something like
SET ANSI_PADDING ON
ALTER TABLE [dbo].[tb] ADD [Col1] [char](3) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[tb] ADD [Col2] [char](5) NULL
may be its just how it is scripted out but table may be still ok, i assume but not sure.
November 21, 2011 at 7:37 am
Hi
I really not getting what you want to say.
When your saying that view has alies names of the orignal table then how come your getting the orignal column for the table that you creating from view its no ware possible and its quite waired unless your again keep alies of orignal name of column name for the view when creating new table.
eg.
Select a as COL1,b as COL2
into newtable
from ViewTable
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
November 21, 2011 at 7:02 pm
If you open up the table in Object Explorer and expand the columns, do they have the right names? (Be sure to right-click on the table and hit 'Refresh' before checking the column names).
SB
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply