Execute .sql files to create table schemes

Question

Execute .sql files to create table schemes

MTY-1082557

SSCarpal Tunnel

Points: 4540
More actions
July 2, 2010 at 8:44 am

#226407

I have around 50 .sql files, each file contain the scheme of each table.
i need to create those 50 tables in other database, but I dont want to open each file to execute it and create each table.
Is there a way to run all the .sql files at the same time and create the 50 tables at once??
Regards,

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

from where? via TSQL in SSMS,SSIS, or command like via sqlcmd or maybe even an application like vb/vb.NET?

TSQL is a little hard to do, because of security issues....would the account running SQL Server have access to the folder of files?

one way i think would work would be to create a linked server to the folder, but that would only show .txt or .csv file names....if they are .sql they would not show up...then you could bulk insert them into a table andy use dynamic SQL to execute them.

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!

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 2

it can be eiter TSQL, SSMS, SQLCMD...

I have to create those tables locally, the files are already located on the local machine that i need.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

I would use powershell to loop through each file in a directory and pass the file to sqlcmd if I had to use those scripts.

If I could use an existing copy of the database, I would use SQL Compare from Redgate. I would create the new blank database, compare the blank database with the existing copy and synchronize the 50 selected tables/objects.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ram:) SSCrazy Points: 2246 More actions · Answer 4

With similar issue when I had but one SP to all databases, I used the following script.. I don't know whether it will be useful to you or not.

Any mistakes plese correct me..

But I feel it is not secure to use.. Because we have to give login name and password here

set nocount on

declare @sql varchar(100)

declare @a varchar(100)

declare @sql1 varchar(600)

declare cur cursor for select name from master..sysdatabases where name not in ('master','msdb','tempdb','model','northwind','pubs')

open cur

fetch next from cur into @a

while @@fetch_status =0

begin

set @sql1 = 'if exists(select name from '+@a+'.dbo.sysobjects where name='+char(39)+'sp_bill'+char(39)+' and xtype='+char(39)+'p'+char(39)+') use '+@a+' drop proc sp_bill'

--print @sql1

exec (@sql1)

print 'Stored procedure(s) created in database '+upper(@a)

set @sql = 'exec master..xp_cmdshell"osql -T -U sa -P sqlpw -d '+@a+' -i d:ew.sql -n"'

exec (@sql)

fetch next from cur into @a

end

close cur

deallocate cur

🙂