I need to copy a column which contains a list of integers to a new column. Can someone please tell me how I can do this using a query statement? Thanks in advance.
There are several easy ways to accomplish what I am trying to do in a simpler way however (long story short) this is something I need to learn for a much larger task in the near future. O.K. I have a table named clients I'm trying to get started copying columns from the same table one by one. Lets say original table has these columns: Database kokomain -- table dbo.clients ID(pk) fname lname idate
I've created a blank temp table with the exact same schema and named it 'temp'. I've populated the id,fname and lname fields with the statement: Insert Into kokomain.dbo.temp (cid,fname,lname) select c.cid,c.fname,c.lname from kokomain.dbo.clients c
Now, how could I add the idate field to the table as a separate query? I've tried several variations of things like... INSERT INTO kokomain.dbo.temp (idate) select c.idate from kokomain.dbo.clients c where c.idate in (select idate from kokomain.dbo.temp) No dice.
The correct statement would be greatly appreciated Thanks.
I'm having difficulty bulk-copying a data file, using a format file, into a SQLServer 6.5 table with an identity column. The data file is tab-delimited, and does not include the identity column (I want it to be automatically generated).
When I execute the following:
bcp DB1.dbo.WORK_TABLE in WorkTable.txt -Usa -P -fWorkTable.fmt -SSERVERX'
I receive this message:
Starting copy... BCP copy in failed
I've had no problems loading a similar table without the identity column.
Can anybody help? Here is the table definition & bcp format:
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1] and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
I need to update a table by copying a column from another table(having the samestructure, but on another database), from the record having the sameprimary key.1 - What is the correct query?2 - I tried copying them record by record, but the datatype is ntext,(it displays <longtext> in the result pane), and trying to update it results in thefollowing errormessage:The text, ntext, and image data types are invalid in this subquery oraggregateexpression.I tried variations of the following:UPDATE TABLESET column0 = (SELECTcolumn0FROManotherDB.dbo.TABLEWHEREanotherDB.dbo.TABLE.column1 = column1)WHEREanotherDB.dbo.TABLE.column1 = column1
i want to copy all stored procedures in one drives. if i do manually it will take whole day. i have to change udd length in all sps.so please anybody give sugessions
Hi, I have been having fun and games for well over a week now trying to get an xml file copied into an sql file, but still have no joy. I and using the bulk copy to do this and think I am close to solving it but just need a final push in the write direction. In green below is the full source code, I think that the trouble is with the try statement part here:
Try bulk.WriteToServer(xd)
This comes up with the error : Unable to cast object of type 'System.Xml.XmlDocument' to type 'System.Data.IDataReader'
Basically how do I get the XML data into a reader (or IDataReader) format so that the writetoserver command can interpret it
I would be so greatful if someone could help resolve this it is becoming increasingly more frustrating Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim objXML Dim objXSL ' destination table
Dim connectionString As String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionString Dim myNewConnection As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connectionString) myNewConnection.Open()
Dim productreader As XmlReader Dim bulk As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(myNewConnection) bulk.DestinationTableName = "Product" Dim productcount As Integer = 0
Dim settings As New System.Xml.XmlReaderSettings() settings.IgnoreWhitespace = True settings.IgnoreComments = True
Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml")) Using reader As XmlReader = XmlReader.Create(xs, settings) While (reader.Read()) If (reader.NodeType = XmlNodeType.Element And "product" = reader.LocalName) Then productcount += 1 End If End While End Using Response.Write(String.Format("found {0} products!", productcount))
Using bulk bulk.DestinationTableName = "Product"
Dim xd As New System.Xml.XmlDocument() xd.Load("C:Documents and SettingsSimonMy DocumentsVisual Studio 2005WebSitesWebSite1App_Dataxmlfile.xml") Dim xr As New System.Xml.XmlTextReader(Server.MapPath("~/App_Data/xmlfile.xml"))
bulk.ColumnMappings.Add("TDProductId", "TDProductId") ' map First to first_name
bulk.ColumnMappings.Add("name", "name") ' map Last to last_name
bulk.ColumnMappings.Add("description", "description") ' map Date to first_sale
bulk.ColumnMappings.Add("imageUrl", "imageUrl") ' map Amount to sale_amount
bulk.ColumnMappings.Add("productUrl", "productUrl") ' map UserID from Session to user_id
i have sql local database in the application . I want to copy the table from one local database to another. here the detination table is already created with one field which is incremental and other field is image and some other fields are text. any solutions on how to do it
Hi all, I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!
I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.
I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also. Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.What is the easiest way to do so?
Hi, I'm trying to insert one value (an order) into the table Order and (via a for-loop) all the products in that order in the table Product, hence, one order can have multiple products (and must have at least one). I have an automatically increased value for the OrderID as the primary key for Order, and I have a foreign key named OrderID in the Product table. So far, I _think_ everything's logically correct. However, I don't understand how to retrieve the OrderID to be able to insert it in the Product table upon insertion. I guess this is done all the time, but the only solution I can think of is to make a new SQL Command, asking for the just created OrderID to use it in the SQL Command for the products' for-loop. I'm sure that's a bad idea. :-) Can I use relationships or so to make this automatically updated (that is, to have the Product table "check for" the OrderID and insert the OrderID upon insertion of the Product row(s))? I hope this is clear to you. Thanks in advance for all help! Pettrer
Folks, I need a help here. We are running our production database on SQL Server 7.0. We are in a process of switching the production database server to another server hardware. I was wondering, How can we copy the Logins/Passwords from my old server to the new one. Well this is very important to have all the old logins and passwords on the new server to have anybody login to it. I know, Doing Export will copy the logins. But would it allow the same old password to be on the new server?
I have a SQL Table called Consumers that contains 495,037 records. The record size is about 350 bytes and it has one field that is a varchar (255). I am using DTS to copy this file from SQL (7.0) to Access (97). Last week this file had about 216,000 records and the DTS Process worked okay. Today, it has 495,037 records and the DTS Process appears to lose some records (about 26,000) during the copy. In the DTS Package the SQL query is just a straight SELECT statement with no WHERE condition.
During the DTS Process the SQL Consumers Table is being copied to and Access Table. The error I am getting during the DTS Process is "Error at Destination for Row # 468608".
When I open the Access Table it shows 468,608 as the actual number of rows in the Access Table and shows ConsumerID 675820 (primary key, identity field) for this particular record.
When I link to the SQL Table via Access the Record # 468,608 shows ConsumerID 643852 and the last record shows Record # 495,037 and ConsumerID 675820.
This tells me that some records are not being copied over during the DTS Process. The last record on the SQL Table is on the Access Table. So there are probably 26,429 (495,037 on SQL Table minus 468,608 on Access Table) that are not on the Access Table.
The DTS Select Query does not have a Where Condition. Do you think some records are being lost because of the size of the SQL Table?
There is an Advance Tab on the DTS Properties Page; there are some options like 'Insert Commit Size', Fetch Buffer Size', 'Use Fast Load', 'Keep NULLS', and 'Check Constraints' that I need to take a further look at. Have you used any of these options and do you think any particular one may help my problem?
There is also and 'Exception File Name' option. I will give this a try next week to see if it will write the problem records to disk.
I have been trying to copy a VIEW from one database to another, but SQL 7 doesn't let me. I use the DTS wizard and it looks like it happns but then it doesn't appear, and I have tried to refresh. Can someone please tell me what I am doing wrong?
This may seem like an easy question, but I have a DTS package that I need to copy to another SQL server. Is this done easily? Can it be done (i think so?).
I have an 8 GB SQL 6.5 database that I need to move to another server. I know I can create the devices and the database on the new server exactly as they were created on the old server then replace the new DAT files with the old (after shuting down SQL Server). The issue I am running into is that the sort order and character sets on the two servers are different. Will this have an effect if I copy the DAT files? Am I better off using BCP? I'd hate to use BCP because of the time that it will take to move the files.
Does anyone know how to move or copy DTS Package? That I need to do: I would have to move database from one server to the other and last thing I want to do is recreate DTS packages from scrach. I could not find any way of transfering DTS packages.
I have 2 databases on the same sql server. Both have the same tables. I need to copy the contents of tableA from the source database to TableA on the destination database. Can I do this using a SQL script or some kind of script, and if so how and what would the syntax be.
I have a database called marketing in it i have a table called products and right now there are five products in the table with product_id as 8003,8004,8005,8006,8007 i want to create the same table in the database but my product_id should start from 1 and i only want three products from the old table to be copied into the new table any idea how to make this happen.
I have a SQL db on my server in internet the server and my DB is online I want to copy mdf file to my computer via FTP but I get an error that file is in use and I can`t copy it
I wanted to know is there any way that i copy information in that MDF fle to another DB on my computer
I was trying to find the fastest way to COPY a 50G DB from our production server to our test server. I was testing this on our test server and had a simple question.
Attach/Detach is out since I can only move not copy a DB using this method. export/import, bcp and DTS take too long and are not indented for this anyways.
so, I was left with Backup and restore. so, I tried that on two different test servers. Took very long. so, I decided to be brave and try the following.
1. Take DB1 that's running on server1 OFFLINE 2. Manually copy all datafiles and logfiles from Server1 to server2. 3. Attach DB1 on server2.
It came up great. No complaints whatsoever. So basically, it's the same as attach/detach but instead of detaching the DB I took the Db offline and copied the files over. So my question...
Is this supported? Any chance of corruption on either of the servers? If a DB is offline, is copying datafiles and logfiles supported (I am bit worried if I might corrupt data in production). Do people use this method to COPY databases or do they stick with BACKUP and RESTORE?
Hi all, I'm in the process of cleaning up a very VERY poorly implemented system. One of the problems I'm tackling consists of :
1 - Database A 2 - Database B (different schema than A) 3 - A task which regularly polls A, checks for updated/new records and inserts them (with some transformation) into B
As you can imagine, the overhead of the polling is killing the system. What I want to know is :
What is the best way for A to notify B whenever there are changes to a particular table on A ?
I'm running 2000 but we will most probably upgrade to 2005.