Create 'Dummy' Rows

Dec 6, 2005

Guys - scenario/DDL/DML below

Create table #Periods (Period INT , Frequency INT , startdate datetime NULL , enddate datetime NULL )
insert #Periods (Period , Frequency , startdate , enddate)
select 0 , 3 , '01-Nov-2004' , '30-Nov-2004'
union all
select 1 , 3 , '01-Dec-2004' , '31-Dec-2004'
union all
select 2 , 3 , '01-Jan-2005' , '31-Jan-2005'
union all
select 2 , 6 , '01-Nov-2004' , '30-Nov-2004'
union all
select 3 , 6 , '01-Dec-2004' , '31-Dec-2004'
union all
select 4 , 6 , '01-Jan-2005' , '31-Jan-2005'

select * from #periods
Period Frequency startdate enddate
03 2004-11-01 00:00:00.0002004-11-30 00:00:00.000
13 2004-12-01 00:00:00.0002004-12-31 00:00:00.000
23 2005-01-01 00:00:00.0002005-01-31 00:00:00.000
26 2004-11-01 00:00:00.0002004-11-30 00:00:00.000
36 2004-12-01 00:00:00.0002004-12-31 00:00:00.000
46 2005-01-01 00:00:00.0002005-01-31 00:00:00.000


For any frequency (in this simple example 6) where the 'lowest' period is not 0, I need to create dummy rows
so here I need to create the following
Period Frequency startdate enddate
06 NULL NULL
16 NULL NULL

I've built a temp table to identify the min period for each Frequency but am not sure where to go from here using this to do the inserts ?
select
min(Period) as MinPeriod ,
ResetFrequency
into #Periods2
from #Periods
group by ResetFrequency

select * from #CashFlow2
MinPeriodResetFrequency
03
16

View 3 Replies


ADVERTISEMENT

Dummy Where Clause Allowing Dummy Select Of Data - Utilizing Where Value = 1

Aug 30, 2007

Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.

The function of this, I believe was to make the select work but not retrieve any actual values.


I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.

What I am thinking may not even apply to what I need to do here.

If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.

Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table
but I was hoping this would work.

Example:

Insert Into table
Select
1 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'CA'

Union
Select
2 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'NJ'

Union

/*Final Select */

Select
6 as RecId,
' ' as field1,
field2
From test1
Where 'some value' = 1'

Thanks much for your assistance!!!

TADEG

View 1 Replies View Related

Create Dummy Data

Feb 24, 2008

Hello,


I have 4 tables: Folders, Files, Tags and FilesTags.



I need to insert some dummy data for testing as follows:



1. Insert 100 records in Tags tables.
Give Tag 01, Tag 02, Tag 03, ... to Tag names.


For this I have the following:



declare @i integer
select @i = 1
while @i <= 100
begin
insert into Tags ([text])
select 'Tag ' + right('000' + convert(varchar(3), @i), 3)
select @i = @i + 1
end



What I am missing is the following:



2. Insert 10 records in Folders tables.

Each folder should be filled as follows:
FolderID = New Guid
Name = "Name" + N (1, 2, 3, ...)
CreatedDate = Current Date



3. For each Folder I want to insert a random number of Files.
It can be added 0 to 10 Files.

Each file should be filled as follows:
FileID = New Guid
FolderID = Folder ID
Description = "Description" + Folder Number + File Number


4. For each file added associate it with a random number of Tags.

It can be 0 to 10 Tags and it should be done as follows:


Insert record in FilesTags where:
TagID = Random TagID taken from table Tags
That has still not be used in current File
FileID = Current FileID



Could someone, please, help me doing this?



My tables are as follows:



create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(200) not null
)
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
CreatedDate datetime not null,
[Name] nvarchar(200) null
)
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
Description nvarchar(2000) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,


)
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)



Thank You,
Miguel

View 5 Replies View Related

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?

Mar 12, 2007

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

View 3 Replies View Related

SQL Dummy!

Jul 21, 2007

Thank you to the two persons who took time to respond to my question.

