Easier Method To Remove And Add A Table To Database?
Feb 22, 2000
I have a table that is corrupted and want to remove and add a backup version of it. How can i remove this table and add it again preserving all the foregin key restraints, permissions, dependencies, etc? Simply exporting and importing does not work. I could painfully remove the table and then painfully reconnect it again, recreating all the foreign key restraints, etc, by hand; but there has to be an easier way! What is the How-to?
Thank you!
Llyal
View 1 Replies
ADVERTISEMENT
Jan 5, 2015
I am using the Database is Oracle SQL Developer, here the Requirement is like Before insert the values in Database I need to remove the Database table and insert new values.
DELETE FROM XXMBB_NOSVOS_TMP_VO_NO WHERE EFFECTIVE_DATE BETWEEN
'01'
|| '-'
|| TO_CHAR(to_date(:N_Date,'DD-MM-YY'),'MON-YY')
AND to_date(:N_Date,'DD-MM-YY')
AND LEDGER_ID = LED and name_rdf='NOSVOS';
COMMIT;
DELETE FROM XXMBB_NOSVOS_BAL_TMP_VO_NO WHERE
PERIOD_NAME = TO_CHAR(add_months(to_date(:N_Date,'DD-MM-YY'),-1),'MON-YY')
[code]....
View 1 Replies
View Related
Aug 27, 2007
Hi everyone,
I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?
Can you please give some suggestions? It will be very helpful if you can post some references or examples.
Thank you!
View 12 Replies
View Related
Oct 26, 2005
I have a database with table representing city blocks, houses, and people. On my main aspx page, I have a datagrid which displays a list of the blocks, followed by a count of the houses in each block, followed by a count of the people in each block. Right now I have something that works, but it is awful. Every time the page loads it makes a ton of connections to the database, and I have convoluted spaghetti code. Now I need to add more functionality, but I can't rightly do that until I find a more efficient way to do this.Step one. The program connects to the database and gets a list of the blocks using the statement "SELECT blockid FROM blocks"Step two: The program iterates through each blockid in the list and executes the statement "SELECT houseid FROM houses WHERE blockid = (whatever)"Step three: The program counts the rows returned from step two to determine the count of how many houses are in that block. Step four: The program iterates through each houseid from step two and exectues the statement "SELECT COUNT (personid) FROM people WHERE houseid = (whatever)" the result is added to a variable that keeps a running count.Step five: the final value of the variable in step four is the number of people in that block.My question for you is, how can this be done more efficiently? Can I group together some awesome SQL statement that will get these counts? I thought about doing something like "SELECT blockid (SELECT COUNT houseid FROM houses WHERE blockid = something) as HouseCount" but I can't figure out how I could take the value in the first column (blockid) and pass it to the inner select statement.Any thoughts on how to make this better? Below is the full code for my function, in case you want to examine in more detail. Also, I am in the process of changing the select statements into stored procedures, so don't beat me up too badly over that bit of ugliness in my function. Thanks.Private Function GetBlockDataSet() As DataSet Dim myconnection As SqlConnection Dim objDataAdapter As SqlDataAdapter Dim query, connectionstring As String Dim tempDS As New DataSet Dim houseDS As New DataSet Dim peopleDS As New DataSet Dim DC1 As New DataColumn Dim DC2 As New DataColumn Dim i, j, peoplecount As Int32 Dim DR, DR2 As DataRow
query = "SELECT blockid FROM blocks" connectionstring = configurationsettsing.appsettings("ConnectionString")
myconnection = New SqlConnection(connectionstring) objDataAdapter = New SqlDataAdapter(query, myconnection) objDataAdapter.Fill(tempDS, "BlockList")
DC1.DataType = System.Type.GetType("System.Int32") DC2.DataType = System.Type.GetType("System.Int32") DC1.ColumnName = "HouseCount" DC2.ColumnName = "PeopleCount" tempDS.Tables("BlockList").Columns.Add(DC1) tempDS.Tables("blockList").Columns.Add(DC2)
i = 0 For Each DR In tempDS.Tables("BlockList").Rows query = "SELECT houseid FROM Houses WHERE blockid = '" query &= tempDS.Tables("BlockList").Rows(i).Item(0) query &= "'" objDataAdapter = New SqlDataAdapter(query, myconnection) objDataAdapter.Fill(houseDS)
tempDS.Tables("BlockList").Rows(i).Item(1) = _ houseDS.Tables(0).Rows.Count tempDS.Tables("BlockList").Rows(i).Item(2) = 0 j = 0 peoplecount = 0 For Each DR2 In houseDS.Tables(0).Rows query = "SELECT COUNT (personid) FROM people WHERE HouseID = '" query &= houseDS.Tables(0).Rows(j).Item(0) query &= "'" objDataAdapter = New SqlDataAdapter(query, myconnection) objDataAdapter.Fill(peopleDS) peoplecount += peopleDS.Tables(0).Rows(0).Item(0) j = j + 1 peopleDS.Clear() Next tempDS.Tables("BlockList").Rows(i).Item(2) = peoplecount houseDS.Clear()
i = i + 1 Next
GetBlockDataSet = tempDS ' Here comes the garbage collection myconnection.Close() myconnection.Dispose() myconnection = Nothing objDataAdapter.Dispose() objDataAdapter = Nothing tempDS.Dispose() tempDS = Nothing houseDS.Dispose() houseDS = Nothing peopleDS.Dispose() peopleDS = Nothing DC1.Dispose() DC1 = Nothing DC2.Dispose() DC2 = Nothing
End Function
View 5 Replies
View Related
Apr 20, 2007
This statement returns date formatted 'yyyymmdhhnn'. But there has to be an easier way. Can someone help?
DECLARE @dt datetime;
SELECT @dt = GETDATE();
SELECT CONVERT(varchar(40),@dt,112) +
RIGHT('0' + CAST(DATEPART("hh", @dt) AS varchar(2)), 2) +
RIGHT('0' + CAST(DATEPART("mi", @dt) AS varchar(2)), 2) AS isodt;
Thank you.
View 4 Replies
View Related
Sep 18, 2007
Hi-I have a sql server database, and am wring web apps to access it.I've created databases different ways, and ended up with different owners (eg dbo, nt authorityetwork services...)I also have connection strings using windows authentication, and some using a user name and password.I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely. What is the safest 'owner' of the database, and what's the safest way to connect?Thanks Dan
View 8 Replies
View Related
Feb 20, 2008
Hello,
I just finished a lengthy process (for me) of writing this cursor that gets each row from the Fact table, and creates a running total of the billable hours (hours from rows where BillableType = 1). It starts over when the month changes, the year changes, or the employee changes.
I did this again for a running total of billable hours for the year.
I put these in 2 stored procedures and run them in an Execute SQL control flow task in my SSIS package. It seems like SSIS is designed to make this kind of procedure simpler, and I'm wondering if I'm doing this in the best way, or if there's a more efficient way to do this using tasks inside SSIS. Can anyone advise? Any help is greatly appreciated.
Best,
Andy
Don't feel obligated to read this cursor if you understand the problem above. It's not color coded because when I copied it from SQL I lost the tabbing on the case expressions.
Code Snippet
CREATE PROCEDURE YearTotalHours AS
DECLARE @FactBillingId Int;
DECLARE @dt DateTime;
DECLARE @EmpKey Int;
DECLARE @YearTotalHours Float;
DECLARE @NewYearTotalHours Float;
DECLARE @NewYear Int;
DECLARE @BillableType Int;
DECLARE FactBillingFinalRows CURSOR FOR
SELECT FactBillingId
FROM FactBillingFinal
OPEN FactBillingFinalRows
FETCH NEXT FROM FactBillingFinalRows
INTO @FactBillingId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @EmpKey = (SELECT EmployeeKey FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
SET @dt = (SELECT dt FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
SET @BillableType = (SELECT BillableTypeKey FROM FactBillingFinal WHERE FactbillingId = @FactBillingId)
SET @NewYear = (SELECT Year(@dt))
SET @NewYearTotalHours =
CASE
WHEN @FactBillingId = 1 THEN
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
ELSE
CASE
WHEN @EmpKey <> (SELECT EmployeeKey FROM FactBillingFinal WHERE FactBillingId = @FactBillingId - 1)
THEN
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
ELSE
CASE
WHEN YEAR(@dt) = (SELECT YEAR(dt) FROM FactBillingFinal WHERE FactBillingId = @FactbillingId - 1)
THEN
CASE
WHEN @BillableType = 1
THEN (SELECT @YearTotalHours + (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId))
ELSE
CASE
WHEN @NewYearTotalHours IS NULL
THEN 0
ELSE @NewYearTotalHours
END
END
ELSE
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
END
END
END
UPDATE FactBillingFinal SET YearTotalHours = @NewYearTotalHours WHERE FactBillingId = @FactBillingId
SET @YearTotalHours = @NewYearTotalHours;
FETCH NEXT FROM FactBillingFinalRows
INTO @FactBillingId
END
CLOSE FactBillingFinalRows
DEALLOCATE FactBillingFinalRows
View 9 Replies
View Related
May 10, 2007
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies
View Related
Jan 7, 2008
Hi,
I have a table load which has load value for each hour.ie load_1,load_2...load_24... I want to find the max value between the 24 hourly loads and assign it to a variable say load_max...
Format of table
load_ID load_1 load_2 load_3 load_4 load_5 load_6...... load_24
1 2 4 5 6 7 8 23 56 44 22 64 33 67 24 345 34 75 57 24 23 24 24 66 789
These are the 24 load values with the load _id
I have lots of rows with load_id starting from 1- 100
Output should be to display the load_Id,load_max, load_min for each row...(after comparing the 24 loads with each other)
How can I do it with sql server.
View 7 Replies
View Related
Jan 14, 2008
Good afternoon,
I've here a shell plugin and it's compiling fine and can be viewed in BI Dev Studio when choosing the DM technique using the proper wizard.
I also have here a K-Means implementation that estimates the number of clusters using a statistical semi-empiric index (the PBM index).
This implementation is done in C# and works fine. But it has to receive all the data of the database (all variables for each row) in order to do the proper vectorial calculations in a CSR (Compact Sparse Rows) way.
Besides, as you know, K-Means needs all the data at once because of the clusters mean (centroid) calculation.
So, I have some questions:
1) Where to place the call to the K-Means implementation in the shell passing as argument an object holding all the data ?
2) After this call, with the data clustered, what other objects must be modified in order to use Microsoft Cluster Viewer ?
3) I will need to create a new column or a new table on the database to specify which data belongs to which cluster. Can I open an ADO connection as I normally do in other programs from inside the plugin or is there another (easier/better) way to do so ?
Thanks a lot once more.
Best regards,
-Renan Souza
View 1 Replies
View Related
Oct 5, 2006
hi,
i get just as frustrated each time i try to configure email alerts on failed jobs on ms sql, it is beyond me why microsoft couldn't just let you point out an SMTP server to send through and be done with it.
is there a way to avoid having to setup an email client on our sql 7 and 2000 servers through some 3rd party app or other simple solution?
thanks in advance,
daniel
View 2 Replies
View Related
Oct 11, 2006
Hello,
I have a Sql Server 2005 database with many tables, each with millions of records within them.
They all have a Receive Date field, with records going back 10 years or so.
What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?
~Le
View 2 Replies
View Related
Oct 21, 2006
Hi,
Sorry if these is not the right forum for this:
I€™m using the data adapter method FillSchema works fine in MS Access but SQL Server Express Edition it doesn€™t get the primary key of the table. For example if I try to use the Find method of the dataset I get this error €śTable doesn't have a primary key€? but in MS Access these do not happen.
This is the code that I€™m using:
Dim sql As String = "SELECT admin.rents.id, admin.rents.transaction_id, admin.rents.item_id, admin.rents.description, admin.rents.due_date, admin.rents.return_date, "
sql &= "admin.rents.amount_to_pay, admin.rents.paid_amount, admin.rents.tax, admin.rents.type, admin.rents.deposit_id "
sql &= "FROM admin.rents INNER JOIN "
sql &= "admin.transactions ON admin.rents.transaction_id = admin.transactions.id "
sql &= "WHERE admin.transactions.customer_id=?; "
RentsDA = New OdbcDataAdapter(sql, cn)
With RentsDA.SelectCommand
.Parameters.Add(New OdbcParameter("@customer_id", OdbcType.Int)).Value = TextBox1.Text.Trim
End With
RentsDA.FillSchema(ds, SchemaType.Source, "Rents")
RentsDA.Fill(ds, "Rents")
It's because of the joins?
I need to configure something in the sql server?
What is the problem?
View 1 Replies
View Related
Jul 10, 2007
Dear AllI am very new to MS SQL Server and I am wondering is there some toolwhich would allow me to build pivot tables in SQL more easily. At themoment writing a query can be quite challenging and difficult.Is there any software which allows you to do it more intuitively andgives you some visual feedback about query you are building?I would be very grateful for any help with this.wujtehacjusz
View 2 Replies
View Related
Apr 18, 2007
I'm trying to figure out what solution (replication, mirroring, clustering) would work best for me.
I have been reading many articles in BOL and in this forum. Most talk about getting data TO a backup/standby/subscriber, but I can't find a lot of info regarding getting the data BACK after a disaster is over.
We have a main office and a disaster recovery facility. Most of the time there are no data updates at the disaster location. So, I need to get data to the disaster facility via WAN (latency is not a huge issue - end of day syncing is fine) for backup purposes. In the event of a disaster, the main office will be offline and data changes will happen at the disaster site. When the disaster is "over" and we return to the main office, what's the best scheme to reverse the data back to the main office to start business again? We are a financial company, and have gigabytes of relatively static data. Most changes are current day. So, to snapshot a 100GB database when I know only a few hundred MB changes a day doesn't seem feasible to me.
Most replication scenarios (at least from what I see) can't easily "reverse" the replication after a disaster situation. I'm looking at merge replication on a schedule which seems to look good, but was wondering if anyone else has any ideas or suggestions?
View 5 Replies
View Related
Apr 12, 2008
I'm trying to select from a table with three columns. I want these columns to be spread out among multiple columns based on the values. I hope someone can shed some light on this. I might be able to use pivot, but don't know how the syntax would roll for this.
Here is the example of dummy values and the output I am trying to obtain.
drop table table1
create table table1
(Category int, Place int, Value int)
insert into table1 values
(1, 1, 20)
insert into table1 values
(1,2, 12)
insert into table1 values
(1,3, 30)
insert into table1 values
(2,1, 34)
insert into table1 values
(2,2, 15)
insert into table1 values
(2,3, 78)
select Category,
(select top 1 value from table1 where place = 1 and Category = t1.Category) as place1,
(select top 1 value from table1 where place = 2 and Category = t1.Category) as place2,
(select top 1 value from table1 where place = 3 and Category = t1.Category) as place3
from Table1 t1
group by Category
Thanks for the help.
View 5 Replies
View Related
Nov 11, 2006
Greetings all,
I have a bit of brainteaser that's going to take some serious thought.
I'm importing information from .xls files into a SQL table. The problem is I need to check for dupes and increment certain fields on success of dupe find and then not insert or delete the dupes.
For example, if I have Adam, Turner, 32, 50 already in the table and someone tries to insert Adam, Turner, 32, 50...I need it to increment to read Adam, Turner, 64, 100 and not insert the record. (Notice 2 fields were incremented.)
With that, I have created an INSERT trigger as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[trgInsertCheck]
ON [dbo].[MyTable]
FOR INSERT
AS
BEGIN
EXEC sp_UpdateDupes
EXEC sp_DeleteDupes
END
The first stored procedure checks for dupes and updates if any dupes are found as follows:
--------------------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_UpdateDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE MyTable set Widgets = Widgets + @Widgets, Age = Age + @Age
WHERE FirstName = @FirstName AND LastName = @LastName
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Lastly, it finds all dupes, deletes them and inserts one row back in as follows:
--------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_DeleteDupes] AS
DECLARE @FirstName varchar(20), @LastName varchar(20), @Age int, @Widgets int --declare all fields in table
DECLARE c1 CURSOR FOR
SELECT FirstName, LastName, Age, Widgets
FROM MyTable
GROUP BY FirstName, LastName, Age, Widgets
HAVING COUNT(*) > 1
OPEN c1
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
WHILE @@FETCH_STATUS = 0
BEGIN
--Delete all dupes...the cursor remembers the current record
DELETE FROM MyTable
WHERE FirstName IN (SELECT FirstName FROM MyTable GROUP BY FirstName HAVING COUNT(FirstName) > 1)
AND LastName IN (SELECT LastName FROM MyTable GROUP BY LastName HAVING COUNT(LastName) > 1)
AND Age IN (SELECT Age FROM MyTable GROUP BY Age HAVING COUNT(Age) > 1)
AND Widgets IN (SELECT Widgets FROM MyTable GROUP BY Widgets HAVING COUNT(Widgets) > 1)
--insert the current record back into the table
INSERT INTO MyTable(FirstName, LastName, Age, Widgets) VALUES(@FirstName, @LastName, @Age, @Widgets)
FETCH NEXT FROM c1
INTO @FirstName, @LastName, @Age, @Widgets
END
CLOSE c1
DEALLOCATE c1
Is there an easier way to do this?
(I know Age doesn't make much sense in this example but just replace it with a field would logically be incremented such as wadgets.)
Adamus
View 7 Replies
View Related
Oct 19, 2007
When I was looking at this I knew that I've done this same issue before without using temp tables at all but I need a push to to jar it loose.
I have a table like this:
Balance
Date
1
200
2/14/2000
2
350
2/14/2000
3
32
2/14/2000
2
723
2/14/1998
3
354
2/14/1998
1
321
2/14/2000
2
673
2/14/1998
3
581
2/14/2000
2
574
2/14/1998
3
50
2/14/2000
1
10
2/14/2000
And essentially need this.
Total Balance Before 1/1/2000
Total Balance After 1/1/2000
1
0
531
2
1970
350
3
354
663
Right now I'm splitting it into two temp tables and then joining them together.
Temp Table 1 has the sum of the balance grouped by field1 before 1/1/2000 and Temp table 2 has the after 1/1/2000 criteria.
Table 1 gets updates with field1 values from table 2 that aren't already there. And then the balance field after 1/1/2000 is merged in.
Utimately this will be used in a SPROC for a Multivalued SSRS report.
View 3 Replies
View Related
Feb 9, 2007
I have a DataSet which I am holding in .NET and I would like to know the quickest way to get the DataSet in to a Table on SQL Server?
Any sample code would be great.
View 6 Replies
View Related
Jul 8, 2007
I want to pass the table name as a variable
Declare @tbl varchar(30)
set @tbl = 'DB.dbo.Set'
declare @sql nvarchar(200)
set @sql =
'select * from OPENXML(@xmldoc,'DB.dbo.Site',2) with' + @tbl + ''
exec(@sql)
Please help me with the correct syntax.
Mayank
View 1 Replies
View Related
Aug 23, 2007
i need to use inner join while updating..
im using only one table..
rajesh
View 2 Replies
View Related
Jan 29, 2008
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
View 7 Replies
View Related
May 22, 2008
Hi,I have a table which I need to store times in. What is the best way of doing this? The only way I can think of so far is to save the times as a datetime such as '1/1/1900 03:00 PM' then format the date as "hh:mm tt" so that the day isn't displayed. Is this the best method?Thanks,Curt.
View 6 Replies
View Related
Dec 16, 2007
Lets assume database A is production, B is copy. SQL Server 2005 sp2, SQL CE 3.5
Database A has a variety of transactions against it 24x7
Database B (the copy) is for reporting and as a source of merge replication for SQL CE instances
Merge replication and reporting is used 24x7 as well
I have the following requirements:
Maintain an up to date copy of the production database (need not be up to the minute, could be hourly, even daily update)
Database B is read-only. The merge replication is NOT bi-directional.
Here is the caveat (which I think prohibits using some solutions to this problem):
The production application accomplishes much of it's functionality with in-memory copies of records. I have no control over the production application. When it works against the database, it sort of does a 'withdrawal-deposit' scenario. (to the best of my knowledge it's not using SQL Server transactions) So, for every record it works with, a copy is made out of the database, changes are made in memory, a delete of the database record is done, then the record is re-inserted.
With this kind of behavior in db A, I'm not sure what it would do to log-shipping or transactional replication. I do know that I want to minimize the changes required at the SQL CE instances to keep the sync operation to a minimal cost.
Any suggestions?
View 1 Replies
View Related
Feb 15, 2008
I need a SSIS Package that compares the two tables and removes the rows in the first table with keys that do not exist in the second table. For example....
I have a table of returns based on returnID. In another table I have returnErrors that are based on returnID as well. I want a package that will uses my returns table as a source and compares that dataset to the dataset of the returnError and remove or spilt the data so that my remaining dataset only has returns that have returnErrors. I can do this in T-SQL, but I am looking for a SSIS solution that uses the conditional split transformation or some other transformation(s) combinations.
-- Ryan
View 7 Replies
View Related
Jul 23, 2005
From what I've read in the docs, ado.net currently supports opening sqlserver ce tables in table-direct mode and performing Seek operations on them(using SqlCeDataReader), but not on the full-blown sql server. Is this(will this be) still true with ado.net 2.0 & sql server 2005?
View 11 Replies
View Related
Dec 29, 2007
I have a process that restores a backup from a primary server to a backup server daily. When doing the restore, sometime it fails (for various reasons).
I have coded a job to Set offline, set online, an then do the restore:
RESTORE DATABASE [xxx] FROM DISK = N'D:Backup Stagingxxx.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Sometimes it fails to bring back online, other errors as well. Is there a reliable method of doing this?
View 1 Replies
View Related
Feb 7, 2008
I wanted to set up a mechanism that would transfer blocks of records (a few dozen to in rare cases a few thousand), with slight modification, from one database to another. It's a sort of custom partial archiving process that would be triggered from a web-based admin application in plain old ASP (not .net alas). Records in the target db would be identical except:
-- the primary key in the source table, an identity field, would be just an integer in the target table
-- the target table has an extra field, an integer batch ID supplied by the web application that triggers the process
It's a simple, if not efficient matter to do it within the web application: query the source table, suck the records into memory, and insert them one by one into the target db. This will be an infrequent process which can be done at off-hours, so a bit of inefficiency is not the end of the world. But I wondered if there is a more sensible, orthodox approach:
-- Could this process be done, and done efficiently, as a stored procedure with the batch ID passed as a parameter?
-- Is there any way to do a bulk insert from a recordset or array in memory using plain ASP, ADO and SQL? And if so, is that better than inserting records one by one?
I realize that the ASP.NET tableadapter and dataset features might provide a good solution, but in the short run I can't rewrite the whole application. Advice on the best general approach from an ASP-ADO platform would be appreciated, and I will try to figure out the details.
View 3 Replies
View Related
Nov 29, 2007
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
View 1 Replies
View Related
Jan 26, 2007
Hi,I'm writing a telecom billing system where it is necessary to import csv delimited files into a sql server table.
1) The csv files to import differ in column arrangement i.e SQL server table arrangement = A, B, C, D whilst CDR = - , A, -, C, D2) The csv files are delimited via different characters i.e some are del.. by commas some by semi colons
I can import a CSV file from my application using a BULK INSERT query but only using CSV that resembles the structure of the table exactly
How can I pass a CSV to the table and state where I want each CSV column to fill a column in the SQL SERVER table
Below is how I am managing to pass a CDR to the SQL table that I have pre manufactured to resemble the structure of the SQL table Is there a way I can state which values from the CSV I want to pass to corresponding columns in the SQL table:
string conString = @"Provider=SQLOLEDB;Server=(local);Database=Billing;Uid=sa;Pwd=sa;";
for (int i = listBox1.Items.Count - 1; i >= 0; i--)
{
string strSQL;
OleDbConnection objConnection = null;
OleDbCommand objCmd = null;
objConnection = new OleDbConnection(conString);
string cdr = listBox1.GetItemText(listBox1.Items[i]);
strSQL = "BULK INSERT Campaign FROM '"+ cdr +"' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '')";
OleDbDataReader objDataReader;
try
{
objConnection.Open();
objCmd = new OleDbCommand(strSQL, objConnection);
objCmd.ExecuteNonQuery();
objCmd = null;
objDataReader = null;
objConnection.Close();
listBox1.Items.Remove(listBox1.Items[i]);
}
catch (Exception ex)
{
objConnection.Close();
}
objCmd = null;
objConnection = null;
}
Regards
Simon
View 4 Replies
View Related
Jul 20, 2005
Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com
View 3 Replies
View Related
Dec 5, 2007
I am trying to use the ListChildren method to query the SSRS Catalog. I am using this as a dataset in a report. I'd like to see more columns, in particular the Description column, included in the dataset. Is there some way to tell ListChildren what columns should be included or is there some other method to obtain this information.
ListChildren returns ID, Name, Path, Type, CreationDate, ModifiedDate, CreatedBy, ModifiedBy, xmlns.
I'd prefer not to query the Catalog table directly.
Thanks
Tim
View 1 Replies
View Related
Jul 23, 2005
I developed a console application that will continually check a messagequeue to watch for any incoming data that needs to be inserted into MSSQL database.What would be a low-cost method I could use inside this consoleapplication to make sure the MS SQL database is operational before Iperform the insert?
View 7 Replies
View Related
