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

avatar image

Debasish
58 4 6 9

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

avatar image

Tom Staab ♦
14.5k 7 14 18

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

avatar image

user-806
181 20 23 27

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

plz give some advice

more ▼

answered Mar 11, 2010 at 08:23 AM

avatar image

user-806
181 20 23 27

(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.

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:

x1140

asked: Mar 10, 2010 at 06:13 AM

Seen: 1170 times

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

Copyright 2016 Redgate Software. Privacy Policy