I have to tell you - I know absolutely NOTHING about SQL.

Can you please tell me in language a two-year-old could understand, "What is SQL?"

View 3 Replies View Related

Dummy Question

Mar 3, 2004

Hello,

For sure that this is a dummy question but can you explain me how SQL Server reacts to the fact that we have the same application running in two different pc's and this two applications intent to insert,update, delete or even read a record from the same table

Does "he" blocks the record until one of them leave the table ??? And if the other tries to update at the same time ???

Thanks,
Paulo

View 8 Replies View Related

Dummy Table?

Feb 17, 2004

Hi!

I'm new in SQL SERVER 2000 and i'm looking for some kind of predefined dummy table. You know something like the "dual"-Table in Oracle. I want to use it for queries like that:

Select 1, 'auto' from dummy_table

Does anyone know, if something like that in SQL SERVER 2000 exists?

Nick

View 5 Replies View Related

Dummy Question

Jun 6, 2006

It seems every month when I'm diluged by lots of data I fall back on olereliable, Excel. But, I get over 50,000 rows of data to scrub. A colleagueof mine suggested I use a database. Seems simple so far, but having dabbledin Access, it has always not so intuitive to understand. My question istwo-fold, is SQL a database. I believe its the language of some otherdatabase. If I'm corrent on the later, what database(s) use SQL?Tony

View 1 Replies View Related

Dummy Table In SQL Server

Apr 19, 2004

Hi, Is there a dummy table in SQL Server just like DUAL in Oracle?
Thanks..

View 3 Replies View Related

Accomodating 'Dummy' Data?

Jul 23, 2005

Hello,I am working with a vb6 Windows application that runs on Tablet PC's.There are about five SQL Server 2000 databases that tie into theapplication. SQL Server MSDE runs on the tablets, and currently thereis only one instance running. There are efforts underway to upgrade theapplication to a .NET SmartClient.One enhancement that needs to be made to the current application willbe to provide a 'training mode' in the application for the users. Theuser will use the same application, but will work with 'dummy' datainstead of live data when in training mode.So far as I can tell, the easiest way to accomodate this would be tohave a second instance of MSDE with 'copies' of all the databasesrunning, but with the 'dummy' data contained in the databases copies.The application would have some mechanism for switching to 'trainingmode', and maybe by some change of a connection string, the dummy datawould be presented in the application.So aside from accomodating a 'training mode' feature in theapplication, the application would remain the same, and the databaseschema for the databases would remain the same. Only the data wouldchange for 'training mode'.I'm looking for some ideas on how to approach this. This application isused by five thousand field agents, and so we can't support thisimplementation on a case by case basis. So my thought is that thesolution needs to have 'few moving parts'. If we can do this and getaway from having two instances of MSDE, that would be great. I have nottried to set up identical databases in different directories on thesame instance (I figure that would cause a problem in a system table ortwo someplace, but maybe not), but its one thing I want to investigate.If you have any ideas on this, please share!Thank you for your help!CSDunn

View 1 Replies View Related

Question About Dummy Constraint

Jul 20, 2005

I've written code that dynamically builds an sql query based onvarious constraint possibilities.The problem is the code would have been very complex had I not come upwith a dummy constraint as a kind of place holder in the statement.To avoid complex logic that determines if there was another constraintbefore any other constraint and hence the need to add, say, AND ornot, I came up with a dummy constraint so that every subsequentconstraint will begin with AND. There's no need to determine whetherto add AND or not. This makes the coding much simpler because allthat needs to be done is ask if a certain condition exists then addthe constraint along with AND in front every time.So what I did was create the statement like this:SELECT elapsed_time AS ET from Table1 WHERE 1 > 0 AND 1stConstraintAND 2nd Constraint and so on.See if the 1 > 0 condition were not there it would be necessary tofirst determine the first actual contraint and not add AND in front ofit and then add the rest with ANDs in front of every one.I should add that the user does not have to select any constraint andthat's the problem. I need to stick that WHERE 1>0 in there so thatthere doesn't need to be a determination of which other, if any,constraints are selected.Even if my explanation above is not well understood, believe me thefront-end coding is much easier this way.My question is does the 1 > 0 conditional check present the databasewith any significant overhead or as far was dummy constraints go isthis as good as any other?-David

