x

how to split data

I have some data in following structure.I want to split the data such a way where ac_no wise database will create & part_no wise table will be created on respective ac_no.

AC_NO PART_NO SLNOINPART SECTION_NO
5 1 660 1
5 1 662 1
5 2 663 1
5 3 664 1
5 4 666 1
5 2 668 1
5 1 669 1
5 3 670 1
5 4 671 1
5 1 672 1
5 2 674 1
5 3 675 1
5 4 676 1
6 1 677 1
6 1 678 1
6 2 679 1
6 2 680 1
6 33 681 1
6 5 682 1
6 6 683 1
6 3 684 1
6 8 685 1
6 1 687 1
6 2 688 1
6 1 689 1
6 3 690 1
6 1 691 1
6 1 693 1
6 1 694 1
6 1 696 1

how can i do this in ssis

more ▼

asked Mar 10, 2010 at 06:13 AM in Default

Debasish gravatar image

Debasish
58 4 4 5

Not enough detail, Debashis. What do you want in the database? Just the Part_No table? What do you want the Part_No table to look like so far as datatypes for each column and what do you want for indexes? Considering that the databases are based on AC_No, what do you want the database name to look like?

Last but not least, why do you want a separate database for each AC_NO especially since it will only have one table in each?
Mar 10, 2010 at 10:57 AM Jeff Moden
i want database name as AC001 IF AC_NO IS 1 & TABLE NAME AS AC001PART01 IF PART NO IS 1
Mar 11, 2010 at 05:50 AM Debasish
I updated my answer to include everything you need.
Mar 11, 2010 at 10:07 PM Tom Staab
I modified my code to order the incoming data. That will guarantee databases and tables are created as needed, and all data for a given table is inserted immediately after the table is created.
Mar 12, 2010 at 10:23 AM Tom Staab
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I think will be better solved with dynamic SQL. Granted, you could run the SQL inside SSIS using an Execute SQL task. I have written two potential solutions to your question, but neither is tested. If you have problems, please respond with comments.

You won't need this, but here's the SQL I used to create a table variable with the sample data you provided:

DECLARE @originaltable TABLE
(
    AC_NO int, PART_NO int, SLNOINPART int, SECTION_NO int
);
INSERT INTO @originaltable
    SELECT 5, 1, 660, 1
    UNION
    SELECT 5, 1, 662, 1
    UNION
    SELECT 5, 2, 663, 1
    UNION
    SELECT 5, 3, 664, 1
    UNION
    SELECT 5, 4, 666, 1
    UNION
    SELECT 5, 2, 668, 1
    UNION
    SELECT 5, 1, 669, 1
    UNION
    SELECT 5, 3, 670, 1
    UNION
    SELECT 5, 4, 671, 1
    UNION
    SELECT 5, 1, 672, 1
    UNION
    SELECT 5, 2, 674, 1
    UNION
    SELECT 5, 3, 675, 1
    UNION
    SELECT 5, 4, 676, 1
    UNION
    SELECT 6, 1, 677, 1
    UNION
    SELECT 6, 1, 678, 1
    UNION
    SELECT 6, 2, 679, 1
    UNION
    SELECT 6, 2, 680, 1
    UNION
    SELECT 6, 33, 681, 1
    UNION
    SELECT 6, 5, 682, 1
    UNION
    SELECT 6, 6, 683, 1
    UNION
    SELECT 6, 3, 684, 1
    UNION
    SELECT 6, 8, 685, 1
    UNION
    SELECT 6, 1, 687, 1
    UNION
    SELECT 6, 2, 688, 1
    UNION
    SELECT 6, 1, 689, 1
    UNION
    SELECT 6, 3, 690, 1
    UNION
    SELECT 6, 1, 691, 1
    UNION
    SELECT 6, 1, 693, 1
    UNION
    SELECT 6, 1, 694, 1
    UNION
    SELECT 6, 1, 696, 1
;

Here's the code you will need to do what you want. Just change @originaltable to the name of the current table or view that has your data.

DECLARE @acNo int; SET @acNo = 0;
DECLARE @partNo int; SET @partNo = 0;
DECLARE @AC_NO int, @PART_NO int, @SLNOINPART int, @SECTION_NO int;
DECLARE @sql nvarchar(max);
DECLARE @NewLine nchar(2); SET @NewLine = NCHAR(13) + NCHAR(10);

