Inserts Overwriting

Overwriting Current Db
I'm new to Sql Server.
I am bringing down a db in Enterprise Manger through the DTS-Export Data feature. I bring it down into Access because I know how to work in Access. I make some changes to the db. Then export it back in to Sql Server then up to the the hosted database through DTS.
Well instead of overwriting the information it just added to it..So now my database has two of every field.
How do I upload a new db and have it overwrite the one that's up there.
I thought there was an option to "Drop before creating.." or something like that.

I'm in touch with the sysadmin to have him restore...which he has already done.

Any help would be appreciated.

View Replies !   View Related

---

Overwriting Database
I am very new to SQL, but I am now receiving a updates on a database from an online HD. My idea was that I could download the updated database and paste over the existing database in my SQL folder. For some reason that is not working. How should this database be updated. I would prefer not to have to drop and add everyday.

Possibly I could make a database that is not in any folder and then make an update to it from the newly recieved database, but I am not sure how that would be done.

View Replies !   View Related

---

Overwriting Entry In Table
Task:

To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry.

What's the quickest/cleaniest way to do this ?

thanks,
Clayton

View Replies !   View Related

---

Restoring Database Without Overwriting The Old One.
Hello,

I'm trying to create installer that installs database from empty backed up database.
The SQL script executed by installer:

RESTORE DATABASE [DU] FROM DISK = N'$TARGETDIR$DuTempDU.bak' WITH FILE = 1, MOVE N'DU' TO N'$DATABASE_DIR$DU.mdf', MOVE N'DU_log' TO N'$DATABASE_DIR$DU_log.ldf', NOUNLOAD, STATS = 10
GO

There $TARGETDIR$ and $DATABASE_DIR$ are MSI variables set on runtime. The REPLACE flag is not set, but DU database (non empty )still gets overwritten with empty database. Do I need to check manually if database exists before trying to restore it?

View Replies !   View Related

---

Db Backups Appending / Overwriting
When I create a db backup on our network using BACKUP DATABASE...


BACKUP DATABASE [TKKCommonData] TO DISK = N'G:SQL_BACKUPSTKKCommonDataTKKCommonData_DATA.bak' WITH NOFORMAT, NOINIT, NAME = N'TKKCommonData_DATA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10


I've specified the NOINIT so that it appends rather than overwrites the database, however the database is still overwritten.
Any idea how to get the database to backup and append to the set rather than overwrite the backup ?

Programmers HowTo's -- http://jhermiz.googlepages.com

View Replies !   View Related

---

DTS - Source Row File Overwriting When Need Append
The source row file is being overwritten rather than appended to in my data driven query tasks when a data error occurs. I have not changed the default which is supposed to be append. Is this a "feature"? I am using SQL Server 2000 with service pack 1 applied.

View Replies !   View Related

---

Overwriting SSIS Package Logs
Is there a way to set the SSIS log provider for XML files to overwrite each time the package runs?

View Replies !   View Related

---

Overwriting An Excel File Using SSIS
Hi All,
 
I created  a package which runs everydays and dumps the data into an excel file.
The problem iam facing is that -today the package runs and fills in the excel file,tomorrow it again runs and fills in the data without deleting the previous records.......
But i want it to delete the records already present and fill in the excel only with the new records...
 
Any help is greatly appreciated.
 
Thanks in Advance,
SVGP

View Replies !   View Related

---

Repeating Table Headers Overwriting Data
I'm using RS2000 SP2 and am getting an issue when exporting to PDF. If I have a table that spans more than one page and I set the RepeatHeaderOnNewPage to True, then on occasion the table header will be displayed on top of the first few rows of data. It does not happen on all the pages or all the time and I can not find any information on this issue. Has anyone come across this before and solved it?

View Replies !   View Related

---

Overwriting An Existing Database With One That Is Populated With Data
I am tring to figure out how to simplify the process of populating a database created by an application with the same database, only with data already in it.  So far i have created a backup of the database and used that backup file with SQL server management express to overwrite the existing database with that backup file on a new computer so the program will have data when initally installed for Demonstration purposes.  I was hoping there was an executable script that i could use, so that when someone wants a demonstration of our product, they can see its options and functionallity with data available.  Maby i am going about this the wrong way, i need to know if there is a  way that when our program is installed an executable can simply be run to populate our database with a backup of our sample database.  Any imput would be helpful.
Thanks.
Isaias