View 4 Replies View Related

Inserting Dummy Data

Aug 27, 2006

No one answered this question, how do people normally insert dummy data into tables? I mean, where does the data typically come from? what process is used to insert the data? Note: This is for testing purposes, eventually to be tested in a Web-based front-end which I know nothing about.

thx,

Kat

View 8 Replies View Related

Inserting Dummy Lines And Padding

Dec 2, 2005

Hi AllCan you please help me with a few queries on adding a header line andpadding rows out.I apologise profusely for not providing the DDL for this, but I don't haveit. All I have is stored procedure that I'm trying to edit to make thefront-end app display the right data.The relevant part of the stored procedure that I'm working on is as follow:Declare StockHelpCursor Scroll Cursor ForSelect s.StockID,ISNULL(sd.ShortDescription, s.StockID) +space(30-len(ISNULL(sd.ShortDescription,s.StockID))) +pl.name +space(10-len(str(pl.name,10,3))) +sp.currencyid + str(sp.sellingprice,10,3) +space(10-len(str(sp.sellingprice,10,3))) +str(sq.quantityinstock)From Stock s, StockDescriptions sd, StockQuantities sq,StockPrices sp, PriceLevels plWhere (s.StockID Like @theID) And(ISNULL(sd.ShortDescription, sd.StockID) Like @theName) And(s.StockID=sd.StockID) And(s.StockID=sq.StockID) And(s.StockID=sp.StockID) And(sp.PriceLevelID=pl.PriceLevelID) And(sd.LanguageID=@theLanguageID) And(sp.CurrencyID=@theCurrencyID)Order By s.StockIDOpen StockHelpCursorPLEASE NOTE: this query works fine apart from the following problems:1) Creating a header line - I need to insert a header line to this cursorfor the field headers, as the app is stripping off this header. I wasthinking of creating a var, sticking it in-between the Declare and theSelect part and inserting these field headers, but I don't know how to dothis. Any ideas?2) Padding the results - As you can see from the script, I have tried to padout the above fields as the app's output window is basically a textbox, butthey just don't line up.NOTE: I'm using the 3 in the str(xxx,10,3) bit to make my numbers show at 3decimal places.Could you please give me some pointers on how I can pad these out correctly.ThanksRobbie

View 7 Replies View Related

Need To Generate Test (Dummy) Data In SQLServer 7.0

Nov 1, 1999

Hi.

Does anyone know if SQLServer 7.0 will generate dummy data for specific columns in tables?

TIA,

Jeff

View 2 Replies View Related

How Can You Insert A Dummy Row At The Top Of Every Related Recordset In A Table?

Jan 22, 2008

I have the following:

TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5

I need to insert a dummy row as the first row in this AuditRecord table for every occurrance of a given TicketID so that I get the following:

Dummy_Tick1 Dummy_AuditRec
TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
Dummy_Tick2 Dummy_AuditRec
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5

The AuditRec table is huge. How can I accomplish this using SQL?

View 1 Replies View Related

Test - Populating Tables With Dummy Data

Aug 24, 2006

In 2000, BCP seemed the way to go. DTS packages would also work. My question is, in 2005, what is the best choice? I seem to remember that BCP ignored all referential integrity constraints, and applying them afterwords was a royal pain. I'm not a BCP expert by any means. Running this at the command line means using the DOS prompt correct?

What is 2005's answer to this?

View 4 Replies View Related

Sort Matrix On Dummy Grouping Value/subtotal

Apr 23, 2008



I have matrix with 3 row groupings
Parent, Child, Baby

I have a value in the data cell and also a dummy column grouping to show some % values.

The % is basically what the row subtotal represents as a % of all the data in the dataset.

The Parent row group also has a subtotal, so the % here will show all values within the parent as a % of all data.

I have interactive sorting on Parent, Child and Baby.

