Create a table using script

Question

Post reply

Create a table using script

vinodhkumargv

SSC Enthusiast

Points: 147
More actions
April 11, 2014 at 3:39 am

#306550

Hi,
I have a create table script as .txt file. How can i create table using that script through sql query. Plz any one suggest some approach. I heard through openrow set it can be achieved. But how to do.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQLCJ SSCommitted Points: 1791 More actions · Answer 1

I tried it by writting below code

CREATE TABLE #TEMP

(

SCRIPT VARCHAR(4000)

)

INSERT INTO #TEMP

EXEC XP_CMDSHELL 'type filefath\filename.txt'

DECLARE @sql VARCHAR(8000)

SELECT @sql= STUFF((

SELECT CONVERT(VARCHAR(1000),' '+ SCRIPT) FROM #TEMP

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

EXEC (@SQL)

DROP TABLE #temp

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Why try to open it through a SQL query? Why not just use a command line utility like PowerShell or sqlcmd.exe? Either of those can read the file and execute it against a database. That's much more efficient than trying to open files through T-SQL.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLCJ SSCommitted Points: 1791 More actions · Answer 3

ahhh :crazy:... Indeed... Silly me ... Please try doing the way Grant has proposed...

vinodhkumargv SSC Enthusiast Points: 147 More actions · Answer 4

Thanks for your reply is there any way to achieve this using open row set

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

I'm not sure. It's not something I would try to do. What's the purpose of this approach?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

free_mascot One Orange Chip Points: 27169 More actions · Answer 6

I guess vinodhkumargv is looking for simple solution and could be...

Copy .txt code and past it to the new query windows and execute it. It will create the table.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQLCJ SSCommitted Points: 1791 More actions · Answer 7

SQLCJ

SSCommitted

Points: 1791

April 11, 2014 at 6:42 am

#1705243

:-D:-):Wow: