Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!
Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL...pare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegr oups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>
|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQ...mpare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare data in tables after inserting?

I'm new to bcp and am trying to import the data from an existing
table into a new table (copied from the existing table).
I ran these:
execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
-Sserver -T -n'
bulk insert database.dbo.tableA from 'C:\temp\table.txt'
with (DATAFILETYPE = 'native')
Everything seemed to be fine, no error messages. When I ran select
count on the original and the destination, and got very different
values
select count (*) from dbo.table
1140089
select count (*) from dbo.tableA
205272
Why is there such a discrepancy? How should I go about making sure the
data in the tables match, or get them to match, post insert? TIA!Was there existing data in tableA at the time of the import? A count(*) is
a quick way to compare row counts. A more complete solution would be to use
a third-party data comparision tool i.e.,
http://www.red-gate.com/products/SQL_Data_Compare/index.htm .
HTH
Jerry
"nmsm" <naomimsm@.gmail.com> wrote in message
news:1127747561.868201.208900@.f14g2000cwb.googlegroups.com...
> I'm new to bcp and am trying to import the data from an existing
> table into a new table (copied from the existing table).
> I ran these:
> execute xp_cmdshell 'bcp database.dbo.table out c:\temp\table.txt
> -Sserver -T -n'
> bulk insert database.dbo.tableA from 'C:\temp\table.txt'
> with (DATAFILETYPE = 'native')
>
> Everything seemed to be fine, no error messages. When I ran select
> count on the original and the destination, and got very different
> values
> select count (*) from dbo.table
> 1140089
> select count (*) from dbo.tableA
> 205272
> Why is there such a discrepancy? How should I go about making sure the
> data in the tables match, or get them to match, post insert? TIA!
>|||There was no exisiting data in tableA at the time of import, which is
another reason the select difference surprised me.
Is there another way of comparing without a third-party comparison
tool?

Compare Data Before Inserting

I have a DTS package in which I would like to move to SSIS and also add some new functionality. My current DTS package is pretty simple:

1. Download a flat file from an FTP site
2. Execute a SQL task that deletes the data from the table
3. Insert the data into the table.

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes this can be done in SSIS, but the real questions are what

identifies the data as "new" and how many records are you planning to

process at one time.

If you have a primary key in the source data and you are working with

few records < 50k, then you could perform a lookup on the

destination to see if the primary key exists. If it does not then

you could redirect the row to an insert data flow using the error

output. If it does exist (std output), you could (in the typical

case) direct it to an update data flow.

If you have a primary key and are working with many records, you could

perform a merge join on the source and destination and then use a

conditional split to filter out/redirect the rows where the destination

key is null.

If you have some other identying attributes, then the methodology may

change. You may even want to investigate the Slowly Changing

Dimension transform.

Larry|||

Jason92 wrote:

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes. Check out: http://www.sqlis.com/default.aspx?311

-Jamie

Compare Data Before Inserting

I have a DTS package in which I would like to move to SSIS and also add some new functionality. My current DTS package is pretty simple:

1. Download a flat file from an FTP site
2. Execute a SQL task that deletes the data from the table
3. Insert the data into the table.

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes this can be done in SSIS, but the real questions are what identifies the data as "new" and how many records are you planning to process at one time.
If you have a primary key in the source data and you are working with few records < 50k, then you could perform a lookup on the destination to see if the primary key exists. If it does not then you could redirect the row to an insert data flow using the error output. If it does exist (std output), you could (in the typical case) direct it to an update data flow.
If you have a primary key and are working with many records, you could perform a merge join on the source and destination and then use a conditional split to filter out/redirect the rows where the destination key is null.
If you have some other identying attributes, then the methodology may change. You may even want to investigate the Slowly Changing Dimension transform.
Larry
|||

Jason92 wrote:

With SSIS I would like to first compare the data in the flat file with the data I already have stored in the table. If the data is new, then insert the new row into a different table. Can this be done via SSIS?

Yes. Check out: http://www.sqlis.com/default.aspx?311

-Jamie

Commit a loop of inserting

I use loop to insert few record into a table:
But the for_Loop only loop once and throw an error:
"The variable name '@.res_name' has already been declared. Variable names must be unique within a query batch or stored procedure."
What should i do to get this fix?

Code:

Protected Sub confirm_button_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim DataSources1 As New SqlDataSource()
DataSources1.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()

DataSources1.InsertCommandType = SqlDataSourceCommandType.Text
DataSources1.InsertCommand = "INSERT INTO cust_order (res_name, my_menu) VALUES (@.res_name, @.my_menu)"

Dim c As Integer
For c = 0 To selectListBox.Items.Count - 1 Step +2

DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text)
DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text)
DataSources1.Insert()
Next
End Sub

your loop is trying the re-add the same parameters on each pass.

For c = 0To selectListBox.Items.Count - 1Step +2 DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text) DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text) DataSources1.Insert() DataSources1.InsertParameters.Clear()'remove the previous parametersNext

you can clear them after you perform the insert so they can then be re-added with new values on the next pass through the loop

|||thanks, now the loop works fine.