but what I want to do is this:

Interactive sorting of the parent-subtotal-percent column values to cause a physical reordering of the parents.

i.e. show me the parents in order of their "% as a total of the entire dataset"

Possible?

View 5 Replies View Related

SQL Server 2012 :: Inserting Dummy Records Using Loop Statement

Jul 17, 2015

I have the following attributes in this Table A.

1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)

I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this

LOCATION_ID
1

Serial Number
SN-01

KeyID
E1210

Reference_Address
83

SourceTime
2015-05-21 00:00:00 000

SourceValue
6200

View 7 Replies View Related

Transact SQL :: Creating Dummy Headers (column Names) With Union All

May 19, 2015

I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....

COL 1     COL2      COL3       COL4 etc.                         BEH                             BIG       BPL etc.

*************************************   INTAKT       DEFEKT       INTAKT DEFEKT          INTAKT

*************************************       B                E                 B         E                    B

I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?

View 4 Replies View Related

Create N Rows From 1 Row?

Sep 8, 2006

Hello...

I have a small question about how to realize something in SSIS.

 

We are rewriting an Application and we will be normalizing a table. The current Table has Data in the Format:

ID - Name - Type500 - Type1000 - Type2000

1  - Test - 2 -  1 - 0

2 - Test 2 - 0 - 2 - 1

 

The Targets would be:

ID - Name

1 - Test

2 - Test

TargetID , Type_ID , Date (+ ID Field omitted here)

1 - 1 - 1.1.1900

1 - 1 - 1.1.1900

1 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 3 - 1.1.1900

 

So basically we need to generate N type rows for each "Count" in the Type Fields. So whats the best aproach to convert this data in an SSIS Package? Currently I am thinking about calling a SP to split the rows, but I dont "like" this aproach since it would place import/migration logic into the database and I would have to "clean up" later.

View 3 Replies View Related

How To CREATE Duplicate Rows

Apr 14, 2004

this may sound like a weird one, but i need to create duplicates of all rows that satisfy a condition.

using asp, i am able to select rows from a databate using a recordset, only to insert it straight back into the database, thus assigning it a new unique id.

but is there any one to perform this action just using sql?

thanks,

goran.

View 12 Replies View Related

How To Create SELECT For Rows In Table

Oct 20, 2014

I have two db-tables:

"TAB01" with structure:

CREATE TABLE TAB01 (
ID int NOT NULL,
Name varchar(64) DEFAULT NULL,
PRIMARY KEY (ID)
)

It contains only name of some object.And "TAB02" with structure:

CREATE TABLE TAB02 (
ID int NOT NULL,
TAB01_ID int NOT NULL,
PositionA int,
PositionB int,
StringVal varchar(32) DEFAULT NULL,
PRIMARY KEY (ID)
)

which contains following data:

ID|TAB01_ID|PositionA|PositionB|StringVal|
1|1|1|1|A|
2|1|1|2|B|
3|1|1|3|C|

[code].....

and I need to find a sequence of values in column "StringVal", for example: A B.I look for a suitable SELECT, that returns (in this case) following result:

TAB02.ID|TAB01.Name|TAB02.PositionA|TAB02.PositionB|
1|ABC|1|1|
10|DEF|2|3|
25|JKL|4|3|
30|MNO|5|1|
36|MNO|5|7|
41|PQR|6|4|
46|STU|7|2|

Data inserted in "TAB02" represents real-life structure:

ID|Name|P01|P02|P03|...|Pxy|
1|ABC|A|B|C|D|E|A|D|
2|DEF|J|K|A|B|F|F|B|
3|GHI|B|A|C|A|F|G|A|F|
4|JKL|F|E|A|B|B|E|E|
5|MNO|A|B|C|B|A|D|A|B|
6|PQR|D|E|F|A|B|A|F|
7|STU|A|A|B|B|E|B|E|

View 1 Replies View Related

Create Table With 15,000,000 Default Rows

Jul 23, 2005

Hi I want to create a table with one column, which is a identitycolumn.Let's say like this:CREATE TABLE DefaultTable(N int identity(0,1))Then I want to fill this table with 15,000,000 records, so that I havea table with the numbers 0 to 14,999,999.How can I do this as fast as possible. A standard INSERT would take along time.(It can be a temp table or a table variable. I just need a list withnumbered 0 to 15,000,000)Thank you.Gidon

View 2 Replies View Related

Create Duplicate Rows With One Column Changed

Mar 12, 2013

I am writing a db conversion for a retail grocery chain. This chain uses pricing zones to designate what stores get a certain price

Example:
Cheetos
Zone A: $2.79
Zone B: $2.89

The pricing data in the tables is listed by zone. However, the new product uses pricing by store.

Zone A contains stores 1,2,4,6,7....
Zone B contains stores 10,11,12,14.....

I need to be able to duplicate the rows in a manner that I can take the row containing a price for Zone A and duplicate it for each store in the zone. I have a table of stores with corresponding zones.

So I'm looking to go from:

Zone UPC Price
A 1234500000 2.79
B 1234500000 2.89

To:

Store UPC Price
1 1234500000 2.79
2 1234500000 2.79
10 1234500000 2.89
11 1234500000 2.89

View 13 Replies View Related

TSQL - Create A Variable Number Of Rows

May 4, 2008

I have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :)
Thanks



ORIGINAL DATA
EventID StartDate EndDate
1 1/1/08 1/1/08
2 20/1/08 22/1/08


REQUIRED DATA
EventID EventDate
1 1/1/08
2 20/1/08
2 21/1/08
2 22/1/08

View 2 Replies View Related

Create Script To Insert 200 Rows Into Table

Aug 15, 2007

I have to create a script to install a database, and one of the tableshas about 200 rows of static data... I dont want to have to manuallytype in 200 insert statements, so is there a better way to do this? Ithought about maybe exporting the data into a CSV file and using somesort of procedure to insert the records that way... Any advise?

View 6 Replies View Related

SSIS Read Excel Rows And Create New Output

Feb 1, 2013

I have an excel file with following data:

Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix
123 Agency MA 300,000 Mickey Mouse
Rogers Inc MA 50,000 Mike Greenwell

I want to be able to read the file and create new excel files for each Agent listed. So for Example, the above file would create 3 separate files since there are 3 different Agents listed. Each Agent file would contain the same information from the original file. The name of the file would be somethign like AgentName.xls...So the SAN group file would have this:

Agent State Exposure Insured Name
Rogers Inc MA 100,000 John Smith
SAN Group RI 200,000 Jim Morrison
SAN Group RI 100,000 Jimi Hendrix

Is there a way to accomplish this in SSIS?

View 2 Replies View Related

How Do You Create A Multiple Word Search Box To Return Rows From A Sql Database In Asp.net 2.0

Jun 12, 2007

I am using VS2005 to construct a website. I have a sql database with 3 datasets. One of the table adapters is called Proudcts and has the following fields. ProductItem,    Description,   Price,    ProductID,    PackSizeI want to be able to see a summary of the products (there are thousands of them) using one or more words (or partial words) in one text box to search in only 3 fields (ProductItem,    Description,   ProductID, ). This needs to show only records which contain the search criteria in a gridview?This is such a basic requirement for a website, and can be found on many sites, but I haven't found how to do it.Thanks, Bri 

View 7 Replies View Related

SQL Server 2012 :: Create View With Exploding Rows Into Multiple

Apr 28, 2014

I have a table like below:

ItemIdAmountTax1Tax2SrvType
111 100 10 20 1
112 200 10 2
113 300 10 30 3

Now I want to create View that will have an exploded resultset based on SrvType.

For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.

I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.

View 4 Replies View Related

Create A Data Flow In SSIS Wich Updates Som Rows.

Aug 14, 2006

Hi,

I have a table customer wich has the columns phone_number(char type) and ok_to_call(bit type). There are already data in the table and the column ok_to_call only contains the value false for every row.

