Table creation

Hi Guys,
I have this query which I use to pull the column names from an Informix table (i'm using a linked server connection):
SELECT c.colname AS ColumnName
FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
WHERE t.tabname = 'call_rec'   
AND t.tabtype = 'T'
and t.tabid = c.tabid 
AND t.tabid >= 100  ;

Open in new window

I need to create a table in SQL with the same column names from the above query.
How can I build this into the query above? The column type should be nvarchar(50) for all the columns.
Thanks
ColoplastAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT c.colname AS ColumnName
INTO MSSQL_TableName
FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
WHERE t.tabname = 'call_rec'   
AND t.tabtype = 'T'
and t.tabid = c.tabid 
AND t.tabid >= 100  ;

Open in new window

ColoplastAuthor Commented:
Thanks Vitor,
This gives me 1 column with 88 entries.
I need the query to create 88 columns.
Do you understand what I mean?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry. I misunderstood your question.
Try this solution:
DECLARE @strCreate NVARCHAR(MAX)
DECLARE @strColumns NVARCHAR(MAX)

SELECT @strColumns=STUFF(
	(SELECT c.colname AS ColumnName
	FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
	WHERE t.tabname = 'call_rec'   
		AND t.tabtype = 'T'
		and t.tabid = c.tabid 
		AND t.tabid >= 100
	FOR XML PATH('')),1,1,'')
	
SET @strColumns=REPLACE(@strColumns,',', ' NVARCHAR(50), ')
SET @strCreate = N'CREATE TABLE TableName (' + @strColumns + ' NVARCHAR(50))'

EXEC sp_executesql @strCreate

Open in new window

NOTE: When setting the @strCreate replace TableName with the real name for your new table.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Docteur_ZCommented:
I was working on a bit more compact syntax :

DECLARE @sQuery NVARCHAR(MAX)

Select @sQuery = 'create table toto(' +   substring(
        (
            Select ','+SDB.name + ' nvarchar(50)' 
            From sysdatabases SDB
            --ORDER BY SDB.name
            For XML PATH ('')
        ), 2, 1000) 
+ ');'

EXEC sp_executesql @sQuery

Open in new window

ColoplastAuthor Commented:
I'm getting the following error for both suggestions:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please replace the line EXEC sp_executesql @strCreate with
PRINT @strCreate

Open in new window

and post here the output.
ColoplastAuthor Commented:
The below is from Vitor's code:

CREATE TABLE InformixData (ColumnName>seqnum</ColumnName><ColumnName>acd</ColumnName><ColumnName>row_date</ColumnName><ColumnName>row_time</ColumnName><ColumnName>acwtime</ColumnName><ColumnName>ansholdtime</ColumnName><ColumnName>anslogin</ColumnName><ColumnName>assist</ColumnName><ColumnName>audio</ColumnName><ColumnName>callid</ColumnName><ColumnName>calling_pty</ColumnName><ColumnName>conference</ColumnName><ColumnName>consulttime</ColumnName><ColumnName>da_queued</ColumnName><ColumnName>dialed_num</ColumnName><ColumnName>dispivector</ColumnName><ColumnName>disposition</ColumnName><ColumnName>disppriority</ColumnName><ColumnName>dispsplit</ColumnName><ColumnName>disptime</ColumnName><ColumnName>dispvdn</ColumnName><ColumnName>duration</ColumnName><ColumnName>eqloc</ColumnName><ColumnName>event1</ColumnName><ColumnName>event2</ColumnName><ColumnName>event3</ColumnName><ColumnName>event4</ColumnName><ColumnName>event5</ColumnName><ColumnName>event6</ColumnName><ColumnName>event7</ColumnName><ColumnName>event8</ColumnName><ColumnName>event9</ColumnName><ColumnName>firstivector</ColumnName><ColumnName>firstvdn</ColumnName><ColumnName>vdn2</ColumnName><ColumnName>vdn3</ColumnName><ColumnName>vdn4</ColumnName><ColumnName>vdn5</ColumnName><ColumnName>vdn6</ColumnName><ColumnName>vdn7</ColumnName><ColumnName>vdn8</ColumnName><ColumnName>vdn9</ColumnName><ColumnName>held</ColumnName><ColumnName>holdabn</ColumnName><ColumnName>lastcwc</ColumnName><ColumnName>lastdigits</ColumnName><ColumnName>lastobserver</ColumnName><ColumnName>malicious</ColumnName><ColumnName>observingcall</ColumnName><ColumnName>origlogin</ColumnName><ColumnName>segment</ColumnName><ColumnName>segstart</ColumnName><ColumnName>segstart_utc</ColumnName><ColumnName>segstop</ColumnName><ColumnName>segstop_utc</ColumnName><ColumnName>split1</ColumnName><ColumnName>split2</ColumnName><ColumnName>split3</ColumnName><ColumnName>talktime</ColumnName><ColumnName>tkgrp</ColumnName><ColumnName>transferred</ColumnName><ColumnName>agt_released</ColumnName><ColumnName>ansreason</ColumnName><ColumnName>calling_ii</ColumnName><ColumnName>dispsklevel</ColumnName><ColumnName>origreason</ColumnName><ColumnName>netintime</ColumnName><ColumnName>origholdtime</ColumnName><ColumnName>ucid</ColumnName><ColumnName>anslocid</ColumnName><ColumnName>eqlocid</ColumnName><ColumnName>obslocid</ColumnName><ColumnName>origlocid</ColumnName><ColumnName>cwc1</ColumnName><ColumnName>cwc2</ColumnName><ColumnName>cwc3</ColumnName><ColumnName>cwc4</ColumnName><ColumnName>cwc5</ColumnName><ColumnName>queuetime</ColumnName><ColumnName>ringtime</ColumnName><ColumnName>uui_len</ColumnName><ColumnName>asai_uui</ColumnName><ColumnName>interruptdel</ColumnName><ColumnName>agentsurplus</ColumnName><ColumnName>agentskilllevel</ColumnName><ColumnName>prefskilllevel</ColumnName><ColumnName>icrresent</ColumnName><ColumnName>icrpullreason</ColumnName> NVARCHAR(50))

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Strange. Looks like the STUFF function isn't working properly.
Can you post the query that you are running?
ColoplastAuthor Commented:
Here it is:

DECLARE @strCreate NVARCHAR(MAX)
DECLARE @strColumns NVARCHAR(MAX)

SELECT @strColumns=STUFF(
	(SELECT c.colname AS ColumnName
	FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
	WHERE t.tabname = 'call_rec'   
		AND t.tabtype = 'T'
		and t.tabid = c.tabid 
		
	FOR XML PATH('')),1,1,'')
	
SET @strColumns=REPLACE(@strColumns,',', ' NVARCHAR(50), ')
SET @strCreate = N'CREATE TABLE InformixData (' + @strColumns + ' NVARCHAR(50))'

PRINT @strCreate

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I just missed a comma. Please run this version:
DECLARE @strCreate NVARCHAR(MAX)
DECLARE @strColumns NVARCHAR(MAX)

SELECT @strColumns=STUFF(
	(SELECT ',' + c.colname AS ColumnName
	FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
	WHERE t.tabname = 'call_rec'   
		AND t.tabtype = 'T'
		and t.tabid = c.tabid 
		AND t.tabid >= 100
	FOR XML PATH('')),1,1,'')
	
SET @strColumns=REPLACE(@strColumns,',', ' NVARCHAR(50), ')
SET @strCreate = N'CREATE TABLE TableName (' + @strColumns + ' NVARCHAR(50))'

EXEC sp_executesql @strCreate

Open in new window

ColoplastAuthor Commented:
It's the same error, must be another comma error or somethiing similar.
ColoplastAuthor Commented:
The output  is this:

CREATE TABLE InformixData (ColumnName> NVARCHAR(50), seqnum</ColumnName><ColumnName> NVARCHAR(50), acd</ColumnName><ColumnName> NVARCHAR(50), row_date</ColumnName><ColumnName> NVARCHAR(50), row_time</ColumnName><ColumnName> NVARCHAR(50), acwtime</ColumnName><ColumnName> NVARCHAR(50), ansholdtime</ColumnName><ColumnName> NVARCHAR(50), anslogin</ColumnName><ColumnName> NVARCHAR(50), assist</ColumnName><ColumnName> NVARCHAR(50), audio</ColumnName><ColumnName> NVARCHAR(50), callid</ColumnName><ColumnName> NVARCHAR(50), calling_pty</ColumnName><ColumnName> NVARCHAR(50), conference</ColumnName><ColumnName> NVARCHAR(50), consulttime</ColumnName><ColumnName> NVARCHAR(50), da_queued</ColumnName><ColumnName> NVARCHAR(50), dialed_num</ColumnName><ColumnName> NVARCHAR(50), dispivector</ColumnName><ColumnName> NVARCHAR(50), disposition</ColumnName><ColumnName> NVARCHAR(50), disppriority</ColumnName><ColumnName> NVARCHAR(50), dispsplit</ColumnName><ColumnName> NVARCHAR(50), disptime</ColumnName><ColumnName> NVARCHAR(50), dispvdn</ColumnName><ColumnName> NVARCHAR(50), duration</ColumnName><ColumnName> NVARCHAR(50), eqloc</ColumnName><ColumnName> NVARCHAR(50), event1</ColumnName><ColumnName> NVARCHAR(50), event2</ColumnName><ColumnName> NVARCHAR(50), event3</ColumnName><ColumnName> NVARCHAR(50), event4</ColumnName><ColumnName> NVARCHAR(50), event5</ColumnName><ColumnName> NVARCHAR(50), event6</ColumnName><ColumnName> NVARCHAR(50), event7</ColumnName><ColumnName> NVARCHAR(50), event8</ColumnName><ColumnName> NVARCHAR(50), event9</ColumnName><ColumnName> NVARCHAR(50), firstivector</ColumnName><ColumnName> NVARCHAR(50), firstvdn</ColumnName><ColumnName> NVARCHAR(50), vdn2</ColumnName><ColumnName> NVARCHAR(50), vdn3</ColumnName><ColumnName> NVARCHAR(50), vdn4</ColumnName><ColumnName> NVARCHAR(50), vdn5</ColumnName><ColumnName> NVARCHAR(50), vdn6</ColumnName><ColumnName> NVARCHAR(50), vdn7</ColumnName><ColumnName> NVARCHAR(50), vdn8</ColumnName><ColumnName> NVARCHAR(50), vdn9</ColumnName><ColumnName> NVARCHAR(50), held</ColumnName><ColumnName> NVARCHAR(50), holdabn</ColumnName><ColumnName> NVARCHAR(50), lastcwc</ColumnName><ColumnName> NVARCHAR(50), lastdigits</ColumnName><ColumnName> NVARCHAR(50), lastobserver</ColumnName><ColumnName> NVARCHAR(50), malicious</ColumnName><ColumnName> NVARCHAR(50), observingcall</ColumnName><ColumnName> NVARCHAR(50), origlogin</ColumnName><ColumnName> NVARCHAR(50), segment</ColumnName><ColumnName> NVARCHAR(50), segstart</ColumnName><ColumnName> NVARCHAR(50), segstart_utc</ColumnName><ColumnName> NVARCHAR(50), segstop</ColumnName><ColumnName> NVARCHAR(50), segstop_utc</ColumnName><ColumnName> NVARCHAR(50), split1</ColumnName><ColumnName> NVARCHAR(50), split2</ColumnName><ColumnName> NVARCHAR(50), split3</ColumnName><ColumnName> NVARCHAR(50), talktime</ColumnName><ColumnName> NVARCHAR(50), tkgrp</ColumnName><ColumnName> NVARCHAR(50), transferred</ColumnName><ColumnName> NVARCHAR(50), agt_released</ColumnName><ColumnName> NVARCHAR(50), ansreason</ColumnName><ColumnName> NVARCHAR(50), calling_ii</ColumnName><ColumnName> NVARCHAR(50), dispsklevel</ColumnName><ColumnName> NVARCHAR(50), origreason</ColumnName><ColumnName> NVARCHAR(50), netintime</ColumnName><ColumnName> NVARCHAR(50), origholdtime</ColumnName><ColumnName> NVARCHAR(50), ucid</ColumnName><ColumnName> NVARCHAR(50), anslocid</ColumnName><ColumnName> NVARCHAR(50), eqlocid</ColumnName><ColumnName> NVARCHAR(50), obslocid</ColumnName><ColumnName> NVARCHAR(50), origlocid</ColumnName><ColumnName> NVARCHAR(50), cwc1</ColumnName><ColumnName> NVARCHAR(50), cwc2</ColumnName><ColumnName> NVARCHAR(50), cwc3</ColumnName><ColumnName> NVARCHAR(50), cwc4</ColumnName><ColumnName> NVARCHAR(50), cwc5</ColumnName><ColumnName> NVARCHAR(50), queuetime</ColumnName><ColumnName> NVARCHAR(50), ringtime</ColumnName><ColumnName> NVARCHAR(50), uui_len</ColumnName><ColumnName> NVARCHAR(50), asai_uui</ColumnName><ColumnName> NVARCHAR(50), interruptdel</ColumnName><ColumnName> NVARCHAR(50), 

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
I re tested my version and didn't get any error.
Please review your query.
ColoplastAuthor Commented:
Is the output I posted OK?
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, output isn't ok.
Can you post what the following select returns?
SELECT ',' + c.colname AS ColumnName
	FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
	WHERE t.tabname = 'call_rec'   
		AND t.tabtype = 'T'
		and t.tabid = c.tabid

Open in new window

ColoplastAuthor Commented:
,seqnum
,acd
,row_date
,row_time
,acwtime
,ansholdtime
,anslogin
,assist
,audio
,callid
,calling_pty
,conference
,consulttime
,da_queued
,dialed_num
,dispivector
,disposition
,disppriority
,dispsplit
,disptime
,dispvdn
,duration
,eqloc
,event1
,event2
,event3
,event4
,event5
,event6
,event7
,event8
,event9
,firstivector
,firstvdn
,vdn2
,vdn3
,vdn4
,vdn5
,vdn6
,vdn7
,vdn8
,vdn9
,held
,holdabn
,lastcwc
,lastdigits
,lastobserver
,malicious
,observingcall
,origlogin
,segment
,segstart
,segstart_utc
,segstop
,segstop_utc
,split1
,split2
,split3
,talktime
,tkgrp
,transferred
,agt_released
,ansreason
,calling_ii
,dispsklevel
,origreason
,netintime
,origholdtime
,ucid
,anslocid
,eqlocid
,obslocid
,origlocid
,cwc1
,cwc2
,cwc3
,cwc4
,cwc5
,queuetime
,ringtime
,uui_len
,asai_uui
,interruptdel
,agentsurplus
,agentskilllevel
,prefskilllevel
,icrresent
,icrpullreason

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Now with XML, how it looks like?
SELECT STUFF(
	(SELECT ',' + c.colname AS ColumnName
	FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
	WHERE t.tabname = 'call_rec'   
		AND t.tabtype = 'T'
		and t.tabid = c.tabid 
	FOR XML PATH('')),1,1,'')

Open in new window

ColoplastAuthor Commented:
Output:

ColumnName>,seqnum</ColumnName><ColumnName>,acd</ColumnName><ColumnName>,row_date</ColumnName><ColumnName>,row_time</ColumnName><ColumnName>,acwtime</ColumnName><ColumnName>,ansholdtime</ColumnName><ColumnName>,anslogin</ColumnName><ColumnName>,assist</ColumnName><ColumnName>,audio</ColumnName><ColumnName>,callid</ColumnName><ColumnName>,calling_pty</ColumnName><ColumnName>,conference</ColumnName><ColumnName>,consulttime</ColumnName><ColumnName>,da_queued</ColumnName><ColumnName>,dialed_num</ColumnName><ColumnName>,dispivector</ColumnName><ColumnName>,disposition</ColumnName><ColumnName>,disppriority</ColumnName><ColumnName>,dispsplit</ColumnName><ColumnName>,disptime</ColumnName><ColumnName>,dispvdn</ColumnName><ColumnName>,duration</ColumnName><ColumnName>,eqloc</ColumnName><ColumnName>,event1</ColumnName><ColumnName>,event2</ColumnName><ColumnName>,event3</ColumnName><ColumnName>,event4</ColumnName><ColumnName>,event5</ColumnName><ColumnName>,event6</ColumnName><ColumnName>,event7</ColumnName><ColumnName>,event8</ColumnName><ColumnName>,event9</ColumnName><ColumnName>,firstivector</ColumnName><ColumnName>,firstvdn</ColumnName><ColumnName>,vdn2</ColumnName><ColumnName>,vdn3</ColumnName><ColumnName>,vdn4</ColumnName><ColumnName>,vdn5</ColumnName><ColumnName>,vdn6</ColumnName><ColumnName>,vdn7</ColumnName><ColumnName>,vdn8</ColumnName><ColumnName>,vdn9</ColumnName><ColumnName>,held</ColumnName><ColumnName>,holdabn</ColumnName><ColumnName>,lastcwc</ColumnName><ColumnName>,lastdigits</ColumnName><ColumnName>,lastobserver</ColumnName><ColumnName>,malicious</ColumnName><ColumnName>,observingcall</ColumnName><ColumnName>,origlogin</ColumnName><ColumnName>,segment</ColumnName><ColumnName>,segstart</ColumnName><ColumnName>,segstart_utc</ColumnName><ColumnName>,segstop</ColumnName><ColumnName>,segstop_utc</ColumnName><ColumnName>,split1</ColumnName><ColumnName>,split2</ColumnName><ColumnName>,split3</ColumnName><ColumnName>,talktime</ColumnName><ColumnName>,tkgrp</ColumnName><ColumnName>,transferred</ColumnName><ColumnName>,agt_released</ColumnName><ColumnName>,ansreason</ColumnName><ColumnName>,calling_ii</ColumnName><ColumnName>,dispsklevel</ColumnName><ColumnName>,origreason</ColumnName><ColumnName>,netintime</ColumnName><ColumnName>,origholdtime</ColumnName><ColumnName>,ucid</ColumnName><ColumnName>,anslocid</ColumnName><ColumnName>,eqlocid</ColumnName><ColumnName>,obslocid</ColumnName><ColumnName>,origlocid</ColumnName><ColumnName>,cwc1</ColumnName><ColumnName>,cwc2</ColumnName><ColumnName>,cwc3</ColumnName><ColumnName>,cwc4</ColumnName><ColumnName>,cwc5</ColumnName><ColumnName>,queuetime</ColumnName><ColumnName>,ringtime</ColumnName><ColumnName>,uui_len</ColumnName><ColumnName>,asai_uui</ColumnName><ColumnName>,interruptdel</ColumnName><ColumnName>,agentsurplus</ColumnName><ColumnName>,agentskilllevel</ColumnName><ColumnName>,prefskilllevel</ColumnName><ColumnName>,icrresent</ColumnName><ColumnName>,icrpullreason</ColumnName>

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, here's the difference from me. I'm using SQL Server 2008R2. Which version are you using?
ColoplastAuthor Commented:
SQL 2014
Vitor MontalvãoMSSQL Senior EngineerCommented:
Never mind. I saw where's the error. Do not use alias name for the column name (I didn't).
Please try this one:
DECLARE @strCreate NVARCHAR(MAX)
DECLARE @strColumns NVARCHAR(MAX)

SELECT @strColumns=STUFF(
	(SELECT ',' + c.colname 
	FROM [CMCPCMS02].[cms].informix.systables AS t, [CMCPCMS02].[cms].informix.syscolumns AS c 
	WHERE t.tabname = 'call_rec'   
		AND t.tabtype = 'T'
		and t.tabid = c.tabid 
	FOR XML PATH('')),1,1,'')
	
SET @strColumns=REPLACE(@strColumns,',', ' NVARCHAR(50), ')
SET @strCreate = N'CREATE TABLE InformixData (' + @strColumns + ' NVARCHAR(50))'

PRINT @strCreate

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ColoplastAuthor Commented:
Much better :-)
Thanks a lot Vitor.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.