View Replies !   View Related

---

Restore Backup To Second System Without Overwriting Local Custom Views?
Is this possible?

here is the situation. I have a DB on one system. I back it up and then restore it to a second system. This second system I run reports off of and I want to create custom views that do not exist on the original system. Can I restore the
backup DB from the remote system without wiping out the custom views on the local system?

I have to do this this way as they won't let us create the views we want on the remote system so the only way we have access to run the reports is by restoring the backup locally.

TIA!

View Replies !   View Related

---

Copying All Rows From One Table Into Another Existing Table And Overwriting Data
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

any ideas on how i could do this easily?

thanks

View Replies !   View Related

---

SQL Inserts From XML
Does anybody know of any nice utilities to insert data from an XML file into a database. I'm developing an application which will output an XML file to a certain directory and would like to have it picked up automatically and the data inserted into various tables.

View Replies !   View Related

---

No Of Inserts
I am doing a simple IO Test with the below script ...

Just wanted to keep things simple and to check how many Inserts I can do on a given SQL Server.
I am running the below script from QA for 1 minute and then divide the No or rows inserted by 60.

Will it give me approximate results by duing this?

Actually the datafiles .MDF files are sitting on a single drive where the manufacturer specs shows that it will handle 130 IO's per disk. With the below script I am getting around 147 Inserts per second.

But my boss says that he is getting 2000 inserts per second on his laptop from a ...Am I missing some thing?

DECLARE @lnRowCnt INT
SELECT @lnRowCnt = 100000

WHILE @lnRowCnt > 0
BEGIN
SET NOCOUNT ON
INSERT INTO CTMessages..Iotest
SELECT @lnRowCnt , 'VENU' , REPLICATE ( 'V' , 4000 ) , 1000000

SELECT @lnRowCnt = @lnRowCnt - 1
END

Thx
Venu

View Replies !   View Related

---

Inserts Per Second
Just curios,

I have four 72 GB Drive on a RAID 5

Disk Specs
IO/Second = 130 per disk
Speed RPM = 15 K

When I did a load test of inserting data into a table
with four Columns

Col1 INT
Col2 VARCHAR(32)
Col3 VARCHAR(4000)
Col4 DATETIME

I could insert around 1044 Inserts per second where as
I thought I could do max of 520 Inserts ( 130 * 4 ) because
each disk can only take 130 Inserts multiplied by 4 Disks
gives me a theoritical limit of 520.

Also How does the Query Analyser Connects to the datbase
Server.. does it use ODBC

Thx
Venu

View Replies !   View Related

---

SQL TimeSpan And Inserts
I need to Add a Check in the database to ensure that user can only enter up to 20 entries to Database in a period of 10 minutes. Basically, to guard against people using scripts to add data to the database ( instead of using CAPTCHAE on the front end) what we want to do is restrict user to entering at most 20 transactions in 10 Minutes.How do I handle or do this in SQl Server 2005??
 
What I figure to do is right after I do an INSERT into the table, I Select the last 20 entries into that same table and then Calculate the Total time it took to add those 20 transactions and set the righ flag.
1) How do I select last 20 entries into a table??
2) How do I calculate the total time that elapsed between adding the first of those 20 records and the last??
Thanks in Advance

View Replies !   View Related

---

Two Inserts In One InsertCommand
I'm using a SQLDataSource and trying to do two inserts into two different tables with one InsertCommand, but it's not working. Here's the code I'm trying to use. Do you see anything wrong with the syntax? I keep getting an error that says error near ','  but I can't figure out why. Thanks
 
InsertCommand="INSERT INTO [OurProjects] ([Title], [Description], [Location], [Anchors], [Size], [Developer], [DesignBuilder], [Architect], [ImageName], [MapName], [ProjectTypeAbbrev], [Deleted]) VALUES (@Title, @Description, @Location, @Anchors, @Size, @Developer, @DesignBuilder, @Architect, @ImageName, @MapName, @ProjectTypeAbbrev, @Deleted),
INSERT INTO [OurProjectsImages] ([OurProjectsID], [ImageMonthName], [SwfName]) VALUES (@OurProjectsID, @ImageMonthName, @SwfName)"

