SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to copy the results of a query to temp table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Srinivas007
Starting Member

India
7 Posts

Posted - 03/21/2013 :  02:23:48  Show Profile  Reply with Quote
I have a query which give results with duplicate column names. I want to copy all my results to a temp table (with duplicate column names as it is).

Is there any way I can copy the results with duplicate column names to a temp table? Please help.

--------------------------------

If yes, how can I copy the results to a temp table on fly (dynamically)?

Thanks,
Sri

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/21/2013 :  02:30:36  Show Profile  Reply with Quote
you can if you use aliases for duplicate names. A table cant have multiple columns with same name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/21/2013 :  02:31:35  Show Profile  Reply with Quote
The results can be copied to table using INSERT...SELECT if table exists

or SELECT ... INTO Table... if table has to be created on the fly.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Srinivas007
Starting Member

India
7 Posts

Posted - 03/21/2013 :  02:58:59  Show Profile  Reply with Quote
yes, I am aware of that.

But my problem is -

1) I have lot of columns with the same name in my results set where I need to take pain to write the alias for each column. The same I need to do for multiple queries which are already in place by using 'select * ..' for all queries. I wanted to avoid modifying my existing queries.

2) I can use insert to copy the results but my procedure will run for multiple iterations with multiple set of results where I cannot create table on fly with dynamic column names. So, I was trying to use 'Select * into...'

Is there any way to overcome these problems and get a solution for it? Any help is appreciated.

Thanks
Sri
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/21/2013 :  04:20:51  Show Profile  Reply with Quote
1, why cant you write the logic to generate unique column aliases on the fly? If you can elaborate on scenario somebody will be able to help you out.
2, you can use select ...into but in that case table name also should be unique otherwise you'll end up trying to create a table which already exists

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 03/21/2013 :  12:10:49  Show Profile  Reply with Quote
1. You are starting with a flawed "design." Having result sets with mutiple columns of the same same is just silly. If you can can, take this time to fix the flaws that already exists so you can save yourself pain down the road.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000