can anyone help to explain how to insert multiple record into one/two table?
ex:lets say when user specify start date and end date, then we need to created and insert the record on that duration.
and how to do with insert the record weekly or monthly?until the end of the date!
I am trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the IF statement) but i need it to create multiple new records based on the IF statement (or something similar) i have in the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code.
the idea is, the hierachy structure in the tables are:
Location1
--> Part1
--> part2 etc
Location2
--> part1 etc
i want it so that when i input a new part it updates to all the locations. The locations all have the same parentguid (the one specified in the code)
With regards to the stored procedure it executes, this stored procedure creates the entry. I don't really want to change the stored procedure because other functions reference and use it.
My code:
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID2 VARCHAR(50)
select @parentguid2 = parentguid from dsdba.itemgroups
if @PARENTGUID2 = '8CF850AD-2026-411B-AABE-BF1584624EB3'
BEGIN
SET @PREFIX = 'JUST'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX
SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1
END
:confused:
Hi and thanks for any advice.Right now I have a for loop that inserts multiple records. The first record is inserted into the database and I am not sure why. Here is the code I am using - Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"
Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5
MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount
Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close() Below is the whole procedure which either uploads an image or directory of images and resizes them. Then gets the meta data from the image. Then creates an entry into the database for each image. If I am dealing with 4 images then 4 images are uploaded to the new gallery folder. But 5 entries are added to the database. Thanks again for any help, Jennifer Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
If ListDirectories.SelectedValue.Length > 0 Or fileUpEx.HasFile Then
Dim i As Integer
Dim selectedDirectory As String = ListDirectories.SelectedValue
Dim photoDescription As String
Dim photoAuthor As String
Dim photoTitle As String = ""
Dim photoName As String
Dim myFiles As String()
Dim fileCount As Integer
If multiUpload.Visible = True Then
myFiles = Directory.GetFiles(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/")
fileCount = myFiles.Length - 1
Else
fileCount = 0
End If
'Get last photo order from Photo table
Dim PhotoMaxOrder As Integer = 0
Dim DS As DataSet ' DataSet object
Dim SQL As String = "SELECT MAX(PhotoOrder) FROM Photos WHERE GalleryID=" & Request("ID").Trim
Dim connString As String = ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString
Dim sqlDA = New SqlDataAdapter(SQL, connString)
DS = New DataSet
sqlDA.Fill(DS, "Photos")
If Not DS.Tables("Photos").Rows(0).Item(0) Is System.DBNull.Value Then
If DS.Tables("Photos").Rows.Count > 0 Then
PhotoMaxOrder = Convert.ToInt32(DS.Tables("Photos").Rows(0).Item(0)) + 1
End If
Else
PhotoMaxOrder = 1
End If
For i = 0 To fileCount
If multiUpload.Visible = True Then
photoName = Right(myFiles(i), InStr(StrReverse(myFiles(i)), "/") - 1)
Else
photoName = fileUpEx.PostedFile.FileName
End If
If InStr(photoName, ".jpg") > 0 Then
Dim MyPhoto As Bitmap
If multiUpload.Visible = True Then
MyPhoto = New Bitmap(myFiles(i))
Else
MyPhoto = Bitmap.FromStream(fileUpEx.PostedFile.InputStream)
End If
'testFile = myFiles(i)
Try 'Get photo description
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("270")
Dim ascii As Encoding = Encoding.ASCII
photoDescription = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoDescription = ""
End Try
Try 'Get photo author
Dim Make As PropertyItem = MyPhoto.GetPropertyItem("315")
Dim ascii As Encoding = Encoding.ASCII
photoAuthor = ascii.GetString(Make.Value, 0, Make.Len - 1)
Catch ex As Exception
photoAuthor = ""
End Try
Dim photoXmpData As String = GetXmpXmlDocFromImageStream(MyPhoto)
If Not photoXmpData = "" Then
photoTitle = GetXmpXmlNode(photoXmpData)
End If
'insert photo record into photo table
Dim intPhotoKeyID As Integer
Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc "
InsertCmd += ") values " & _
"(@OriginalName, @GalleryID, @PhotoDesc " & _
") "
InsertCmd += "; SELECT CAST(scope_identity() AS int);"
Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString)
Dim MyCommand = New SqlCommand(InsertCmd, DBConnection)
MyCommand.Connection.Open()
MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150))
MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int))
MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text))
myCount = myCount & i 'Is zero for first two records
MyCommand.Parameters("@OriginalName").Value = "Orig" & i
MyCommand.Parameters("@GalleryID").Value = 5
MyCommand.Parameters("@PhotoDesc").Value = MyCommand.CommandText & myCount
Try
intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar())
MyCommand.Connection.Close()
Catch Exp As Exception
Response.Write(Exp)
'ResultsLabel.Text = Exp.ToString()
End Try
DBConnection.Close()
'check photo width and height
Dim NewFilePath As String = Server.MapPath("/appscode/galleries/photos/g" & Request("ID").Trim & "/") & "p" & intPhotoKeyID.ToString & ".jpg" ' & photoName
lblMessage2.Text = lblMessage2.Text & InsertCmd
If MyPhoto.Width.ToString = "200" Or MyPhoto.Height.ToString = "200" Then
'just copy the image
If multiUpload.Visible = True Then
File.Copy(myFiles(i), NewFilePath, True)
Else
fileUpEx.SaveAs(NewFilePath)
End If
Else
'resize image
Dim NewSize As System.Drawing.Size = New System.Drawing.Size(200, 200)
ResizePicture(MyPhoto, NewFilePath, NewSize) 'and save it
End If
MyPhoto.Dispose()
GC.Collect()
End If
Next
If Not myFiles Is Nothing Then
If myFiles.Length > 0 And multiUpload.Visible = True Then
'delete directory that was just processed
Directory.SetCurrentDirectory(Server.MapPath("."))
Directory.Delete(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/", True)
ListDirectories.Items.Remove(selectedDirectory)
'lblMessage2.Text = "Your file(s) have been added."
End If
Else
'lblMessage3.Text = "Your file has been added."
End If
Else
lblMessage2.Text = "Please select a folder."
End If
lblMessage3.Text = myCount
End Sub
Hello Everyone,
I am bit confused.
I am using vwd2005 express,c# and sql express.
I have a webform that registers new employee(title,name,age,address,phone,email) and inserts those data into the sqldatabase.
The problem is there might exists 'N' number or email ids and phone nos for a single user.
for eg: user A might have 3 email address where has user B might have 5 email address.
so in that case its not appropriate to create 5 textboxes in the webform and create 5 column like email1,email2,email3,email4,email5 in the database for both users..
i hope your getting the point.
I tried to create a separate table for emails and phone.
But i am new to relational database.
So if i need to use Relational database then could anyone help me here to :-
1.create a table structure here.(what would be the structure of tbl_employee, tbl_email and tbl_phone)
2.How to write insert query if i am using RD here?
plz help explaining the concept with a simple running example.
Thanks in advance.
Jack.
Hello,
I have problem for insert multiple query for insert in differenr tabels for a single record.
I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport,
i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then...........
so plz help me
Thanks
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies View RelatedHi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
Hi,
How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.
thanks.
Hi All,
I am trying to create package something like that..
1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I will be appreciated if you can help me...
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies View RelatedI need to be able to bulk insert a bunch of tables from their corresponding flat file. I have created an XML file (see below) which has the file name/table name pair at each node. I then created a ForEachLoop task and used the Node enumeration type and the following OuterXpathString: ReferenceFiles/File. At this point I get lost. How do I pass the 2 inside node values (file name and table name) to variables which I can then use as expressions for the bulk insert task inside the Foreach?
Here is XML file:
Code Snippet
<ReferenceFiles>
<File>
<FileName>Ref_Categories.txt</FileName>
<TableName>Ref_Categories</TableName>
</File>
<File>
<FileName>Ref_Configs.txt</FileName>
<TableName>Ref_Configs</TableName>
</File>
</ReferenceFiles>
Thanks.
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
SOURCE TABLE
ID SEQ COM VARCHAR(10)
1 1 I
1 2 am
1 3 doctor
2 1 I
2 2 am nurse
DESTINATION TABLE --Each comm field is varchar(20)
ID_COM1___COM2______COM3
1__I______am_________doctor
2__I______am nurse____NULL
Please Help
I have two table and there are releationsheep between them.
Topic:
topicId, topicName, topicDate
Reply:
replyId,topicId,replyName,replyDate
I want to insert two table at the same time.
Topic: 1,example,10/21/2007
Reply:1,1,example, 10/21/2007
I have following situation -
DECLARE @table1 TABLE
(
Stuid INT NOT NULL,
schoolcd varchar(10),
enterDate Datetime,
entercd char(10)
)
INSERT @Student VALUES
(4648, ‘20130515’, ‘892’,’30’),
(4648, ‘20130103’, ‘764,’10’)
SELECT STATEMENT will give me following -
Stuid schoolcd entercd enterDate
46488923005/15/2013
46487641001/03/2013
Desired output is one row for two or more records for same stuid
464889230 05/15/20137641001/03/2013xxxxxxx/xx/xxxx
How can I accomplish that?
Ok this is a simple question but i cant seem to find out how since i had the access of a query builder for web matrix.I have the following:A Table called Customer, with Cust_Name, Cust_phoneI have modified my CreateUserWizard to accomodate mroe text boxes which would finally store to vales for Cust_Name, Cust_phone.Question is how can i perform an INSERT query? or rather i noticed that the query builder doesnt allow me to, instead the query builder is towards inserting new records thur GridView and FormView.I've designed a query to handle wildcards i.e @Cust_Nameso i'm thinking if i can have a working insert statment i can do something liketbCust_name.txt = @Cust_Namethen finally perform the insert queryAny ideas? thanks
View 2 Replies View RelatedHi,
I would like to know is it possible to combine these 2 statements into 1, as I want to insert a single record set in tblRefLockers.
statement 1:
INSERT INTO tblRefLockers (Locker_No)
SELECT Locker_No from tblLockersInfo
WHERE MemberNo = 1234
statment 2:
UPDATE tblRefLockers
SET Locker_Condition = 'BAD'
WHERE MemberNo = 1234
Can I do that?
Thanks,
KNVB
Hi there,
I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.
Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?
User tableUserid Username
1 Bob2 Luke3 John
Store procedure resultCompanyname Username
Acrm Corp NullMaxwellarm Ltd Null
Regards,
It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.
Currently I'll do something like this:
DECLARE @var1 intSET @var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @somekey)DECLARE @var2 nvarchar(20)SET @var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @somekey)
Of course, I'd rather just have to query "table1" just once to assign my variables.
What obvious bit of T-SQL am I missing?
Thank you in advance.
Hi,
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
Can anyone render any assistance on this one???
I would certainly appreciate it.
Thanks.
I am pretty new to the DB part of this but have built an asp.net web appplication with 2 tables:
FORMS and UNITS
I have created a web page that will allow users to add forms and associate a unit with that form. I now need to be able to allow users to associate the form with multiple units.
I can change the web page list box to allow multiple selections but that doesn't solve the problem. This seems like a pretty simple task but I can't seem to find anything on it.
any help???
below is the stored procedure I was using:
CREATE PROCEDURE dbo.USP_AddForm
@UNIT_IDint,
@F_TYPE varchar(20),
@Titlevarchar(100),
@Keywords ntext = NULL,
@Descriptionntext = NULL,
@FileNamevarchar(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE @NewFOrmID INT
INSERT FORMS
(
UNIT_ID,
F_TYPE,
Title,
Keywords,
Description,
FileName,
RevDate
)
VALUES
(
@UNIT_ID,
@F_TYPE,
@Title,
@Keywords,
@Description,
@FileName, GetDate()
)
SELECT @newFormID = @@IDENTITY
SELECT newFormID = @newFormID
END
GO
We have Three Tables in sqlserver2012
Master Table
OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz
Child1 Table
OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))
Child2 Table
PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White
We want to have result like
OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow
1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red
I have tried
;with mycte as (
select ms.OrderID,ms.PackageID
,ms.CustomerName
, Replace(stuff([ControlName], charindex('Column',ControlName),len(ControlName),' '),'Row','') rowNum
,Replace(stuff([ControlName], 1, charindex('Column',ControlName)-1 ,''),'Column','') columnNum
[Code] .....
it works if we have a product in one color only. like if we have pant in red and blue then its showing just first record
Hi,
I am using DTS in SQL server2k
What is the best way to insert CSV records, where 1 record maps onto multiple tables with parent/child or PK/FK relationship.
eg.
CSV record
(ID, param1, param2, param3)
ParentTable(ID as PK)
Param1Table(ID as FK in ParentTable, param1)
Param2Table(ID as FK in ParentTable, param2)
Param3Table(ID as FK in ParentTable, param3)
Currently i am using multiple Transform Data tasks with ActiveX script. But it seems to work slow.
Thanks and regards,
Henry
I am new in SQL programming world, following is query that i had created
select interfaces.nodeid as 'Node Id',
nodes.caption as 'Node Name',
netflowsources.Lasttime as NetflowLastTime
from interfaces inner join nodes on interfaces.nodeid = nodes.nodeid
inner join netflowsources on interfaces.interfaceid = netflowsources.interfaceid
where netflowsources.LastTime NOT LIKE GETDATE()
which is from that query i get a return successfully, but i just noticed that, for column nodeid was showed me multiple duplicated records, for example
nodeid value = a,a,a,b,b,c,c,c,c,d,d,d
But what i expected was to get a return without any duplicate record within it. and i also have tried using "distinct" command, but that only impacted on "a" value, but others value not change at all.
Is there a way to update all of the records in a table all at once using the results of a select of a different table's data?
For example.
There are two tables, each have a primary index of catnum. One table (invoice_items) contains the line items (catnum) of customer invoices. The other table (sales_history) is a sales history table. I want to select all data from the invoice_items and sum the sales for various time periods (sales_0to30days, sales_31to60days, etc.) I then want to update the sales_history table which has columns: catnum, sales_0to30, sales_31to60, etc.). I want to run this daily to update all records.
Any help would be appreciated. Many thanks.
Hi All,
I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record,
Table1 has the primary key
id
--
1
2
3
4
Table2 has the follwing records
id year subject
-----------------
1 2000 English
1 2002 French
2 2004 English
2 2005 English
2 2006 English
3 2007 French
I want the result to be like this
id 2000 2001 2002 2003 2004 2005 2006 2007
-----------------------------------------------------------
1 English null French null null null null null
2 null null null null English English English null
3 null null null null null null null English
Appretiate your assistance
Hi All,
I'm using Microsoft SQL Server Management Studio with SQL Server 2005 (SP2).
I have not had much experience with t-SQL and iterative logic implementation through SQL, therefore I think my problem is fairly basic for most of the pros here.
Here it goes...
I have two tables at hand, TestTab1 and TestTab2.
TestTab1 contains two attributes (columns) namely account_name (nvarchar(50)) and entry_count (int). For all rows in TestTab1, account_name is unique, i.e. for each account_name there is only 1 row in TestTab1. This is my source table.
TestTab2 contains one attribute namely account_name (nvarchar(50)). This table is empty and is my destination table.
I need to insert X entries (rows) for each account_name into TestTab2 where X is the int value in entry_count againt each account_name in TestTab1.
In other words, I need to insert account_name into TestTab2 as many times as the number in entry_count indicates.
I tried reading through the documentation but the help was not friendly enough for me to understand how to implement this.
Looking forward to the pro support.
Thanks,
I have a text file to import where there are three file types: a header which has info about who sent the file and begins with 'H', detail records that begins with D and a trailer record that begins with T and just has the record count following that. The fields are delimited by '*'. H, D and T records each contain a different number of fields.
I suspect that what I should do is to split this file into three separate files. I tried to do this with SSIS but ran into problems. If I make the output a file destination, it won't let me use that output as input for the next process. There are no arrows I can grab onto to link to the next transform.
This is my first SSIS package although I made hundreds of DTS packages a few years ago. I can't figure this out in DTS either.
This sounds like it should be an EASY thing to do.
Hi,
Here is my sample table creation and insertion script.
I want represent as a summary-result using just one record.
How can I ?
Please note the below red color text.
create table policy
(
id int not null,
name nvarchar(50) not null,
constraint pk_policy primary key(id)
);
create table localhost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_localhost primary key(policy_id,id)
);
create table remotehost
(
policy_id int not null,
id int not null,
ip_begin binary(4) not null,
ip_end binary(4) not null,
prefix tinyint not null default 0,
constraint pk_remotehost primary key(policy_id,id)
);
create table rate
(
policy_id int not null,
inbound int not null,
outbound int not null,
constraint pk_rate primary key(policy_id)
);
-------------
insert into policy values(0,N'policy0');
insert into localhost values(0,0,0xC0A80101,0xC0A80101,24);
insert into localhost values(0,1,0xC0A80A01,0xC0A80B01,0);
insert into remotehost values(0,0,0xACA80101,0xACA80101,0);
insert into remotehost values(0,1,0xACA80A01,0xACA80B01,0);
insert into remotehost values(0,2,0xACA80C01,0xACA80C01,24);
insert into rate values(0,1000,2000);
-------------
select * from policy;
select * from localhost;
select * from remotehost;
select * from rate;
-- result of policy table
0 policy0
-- result of localhost table
0 0 0xC0A80101 0xC0A80101 24
0 1 0xC0A80A01 0xC0A80B01 0
-- result of remotehost table
0 0 0xACA80101 0xACA80101 0
0 2 0xACA80C01 0xACA80C01 24
-- result of rate table
0 1000 2000
------------------------------------------------------
desired result set
id name localhost remotehost rate
-- ---------- ---------------------------------------------------------------- ------------------------------------------- -----------------
0 policy0 (192.168.1.1/24),(192.168.10.1~192.168.11.1) (172.168.1.1),(172.168.12.1/24) 0,1000,2000
descriptin of desired result set :
0. key value is id column and is referenced by each policy_id column.
1. id and name column is the same as policy table's.
2. localhost and remotehost columns are intigration of ip_begin,ip_end,prefix.
3. ip_begin and ip_end should be converted dotted presention from numeric format.
4. if prefix greater than 0, it should be displayed using '/'.
5. if ip_begin and ip_end are equal, show just one ip.
6. if the two ips are different from each other, they separated by '~'.
7. rate field is packed divided by ','.
A flat file I must parse has multiple record types in the same file that must be processed together. For instance, in the following example:
01TestStuff888
02TestStuff2
03TestStuff3
01TestStuff4
02TestStuff5
Each 01 record type has the records after it associated to it until the next 01 appears, so TestStuff would have TestStuff 2,3 related to it while TestStuff 4,5 belong together. In the example the 888 in the 01 record is the key to the group, but it does not appear in the following lines.
The problem is that each record type has different line formats, columns, etc, so they must be parsed differently. I have created a conditional branch on the first two characters, and written each record type out to a seperate flat file for that type, so that they can be imported again and parsed with the Flat File Source, but I am unsure how to relate them again. I tried appending the 888 to the other lines before they were written out, but I can't find a way to share the variable across the conditional split branches using a script component.
Does anyone have an idea how I could parse these files and keep the relationship intact?
Is there a way to tell the flat file wizard to use a different map based on certain characters?
Is there a way to share a variable across the different braches of a conditional split.
Strange question I know, but thanks for any help.
Travis