View Replies !   View Related

---

Identity Inserts
Hey All,
I was trying to use a typed dataset to create a very simple DAL. I found that the code generated for the INSERT statement includes an identity field the table has. That can obviously never work (unless identity_insert is set, which it is not). My question is whether it is possible to control this insert statement generation? Is there a property I am missing somewhere? My solution was to change the INSERT statement on the DataTableAdapter, but that seems awkward for me to have to do that..
Thanks,
Yuval

View Replies !   View Related

---

Multiple Inserts
Hi, I'm trying to create a form where new names can be added to a database. The webform looks like this:<body MS_POSITIONING="GridLayout">        <form id="Form1" method="post" runat="server">         Name:<asp:TextBox ID="newName" runat="server" />         <INPUT id="NewUserBtn" type="button" value="Create New User" name="NewUserBtn" runat="server"            onServerClick="NewBtn_Click">&nbsp;     </form>And the code behind looks like this:Public Sub NewBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewUserBtn.ServerClick        Dim DS As DataSet        Dim MyConnection As SqlConnection        Dim MyCommand As SqlDataAdapter
        MyConnection = New SqlConnection("server=databaseserver;database=db;uid=uid;pwd=pwd")        MyCommand = New SqlDataAdapter("insert into certifications (name) values ('" & newName.Text & "'); select * from certifications", MyConnection)
        DS = New DataSet        MyCommand.Fill(DS, "Titles")
        Response.Redirect("WebForm1.aspx", True)    End SubWhen I try to insert one name it works. When I try to insert a second name, it overwrites the old one. Why is that?Thanks.James

View Replies !   View Related

---

Next Inserts ID Number, Someone Please Help
G'day,
I have a table with a primary key being a bigint and its set to auto increment (or identity or whatever ms calls it). Is there anyway I can get the ID number that will be assigned to the next Insert before I insert it? I want to use that ID number within another field when inserted.

I hope that makes sense.

Thanks for any help.

Robbo

View Replies !   View Related

---

