Script to insert all columns' name into a table

Question

Post reply

Script to insert all columns' name into a table

adonetok

SSCrazy Eights

Points: 8395
More actions
October 14, 2010 at 7:19 am

#230347

How to create a Script to insert only columns' name of one table into another table?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mazzz SSCertifiable Points: 6139 More actions · Answer 1

select name

into #MyNewTable

from sys.syscolumns

where id = object_id('dbo.MyTable')

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

using my magic hat and mind reading glasses, i read your request two ways differently than mazzz did:

1. that you really meant "create an empty table with the same column names:

2. I need all the column names for a table as a comma delimited list.

--create an empty table with the same column names

SELECT *

INTO NEWTABLENAME

FROM ORIGINALTABLE

WHERE 1=2 --this makes the structure, but no data

--column names for a table as a comma delimited list

SELECT DISTINCT

t.name,

sq.Columns

FROM sys.tables t

JOIN (

SELECT OBJECT_ID,

Columns = STUFF((SELECT ',' + name

FROM sys.columns sc

WHERE sc.object_id = s.object_id

FOR XML PATH('')),1,1,'')

FROM sys.columns s

) sq ON t.object_id = sq.object_id

if my best guess wasn't close, can you explain the needs a little deeper?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

adonetok SSCrazy Eights Points: 8395 More actions · Answer 3

Thank you for help. I used code below. It works fine.

select column_name

into NewTable

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'myTable'

WayneS SSC Guru Points: 95460 More actions · Answer 4

Looks like you're making things more difficult than necessary Lowell! ;-):w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lowell SSC Guru Points: 323518 More actions · Answer 5

WayneS (10/14/2010)
Looks like you're making things more difficult than necessary Lowell! ;-):w00t:

clearly!

A lot like you, I'm trying to anticipate the requirement and provide a solid example, but obviously i jumped to far into the best guess scanrio there....whoops!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!