DECLARE abc CURSOR FOR
    SELECT AC_NO, PART_NO, SLNOINPART, SECTION_NO
    FROM @originaltable
    ORDER BY AC_NO, PART_NO;

OPEN abc;
FETCH NEXT FROM abc
INTO @AC_NO, @PART_NO, @SLNOINPART, @SECTION_NO;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF @acNo != @AC_NO
    BEGIN
        -- dynamic SQL to create database
        SET @sql = N'CREATE DATABASE AC' + RIGHT('000' + CAST(@AC_NO AS nvarchar(3)), 3);
        PRINT @sql;
        EXEC sp_executesql @sql;
        SET @acNo = @AC_NO;
        SET @partNo = 0;
    END
    IF @partNo != @PART_NO
    BEGIN
        -- dynamic SQL to create table
        SET @sql = N'USE AC' + RIGHT('000' + CAST(@AC_NO AS nvarchar(3)), 3) + N';' + @NewLine +
            N'CREATE TABLE dbo.AC' + RIGHT('000' + CAST(@AC_NO AS nvarchar(3)), 3) +
                N'PART' + RIGHT('00' + CAST(@PART_NO AS nvarchar(2)), 2) + @NewLine +
            N'  (SLNOINPART int, SECTION_NO tinyint);';
        PRINT @sql;
        EXEC sp_executesql @sql;
        SET @partNo = @PART_NO;
    END
    -- dynamic SQL to insert values into table  
    SET @sql = N'INSERT INTO AC' + RIGHT('000' + CAST(@AC_NO AS nvarchar(3)), 3) +
        N'.dbo.AC' + RIGHT('000' + CAST(@AC_NO AS nvarchar(3)), 3) +
        N'PART' + RIGHT('00' + CAST(@PART_NO AS nvarchar(2)), 2) + @NewLine +
        N'  (SLNOINPART, SECTION_NO)' + @NewLine +
        N'  VALUES(' + CAST(@SLNOINPART AS nvarchar(5)) +
        N', ' + CAST(@SECTION_NO AS nvarchar(5)) + N');';
    PRINT @sql;
    EXEC sp_executesql @sql;

    FETCH NEXT FROM abc
    INTO @AC_NO, @PART_NO, @SLNOINPART, @SECTION_NO;
END
CLOSE abc;
DEALLOCATE abc;

If you really want to use an SSIS Data Flow, you could do this with a script task inside the data flow. In that case, I would override the Input0_ProcessInput method instead of using Input0_ProcessInputRow. Here's a partial example:

public override void Input0_ProcessInput(Input0Buffer Buffer)
{
    int acNo int = 0;
    int partNo int = 0;

    while (Buffer.NextRow())
    {
        if (acNo != Buffer.ac_no)
        {
            //create new database
            partNo = 0;
        }
        if (partNo != Buffer.part_no)
        {
            //create new table
        }
        //populate table
    }
}

You'd still need to execute dynamic SQL for the database and table creation.

more ▼

answered Mar 10, 2010 at 08:23 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

thanks for ur pely.

But if table is already exist.it is not goinf for next database or table creation
Mar 12, 2010 at 06:00 AM Debasish
Tom, I'd upvote you twice if I could for the amount of effort you've put in here
Mar 12, 2010 at 10:23 AM Matt Whitfield ♦♦
Thanks, Matt. I guess it was one of those problems I just really wanted to solve. :)
Mar 12, 2010 at 10:47 AM Tom Staab
(comments are locked)
10|1200 characters needed characters left

hi toma,

thanks for your immediate reply.

i am not able to understand where you mention dynamic sql to create database.

what i have to write there?actually i have 294 ac_no.

Plz help me. show mw one example.

more ▼

answered Mar 10, 2010 at 08:41 AM

user-806 gravatar image

user-806
181 17 19 22

(comments are locked)
10|1200 characters needed characters left

plz give some advice

more ▼

answered Mar 11, 2010 at 08:23 AM

user-806 gravatar image

user-806
181 17 19 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927

asked: Mar 10, 2010 at 06:13 AM

Seen: 904 times

Last Updated: Mar 10, 2010 at 06:19 AM