Speeding Up Inserts
Hello everybody,Just short question:I have tables, which are only log tables (very less used for selects),but there is a lotof writing.I would like to have as much speed as possible by writing data intothis tables.create table [tbl] ([IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Time_Stamp] [datetime] NOT NULL ,[Source] [varchar] (64) COLLATE Latin1_General_CI_AS NULL ,[Type] [varchar] (16) COLLATE Latin1_General_CI_AS NULL ,[MsgText] [varchar] (512) COLLATE Latin1_General_CI_AS NULL ,CONSTRAINT [tbl] PRIMARY KEY NONCLUSTERED([IDX]) ON [PRIMARY]) ON [PRIMARY]GOQuestion:Is it better for inserts,, to remove PK but leave identity insert?How to make this table optimized for writing?If I will set fill level of the table with 0%, will I winn much?Once information: this table will be deleted with old data, dependingon row count (oldest ID's will be deleted each night).Thank You in advanceMateusz

View Replies !   View Related

---

Speeding Up Inserts
according to the mysql manual, multiple inserts can be sped up bylocking the table before doing them and unlocking the table afterwards.is the same true of multiple inserts in mysql? if so, how would thetable be locked?any insights would be appreciated - thanks!

View Replies !   View Related

---

Large Inserts
Dear Experts,What is the best way to do a large insert WITHOUT having direct accessto the machine SQL Server is running on? For example, imagine I wantto insert something like 20,000 records. If I were to have access tothe server, I could BULK INSERT into a temp table and then insert intothe destination table. But if I can't create a file on the server touse for BULK INSERT, what is the next best alternative to doing lotsof 1 record insert statements?Thanks,-Emin

View Replies !   View Related

---

Inserts Failed Sometimes ...
We have installed a package developed by another company and sometimes(when the server is with a big rate of transactions), we are seeingthe following messages in package debug file:2004-04-22 14:23:27 3056:------------------------------------------------------------2004-04-22 14:23:27 3056: rlm03000: ls_PutOneRecord: Failed inserting[04113SO07236054]2004-04-22 14:23:27 3056: ODBCSetCursorName[3056]: Failed specifyingcursor concurrency to statement handle 1 for 'ls_rep_add'2004-04-22 14:23:27 3056: dbc01003: ODBCSetCursorName[3056]:ls_rep_add generated SQL error state: 240002004-04-22 14:23:27 3056: dbc01002: [Microsoft][ODBC Driver Manager]Invalid cursor state2004-04-22 14:23:27 3056: odbcFilterConnectErrors[3056]: set theiState to ODBC_DISCONNECT for <24000>2004-04-22 14:23:27 3056: Function Return Code [00001] ODBC_ERROR2004-04-22 14:23:27 3056: Operation [00000] ls_PutOneRecord2004-04-22 14:23:27 3056: Primary Return Code [00000] 000002004-04-22 14:23:27 3056: Secondary Return Code [0000000001] 000002004-04-22 14:23:27 3056:------------------------------------------------------------When this message appears in the app debug file we are loosing someinserts that the application do in the database.The MS SQL server configuration is:4 Pentium 760Mhz 4 GB RAMSQL 2000 Standard Edition 8.00.760 SP 4MDAC 2.7 driver ODBC SQLSRV32.dll 2000.81.9031.14 15/11/2002Any tip will be welcome,Thanks in advance,Reis

View Replies !   View Related

---

Massive Inserts
Currenlty I have huge amounts of data going into a table.
I'm sending an xmldoc and using openxml with a cursor to seed them.

the question I have is whether to let duplicate keyed data rows bounce
and then check @@error and then do an update on the nokeyed field
or
to do a select on the keyed field and then do an insert or update based on the
selects results.

Speed is my goal.

View Replies !   View Related

---

Best Way For Inserts And Updates
I would like to know what other developers are using as their approch to doing
updates and inserts against SQL server from VB/ASP/C++ apps.

To me it makes good sence to use stored procedures for all querys and selects.
But for updates and inserts I prefers to build a sql string and then execute it.
Creating a SP for inserts and updates would require having to create a parameter for
every database field in the stored procedure.

What is the best way for peformance? especially for MTS components.

I am writing a web app which I except a lot of concurrent users.
So I am concerned with speed as opposed to easier code modification.

Thanks a lot

AL







Any ideas/input appreciated.

View Replies !   View Related

---

Volume Inserts
We have a 4 processor 350 Hz NT 4.0 SQL server. Currently we have an application
that is inserting rows one at a time, each row insert is a separate transaction.
Currenty we are averaging 2500 rows a second with each row ( 56 bytes wide).
The data and the log are on one string of Raid disk. We plan to get another controller
and raid string to separate the data and the log onto separate controllers.
The developer is modifying the application to insert the data in blocks. What is the
impact to the transaction log? He seems to think that by inserting page blocks on
rows there would be less data going into the transaction log. Why would this be so?
Does anyone have any information on practical limits for inserts and log truncation
with similar machine configurations. He would like to try to get around 150,000 rows a second.
Has anyone accomplished inserts at this rate? What type of machine configuration?

View Replies !   View Related

---

Transactions And Inserts
What commands are used in ODBC to batch multiple inserts as
one transaction? Does this scenario make sense to anyone.
My co-workers via ODBC believes he is batching 71 data rows
for inserts as one transaction. When the transaction log
was truncating his job timed out. The code attempted to
retry the inserts however it failed due to duplicated rows.
Some of the rows that were supposely batched together as one
transaction were inserted. I feel like if the inserts were
indeed batched as one transaction when the job failed due to
timeout, all the inserts would have rolled out. Since this
did not happen each insert must have existed as a
separate transaction. My co-workers wonders if the act of the
transaction log truncating committed part of a transaction.
The performance monitor counters for transaction per seconds
were reduced a corresponding amount when he modified the code
to batch the inserts instead of doing single row inserts. He
definitely feels like the inserts are one transaction. Can
someone help us with our confusion?

View Replies !   View Related

---

Inserts Across Databases
Hi,
I have a procedure that I call on one database, and one of it's steps is to write to a table on another database, same server. the user exists in both databases, but i keep getting errors when i try and write to this second database. i know i can fix this by giving the user insert permissions on the table in this second database, but i do not want this for security purposes. any other ideas on how to accomplish this?

View Replies !   View Related

---

Updates, Inserts
I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent

View Replies !   View Related

---

Looped Inserts Sql
i have a {date value}
i have a {frequency value}
1 = yearly
4 = quarterly
12 = monthly

i need to select an item
then check the frequency

then do a loop insert based on the frequency

if frequency = 1

insert item, date into table where date = {date value}

elseif frequency = 4

Per item -- insert 4 new entrys
insert item, date into table where date = {date value}
insert item, date into table where date = {date value + quarter}
insert item, date into table where date = {date value+ 2 quarters}
insert item, date into table where date = {date value + 3 quarters}

<
' below is how i can calculate quartly values from a date iv vb .net just need to do the same within sql

Dim Quarterloop AS Integer
for QuarterLoop = 0 to 3 >
<= formatdatetime(dateadd("q", Quarterloop , MyDate),DateFormat.longdate) ><br>
< Next >

elseif frequency = 12
--- per item insert 12 new entrys
insert 12 items into the table looping from date and then in 12 increments of 1 month values

View Replies !   View Related

---

Single Row Inserts
I've got a package that needs to do various single-row inserts/updates throughout the flow of the package.  Each insert/update will use values from variables.

What is the best practice for doing this?  I was going to use a DFT with my source being a derived column transformation, but it expects a true data source.

I've also thought about just using the obvious -- a SQL command task -- by my experience with doing commands using single values is that it's very quirky and hard to get the data types and mappings to cooperate. 

I'm betting the SQL command task is my only option, but I wanted to check here first to see if others had other ideas.

Thanks in advance.
Jerad

View Replies !   View Related

---

INSERTs Given Me The BLUES
cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _

cstring = "INSERT INTO tblNEW (watch_id, service_center_num, repair_envelope, store_number"

cstring = cstring + "date_purchase, transaction_num, cust_fname, cust_lname, product_code"

cstring = cstring + "value_watch, failure_date, service_date, failure_code, repair_code"

cstring = cstring + "service_request, store_number_senditem, register_number, street_address"

cstring = cstring + "city, state, zip_code, area_code, phone_num, product_desc, service_center"

cstring = cstring + " work_to_bdone, auth_num, labor_cost, parts_cost, tax_cost, total_cost"

cstring = cstring + "notes, client_number)"

cstring = cstring + "VALUES('%" + txtWatchID.Text + "%','%" + txtcenter + "%'" & _

 

this is the error is get, but i did the same thing on a select statement and it works fine...do i need to add something to the string or what i am kinda confused and help would be great.....

Operator '+' is not defined for types 'String' and 'System.Windows.Forms.TextBox'.

View Replies !   View Related

---

Bulk Inserts
I'm trying to perform a bulk insert as shown below. It's problematic b/c it's not updating the identity fields correctly and we're getting dups. I think, but I'm not sure, that On Update Cascade would solve all this, b/c we wouldn't have to concern ourselves with even touching the identity fields, b/c they would be autogenerated. Can someone shed some light?? I'm pretty confused.


CREATE PROCEDURE AddMiamirecords AS

BEGIN TRANSACTION

--USERS
INSERT INTO [Undex_Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])
SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]
FROM Miami
WHERE not exists (select * from users Where users.Username = miami.AdvertiserEmail)
AND validAD=1