Now i want to update the latter column. I have a text file with a list of phone numbers and i want that all the rows in the Customer table(phone_number column)that matches the number in the text file to update ok_to_call to true.

This is to be done in SSIS(Integration Services). I'm new at this and i've looked around that tool but is a lot of items, packages and stuff so i dont know where to begin.

Would appreciate help on how to solve this issue in SSIS. What controlflow/Data flows to use,wich items and packages to use, how to configure and how to link together?

Regards
/Tomas

View 3 Replies View Related

Only Create Destination Flat File When There Are Rows Returned From Ole DB Source

Oct 25, 2007

Hi,

I have a data task with the following requirements:
1) Run query against database to retrieve rows
2) Add header and footer row to the result set. The footer row must contain a count of the records.
3) Write the rows to a fixed width file if there were any data rows

I have got to the point that I can create the file (using a set of tasks that includes derived columns, sorts, aggregation and merges). However the file is created regardless of whether there were data rows returned.
I can't check the row count before proceeding as this isn't set until the data task ends. And if I try to split them into separate data tasks (so that I can access this variable and perform conditional execution) it becomes harder to access the original rows.

Do you have any recommendations on the best way to achieve this?
It all seems to be very complex and I'm starting to feel that it would be easier to do this outside of SSIS...
Please help me to keep the faith!

For those interested this is a slightly simplified version of what I have so far (all within a single data task):

1.Run dummy sql to create header row 2.Run main SQL to retrieve rows
| |
| 3.Multicast
| | |
| | 4.Create footer row by doing sum() in aggregate task
| | |
| 5.Merge body and footer
| |
6. Merge header with body and footer
|

7. Create fixed width file

View 5 Replies View Related

How To Force Deferred Update To Create Two Rows In Change Data Capture

Mar 23, 2015

I have located a bug in the functions cdc.fn_cdc_get_net_changes_<capture_instance> generated when you enable cdc on a table. This bug can be triggered if 2 rows are created in the _CT table having the same values for the __$start_lsn, __$seqval and the table's key column(s). From research on the internet I have found such rows can be created by a "deferred update": a single update statement in which a column that is part of a unique constraint is updated.

In order to report the bug with Microsoft I need to create a complete series of steps-to-reproduce. But even though the situation happens several times a day in our production environment, I have not yet been able to reproduce it in my test environment.I need a single update statement (plus maybe some steps in advance) that make that the log reader inserts 2 rows into the _CT table, one with __$operation = 1 (delete) and another with __$operation = 2 (insert) as opposed to the single row with __$operation = 4 that it inserts for a normal update. Below is the script I have so far to create a fresh database, enable cdc, create a test table, insert some data and update this data.

I would have liked the last update statement to be handled as a "deferred update". However in all of my tests the log reader just simply inserts a single row into the cdc.dbo_NETTEST_CT table.how to reproduce the situation where I get the 2 rows with __$operation 1 and 2 from a single update statement instead of the single row with __$operation = 4.

CREATE DATABASE [cdcnet]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'cdcnet', FILENAME = N'S:SQLDATAcdcnet.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'cdcnet_log', FILENAME = N'T:SQLLOGcdcnet_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

[code]....

View 4 Replies View Related

Reporting Services :: Create Indented Child Rows In SSRS Report

Oct 25, 2015

how to create indented child rows under the parent, in SSRS report. I am quite new to SSRS, the group by function works in a different (and obvious) way then what I want.I have a sample dataset as below.

Declare @Employee Table(Code varchar(10), Name varchar(500),
Organization varchar(100), IsChild bit, ParentId varchar(10))
insert into @Employee Values('BR456','Brandon','XYZ',0,NULL)
insert into @Employee Values('CR674','Carolina','ABC',0,NULL)
insert into @Employee Values('ME230','Melissa','PQR',1,'CR674')
insert into @Employee Values('JR987','Jerome','LMN',0,NULL)
insert into @Employee Values('JO435','Joseph','IJK',0,NULL)
insert into @Employee Values('ME321','Meghan','UVW',1,'JO435')

Is it possible to make this using some formatting options?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved