Make Table Query

Question

Post reply

Make Table Query

water490

SSC Eights!

Points: 959
More actions
February 15, 2022 at 1:08 am

#3983633

Hi
I have table that I want to make sub tables for. Below is an example of how it would work.
Consider tableMain
header1   header2
value1      10
value1    20
value2    5
value3   10
There would be three new tables - table1, table2, table3 as follows:
table1
header 1    header 2
value1      10
value1    20
table2
header 1    header 2
value2    5
table3
header 1    header 2
value3   10
Is it possible to do this in SQL Server 2019?   If yes, how? In addition to the logic noted, it also needs to have a unique name.
It is preferred that the output be written to a CSV file vs SQL Server tables because of the volume of records. There would be close to 8000 sub tables from the main table so I do not want to store them in SQL Server.
This process is part of SSIS package where I output results from SQL Server into another program and that program needs the files to be in CSVs. That program has a limit of 65K records per CSV so I cannot use the main table as is. I have to break it up into smaller tables so I decided on the algorithm above (by different header1 values).
Thank you

Viewing 10 posts - 1 through 9 (of 9 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

use BCP OUT with a parameterized stored procedure to get the data you want?

See Example G on this page: bcp Utility - SQL Server | Microsoft Docs

water490 SSC Eights! Points: 959 More actions · Answer 2

that is looking promising. They give an example:

bcp "SELECT FullName, PreferredName FROM WideWorldImporters.Application.People ORDER BY FullName" queryout D:\BCP\People.txt -t, -c -T

Can the file name "D:\BCP\people.txt" have a variable in it? So that way each file that gets created has the same name as the parameter that was fed into the parameterized stored procedure.

water490 SSC Eights! Points: 959 More actions · Answer 3

I have another question. In my example I used header1 values to break up the main table into smaller tables. If I use a parameterized stored procedure to process it for one input. There are over 8000 values so I need an automated way to feed the 8000 values into the parameterized stored procedure. How would I do this? The only way I can think of is C# but I was really hoping to avoid C# and just use SQL Server someway to do the partition.

ScottPletcher SSC Guru Points: 101105 More actions · Answer 4

For efficiency, create a clustering key on the table on ( header1, header2 ). Since SQL Server so strongly prefers unique indexes, if those values aren't unique by themselves, add a $IDENTITY column as the last key (NOT the first (only) key) to make the index UNIQUE.

Next, you can use a query to find all the DISTINCT header1 values in the table.

Finally, loop thru those unique values to to write out each file.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Here's a post from Erland Sommarskog...

passing parameter to stored procedure in bcp command

then all you'd have to do is loop over the list of values to pass into the stored procedure to create each file, so forward only or fast forward cursor for that part, and call the stored procedure that does the BCP inside it.

water490 SSC Eights! Points: 959 More actions · Answer 6

pietlinden wrote:

Here's a post from Erland Sommarskog... passing parameter to stored procedure in bcp command
then all you'd have to do is loop over the list of values to pass into the stored procedure to create each file, so forward only or fast forward cursor for that part, and call the stored procedure that does the BCP inside it.

There is no link. Can you please post the link?

water490 SSC Eights! Points: 959 More actions · Answer 7

I found this article online that looks like it does looping through column values. Is this what I need to do? I would replace the runningtotal with the bcp command

https://www.mssqltips.com/sqlservertip/6148/sql-server-loop-through-table-rows-without-cursor/

Just want to make sure I am on the right track before I begin the development work.

water490 SSC Eights! Points: 959 More actions · Answer 8

water490

SSC Eights!

Points: 959

February 18, 2022 at 12:38 am

#3985179

Anyone able to help me with this?

pietlinden SSC Guru Points: 63503 More actions · Answer 9

Here's one post by Erland answering a similar question.

Then you'd have to do something like create a cursor (I think) to pass the values from

SELECT DISTINCT header1 FROM tableMain ORDER BY header1

to the bcp command. (maybe you have to create a string variable, use REPLACE() to update the dummy value with the value from header1, and then sp_executesql?)