--PROPERTY
INSERT INTO [Undex_Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId])
SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId]
FROM [Undex_Production].[dbo].[miami]
WHERE miami.AdvertiserEmail IS NOT NULL
AND validAD=1


--ITEM
INSERT INTO [Undex_Production].[dbo].[ITEM] ([SellerID],[Price],[StartDate],[EndDate], [HomePageFeatured],[Classified],[IsClosed])
SELECT USERS.UserID, miami.PropertyPrice, convert(datetime,miami.FirstInsertDate), dateadd(day, 30, miami.FirstInsertDate)as EndDate, 1, convert (int,AdNumber) as Classified, 0
FROM USERS RIGHT OUTER JOIN
miami ON USERS.UserName = miami.AdvertiserEmail
WHERE validAD=1


--PROPERTYITEM
INSERT INTO [Undex_Production].[dbo].[propertyItem]( [propertyId], [ItemId])
SELECT Property.propertyId, ITEM.ItemID
FROM ITEM RIGHT OUTER JOIN
miami ON ITEM.StartDate = miami.FirstInsertDate AND ITEM.Price = miami.PropertyPrice AND ITEM.Classified = convert(int,miami.AdNumber) LEFT OUTER JOIN
Property ON miami.PropertyUnitNumber = Property.Unit AND miami.PropertyZipCode = Property.Zip AND
miami.PropertyCity = Property.City AND miami.PropertyStreetAddress = Property.Address1
WHERE validAD=1

--CONDOFEATURES
INSERT INTO [Undex_Production].[dbo].[CondoFeatures](PropertyId,[Bedrooms], [Area], [PropertyDescription], [Bathrooms], [NumOfFloors])
SELECT Property.propertyId, [PropertyBedrooms], [PropertySquareFeet], dbo.fn_ReplaceTags (convert (varchar (8000),PropertyDescription)),
[PropertyBathrooms], [PropertyTotalFloors]
FROM miami LEFT OUTER JOIN
Property ON miami.PropertyUnitNumber = Property.Unit AND miami.PropertyZipCode = Property.Zip AND
miami.PropertyCity = Property.City AND miami.PropertyStreetAddress = Property.Address1
WHERE validAD=1

--COMMUNITY FEATURES
INSERT INTO [Undex_Production].[dbo].[CommunityFeatures](PropertyId,[totalFloors],isComplete1)
SELECT Property.propertyId, miami.propertyTotalFloors,'0' as IsComplete
FROM miami LEFT OUTER JOIN
Property ON miami.PropertyUnitNumber = Property.Unit AND miami.PropertyZipCode = Property.Zip AND
miami.PropertyCity = Property.City AND miami.PropertyStreetAddress = Property.Address1
WHERE validAD=1

--UNITDISCLOSURES
INSERT INTO [Undex_Production].[dbo].[UnitDisclosures]([propertyId],[monthcondoasso])
SELECT Property.propertyId, [propertyassocfee]
FROM miami LEFT OUTER JOIN
Property ON miami.PropertyUnitNumber = Property.Unit AND miami.PropertyZipCode = Property.Zip AND
miami.PropertyCity = Property.City AND miami.PropertyStreetAddress = Property.Address1
WHERE validAD=1


--BROKERDEVELOPER
INSERT INTO [Undex_Production].[dbo].[BrokerDeveloper]([IsFSBO],[FSBOName],
[FSBOEmail],[FSBOWebsite],[IsDeveloper],[DeveloperName],[DeveloperWebsite],[IsBroker],[BrokerName],[BrokerageWebsite],
[propertyId],[brokercommission],[isComplete])SELECT
CASE AdvertiserType when 'FSBO' THEN 1 else 0 end,
CASE AdvertiserType when 'FSBO' THEN [AdvertiserName] else NULL end,
CASE AdvertiserType when 'FSBO' THEN [AdvertiserEmail] else NULL end,
CASE AdvertiserType when 'FSBO' THEN [AdvertiserURL] else NULL end,
CASE AdvertiserType when 'Developer' THEN 1 else 0 end,
CASE AdvertiserType when 'Developer' THEN [AdvertiserName] else NULL end,
CASE AdvertiserType when 'Developer' THEN [AdvertiserURL] else NULL end,
CASE AdvertiserType when 'Realtor' THEN 1 when 'Broker' THEN 1 else 0 end,
CASE AdvertiserType when 'Realtor' THEN [AdvertiserName] when 'Broker' THEN [AdvertiserName] else NULL end,
CASE AdvertiserType when 'Realtor' THEN [AdvertiserURL] when 'Broker' THEN [AdvertiserName] else NULL end,
Property.propertyId,[PropertyCommBroker],'0' as IsComplete
FROM miami LEFT OUTER JOIN
Property ON miami.PropertyUnitNumber = Property.Unit AND miami.PropertyZipCode = Property.Zip AND
miami.PropertyCity = Property.City AND miami.PropertyStreetAddress = Property.Address1
WHERE validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
COMMIT TRANSACTION
GO

View Replies !   View Related

---

Dynamic Inserts
Hello All,

I have to create dynamic insert statements for the table. For example there are DevTableA and ProdTableA tables. I worte a SQL to get the new records added in the DevTableA but are not there in ProdTableA. The result gives me a list of rows. These tables have a column 'LanguageID' and 'LText'

The compare result has records only for LanguageID = 0. One I see the compare result. I am suppose to create insert statements for LanguageID = 1,2,5 and 6 and update the Ltext for those languages. The Ltext for other languages is in spreadsheet.

Can anyone advice me how to create the insert statements from the comapre result and add 4 more insert statements for LanguageID = 1,2,5 and 6 with their respective Ltext.

So far I thought I can create #table. Looks like I need more than 1 # table.

Thanks in advance
-S

View Replies !   View Related

---

Generate Inserts
is there any easy way I can take a select statment
(such as select from payments where datetime>'20071122' and output a sql insert statment for these records?

I basically need to move a specific set of records from one sql server to another (both sql server 2005)
any suggestions for the best way to do this?

View Replies !   View Related

---

Multiple Dynamic Inserts With SQL
 I'm try to a multiple insert from one database to another by using this code:insert into [mpis].[dbo].[Residents] (acno,surname,name,ID,type)        (select top 30 acno,surname,name,id,type        from [PretoriaDB].[dbo].[WorkingDB])  but I keep on getting this error:Msg 8152, Level 16, State 9, Line 1String or binary data would be truncated.The statement has been terminated.  Can any one help!! 

View Replies !   View Related

---

Handling Nulls With Inserts
Hi,I've got a program that has a form.  On the form there are several fields which are optional that I would prefer to remain as NULL in the database (rather than a default value) if the user doesn't fill them out.  I'm using stored procedures for my inserts (sql 2000 + C#).  How do most people handle these situations?  Right now I have a seperate function which receives the parameter values as params and then handles creating the parameters and executing the stored procedure.  I'm not sure how to handle passing that I want one of those to be null, at least not without having multiple functions and possibly various stored procedures for different possibilities.Thanks.

View Replies !   View Related

---

Parametized Inserts In A Loop
Hi,
I have a for next loop that I am using to write some data into a table.
The problem I have is one of the fields is an @parameter
The relevant bit of VB.net code is:
Dim dbpm_theDateTime As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
           
            dbConnection.Open()
            For intCounter As Integer = 0 To ds.Tables(0).Rows.Count - 1
          
                querystring = "Insert into tblActionsToDo ([TheDT], [username]) VALUES (@theDateTime, '" & username & "’)â€?
           
                dbpm_theDateTime = New System.Data.SqlClient.SqlParameter
                dbpm_theDateTime.ParameterName = "@theDateTime"
                dbpm_theDateTime.Value = tmpstrDT
                dbpm_theDateTime.DbType = System.Data.DbType.DateTime
                dbCommand.Parameters.Add(dbpm_theDateTime)
               
                dbCommand.CommandText = querystring
                                
                rowsAffected = dbCommand.ExecuteNonQuery
               
               
            Next
The first time through the for next loop, everything works fine.  The next time though I get an error that @theDateTime has already been defined.  To be fair, it is true; but how do I get round this?  I've tried making the @parameter a string so that I can manipulate it as "@parameter" & intcounter, and thus have a uniquely-named varaible for every loop, but it doesn't work.
thanks for any pointers

View Replies !   View Related

---

Batch Inserts From DataTable
Please help guys,I have a DataTable filled from the parsing of a csv file by the OleDb text driver.This DataTable could on occassion contain in excess of 2000 rows.I want to be able to batch the inserts to my backend sql table and be able to recorver on errors during the insert.i.e, maybe send the first 500 rows to insert via an insert dynamic text.... really don't know the optimal insert technic to use.but, if I get an error on say the third batch, I want to be able to recorver, and not have to start all over again and continue the inserts from the batch that failed.....Please help... what is the best way to perform the inserts and how can I track these inserts and recorver on errors like power failures or sql server unavailable etc.Please help....

View Replies !   View Related

---

Nested Inserts With TSQL
Hi I have to strings that can both each contain an indeterminable length. These strings are

UserID
NoteID

and will contain something like

UserID = '1, 2, 3, 4'
NoteID = '4, 9, 18, 21, 23, 27'

However its not known the length of each and so we could have the reverse of the above.

I am to insert x amount of notes to one userId and vice versa, but I'm trying to figure out how to do both so the insert would resemble the following.

UserID NoteID

1 4
1 9
1 18
1 21
1 23
1 27
2 4
2 9
etc, etc

This is sp that does it and it works fine for just one or the other, I just don't have much experience in this kind of thing. The spInsertAssignedNoteDetail at the end simply makes the insert when I have both numbers.

Heres my attempt, but i'm just stuck as to where to go from here


CREATE PROCEDURE spInsertAssignedNotesByList
@FK_UserIDList NVARCHAR(4000) = NULL,
@FK_NoteIDList NVARCHAR(4000) = NULL

AS
SET NOCOUNT ON

DECLARE @Length INT
DECLARE @Note_Length INT

DECLARE @FirstUserIDWord NVARCHAR(4000)
DECLARE @FK_UserID INT
DECLARE @FK_NoteID INT


SELECT @Length = DATALENGTH(@FK_UserIDList )
SELECT @Note_Length = DATALENGTH(@FK_NoteIDList )

WHILE @Length > 0 or @Note_Length > 0
BEGIN
EXECUTE @Length = PopFirstWord @FK_UserIDList OUTPUT, @FirstUserIDWord OUTPUT


IF @Length > 0
BEGIN
SELECT @FK_UserID = CONVERT(INT, @FirstUserIDWord)


EXECUTE spInsertAssignedNoteDetail @FK_UserID, @FK_NoteID
END
END
--------------------------------------------------
GO

View Replies !   View Related

---

Inserts Gradually Slow Down
I'm inserting 2 million+ records from a C# routine which starts outvery fast and gradually slows down. Each insert is through a storedprocedure with no transactions involved.If I stop and restart the process it immediately speeds up and thengradually slows down again. But closing and re-opening the connectionevery 10000 records didn't help.Stopping and restarting the process is obviously clearing up someresource on SQL Server (or DTC??), but what? How can I clean up thatresource manually?

View Replies !   View Related

---

A Philosophical Question About Inserts
We have a SQL Server database that can accept data from severaldifferent projects throughout our lab. Is it better to (1) let eachdeveloper create their own prepared SQL statements for inserts or to (2)hide the schema from them and have them call stored procedures or viewsto get the data into the database?So for example, in the 1st case, the developer's code (e.g. PERL w/DBI/DBD) would prepare/execute:INSERT INTO table (col1,col2,col3) VALUES (1,2,3)and in the 2nd case they would prepare/execute:EXEC sp_insertdata, @val1 = 1, @val = 2, @val3 = 3... and the stored procedure does the insert.The main goal here is to isolate the developer from knowing the databaseschema. Only the 'database team' knows the schema and then any changesare done in one place (i.e. the view/stored procedure) instead of one ormany external applications.In the 2nd case, the schema is invisible to him/her but if a column wasadded to 'table', they would have to change their stored procedure callall the same.If there is there an easier way to do this please advise. Some of ourinserts are into tables of 100 or more columns (time-based data) and thecadence can be sub-second.Any help appreciated.Michael Husler

View Replies !   View Related

---

How To Get The @@Identity For Multiple Inserts?
Hi All,

Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @@Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.

Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users

INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@@Identity,'SomeOtherValue')

Thanks
Tanveer

View Replies !   View Related

---

Bulk Inserts Between Two Different DBMS
Hi folks,
I have a table located in DB2 nd I need to have a mirror image of this table on a SQL2000 database to avoid some server downtime problems.
Right now I have a solution using ADO.NET with Windows Services.

This windows service invokes itself everyday morning and pulls all the records from this table in DB2 to a dataset. Then I loop through the dataset and insert every record into SQL 2000 Table. This method is working fine ( It take approximately 2 minutes to insert 5000 records). I am just wondering whether there is any way to acheive bulk insertion in this case. Considering future growth of table I am not thinking the existing solution is neither elegant nor efficient.

Please let me know if I can achive the same either using XML, BULK INSERTS or any other mechanism in ADO.NET and please remeber that we are talking about data migration between different DBMS ( DB2 to SQL 2000)

Thanks,
Sai

View Replies !   View Related

---