Here is an interesting problem which I enjoyed solving yesterday.
There were multiple SQL Files in my one folder. When I had to send it to my friend I had to collect all the files into a folder and zip it to send it via email. My friend who was using mobile device told me that it is difficult to see the content of the file on a mobile device so it would be nice if I can just send all the SQL files combined in one single file and send it to him.
I loved the idea, but the challenge was that there are over 100s of the files were there and combining them into a single file would be a manual task for hours. I did not like the solution and I went online to find some cool solution. After a while I found a solution that I can use wiht command prompt to combine multiple files into a single file and it is easy to do so.
Here is an example. Let us create three SQL Files.
Now we will execute the following command in command prompt which will combine all the three files into a single file.
The command is
type *.sql > OneFile.sql
Above command will combine all the three files into a single called OneFile.sql.
Following image displays the content of the OneFile.sql which is the concatenation of all the sql files in the folder.
I enjoyed solving this little problem.
Reference: Pinal Dave (https://blog.sqlauthority.com)
36 Comments. Leave new
Nice article pinal.
Nice.!
Hello, I ran the same test you did but got duplicate records. — First File
SELECT 1
GO– Second File
SELECT 2
GO– Three File
SELECT 3
GO– First File
SELECT 1
GO– Second File
SELECT 2
GO– Three File
SELECT 3
GO
E:\test\type> type *.sql > e:\test\OneFile.sql
Nice article sir ji
Thanks @Altaf
Hi Pinal, I have multiple SQL files in folder and sub folders. how do I combine them?
NeerajM – you can give all the paths in the command
type e:Folder1*.sql e:Folder1Folder2*.sql > e:Folder1final.sql
Hi Pinel, do you know how combine when the script have accentuation, my DB has portuguese language, I use type, but don´t usefull.
hi,
i want get the recode top 5 statename and top 5 districtName …?
Arun – it would be helpful if you can share sample DDL, dummy data and expected output.
Hey Pinal,
great solutions – thanks for sharing!
I was wondering if I could insert a “UNION” after each query as well, so I could build one large query consisting of multiple smaller ones which I can keep in seperate files?
Best, Vincent
Hi Pinal,
Thanks a lot. This was very helpful…
Welcome!
Great article!
Hi, how to do the opposite. i mean splitting and saving to different files
but it resulted into duplicate copy of contents as contents of OneFile is also copied.
You are the one!!!
To everybody who get´s some sort of duplicates out of the batch file listed above:
I have not the deeper knowledge to explain the specified problem, but my gues is that it might be a problem that the created output file is a *.sql file and is created while still the batch still searches for *.sql files.
That beeing said the solution is quite simple:
– create *.txt file
– rename this txt file to *.sql file
EXAMPLE:
type *.sql > combined.txt
ren combined.txt combined.sql
Thank you very much :)
Dave I run this command and it truncates part of my sql scripts (at about 11000 lines or so). Do you know of any limitations regarding this?
You can try using
copy *.sql AllSql.sql
This does not truncate the final sql file.
I have 4 .sql files and i am trying to create using type *.sql>filename.sql but it is not create proper output file.
output file is automatically truncated data after 368 lines.
same way i have tried with 6 files but same thing happens output file is truncated data after 886 line.
So, does this allow me to run all of the collective scripts on one sql query?
hey man i got a not found
I tried 16 file each one abverage 300 lines but some file content missing
Please check the small files encoding, if the files are not unicode then there is a chance of content missing