Re: Generate Insert Statement - Database Discussion Boards

Member 23350920-Jan-05 23:19

20-Jan-05 23:19

Hi,
I have a problem, I need to move large numbers of data/multiple tables from a Mainframe DB/2 to a SQL server.
What is the best possible way?
Can I use VB.NET and dataadapers to do this? The larger tables consists of more than 25 milj. rows.

Happy to get any kind of help

Regards
JPalm

Re: Moving data

Colin Angus Mackay20-Jan-05 23:49

Colin Angus Mackay

20-Jan-05 23:49

I don't know anything about DB2, but if you have an ODBC driver for it you can do the whole thing in SQL Server using DTS (Data Transformation Services).

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Searching in a disconnected dataset

hendriks20-Jan-05 4:50

hendriks

20-Jan-05 4:50

Hi,

In a disconnected dataset (dsAdressen) I have to search for a specific record. I want to do this with it's Primary Key (PK).
At designtime I created a connection (cn), dataadapter (daPersonen) and dataset (dsAdressen)
When loading the form I do the following

cn.open
daPersonen.Fill(dsAdressen)
cn.close

When I try to search for a specific record with it's primary key I use code like

dsAdressen.Personen.FindByPersoonsID(1)

When I show the found value in a messagebox it's showing the right record

MsgBox(dsAdressen.Personen.FindByPersoonsID(1).Voornaam)

but my textbox, for which I set the databindings property at designtime in the property pane (txtVoornaam=dsAdressen.Personen.Voornaam), does not show the found value. I thought that by setting this property my textbox should automatically show the found value in the dataset.

I can use code like this to show the value in the textbox, but I should say that is not the way

dim dr ad Datarow
dr = dsAdressen.personen.rows.find(1)
txtVoornaam.text = dr("Voornaam")

I do not understand

Thanks
Hendrik Jan

Re: Searching in a disconnected dataset

descola22-Jan-05 22:37

descola

22-Jan-05 22:37

Try using a CurrencyManager

Dim cm as CurrencyManager = CType(Me.BindingContext(table), CurrencyManager)

table.DefaultView.Sort = "PrimaryKey"

cm.Position = table.DefaultView.Find(PK)

This should cause value in the selected row to display in the databound TextBox

Darrell A Escola, MCDBA, MCAD

Generate Insert Statement

Sebastien Lachance20-Jan-05 3:18

Sebastien Lachance

20-Jan-05 3:18

Is someone know the way to generate Insert script of a couple of selected record in a databae ? To after execute to script on another databse/server ?

sebastien.lachance.blogspot.com

Re: Generate Insert Statement

Colin Angus Mackay20-Jan-05 22:42

Colin Angus Mackay

20-Jan-05 22:42

Sorry, but that makes no sense. Can you try describing your problem again?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Re: Generate Insert Statement

Sebastien Lachance21-Jan-05 3:45

Sebastien Lachance

21-Jan-05 3:45

Sorry, I will try again. I have a Select statement that select some records. Then I want to send those record on another database. So I wanted to generate a Insert Script, that "Insert" those record.

My english is not so great, but I try to become better. Sorry. OMG | :OMG:

sebastien.lachance.blogspot.com

Re: Generate Insert Statement

Colin Angus Mackay21-Jan-05 4:00

Colin Angus Mackay

21-Jan-05 4:00

Sebastien_Lachance wrote:
My english is not so great

That's okay - I read some of your blog and it looks like your English is very good. I guess you were just typing in a hurry.

Sebastien_Lachance wrote:
I have a Select statement that select some records. Then I want to send those record on another database. So I wanted to generate a Insert Script, that "Insert" those record.

If the two tables have the same columns then the easiest thing is:

INSERT INTO [DestinationDatabase].[dbo].[DestinationTableName]
SELECT *
FROM [SourceDatabase].[dbo].[SourceTableName]

You can, of course, use any select statement that returns the data you need.

Does this help?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.

Any tutorial/link on developing applications for Windows CE .NET using C#

amittinku19-Jan-05 18:44

amittinku

19-Jan-05 18:44

Any tutorial/link on developing applications for Windows CE .NET using C#

Query

Yulianto.19-Jan-05 16:31

Yulianto.

19-Jan-05 16:31

I have a table with:
partId, location, quantity.
1 ,a ,12
1 ,b ,10

What is the query to make :
partId, quantity a, quantity b.
1 ,12 ,10

There are only two location, a and b.
Do you get it?

<italic>Work hard and a bit of luck is the key to success.

You don`t need to be genius, to be rich.

Re: Query

Mike Ellison19-Jan-05 17:26

Mike Ellison

19-Jan-05 17:26

Hi there. It depends on the database you're using. Here's one way in SQL Server:

select partId, sum(quantityA) as a, sum(quantityB) as b
  from 
 (
  select partId
         ,case (when location='a' then quantity else 0) as quantityA
         ,case (when location='b' then quantity else 0) as quantityB
    from myTable
  ) x
  group by partId

Re: Query

Yulianto.19-Jan-05 18:00

Yulianto.

19-Jan-05 18:00

Thanks a lot

<italic>Work hard and a bit of luck is the key to success.

You don`t need to be genius, to be rich.

a problem about resultset from a procedure

luobo19-Jan-05 16:01

luobo

19-Jan-05 16:01

Hi,I have a problem when I try to get resultset from a procedure.
if there is only select statement in my procedure,it works fine.
but if I add a insert statement in procedure,pRst->GetState() will return adStateClosed

develop environment:
vc6+MDAC2.7
sql server 2000

here is the procedure code:

create table tem(t_id int,t_date datetime)
go
insert tem select 1,dateadd(minute,1,getdate())
go
drop proc pTest
go
create proc pTest
as begin
--insert tem select 2,dateadd(minute,2,getdate()) --if I add this line,it won't work
select * from tem
end
go

here is the ado code:

_ConnectionPtr pConn;
_RecordsetPtr pRst;
_CommandPtr pCmd;

pConn.CreateInstance(__uuidof(Connection));
pCmd.CreateInstance(__uuidof(Command));
pRst.CreateInstance(__uuidof(Recordset));
....
try
{
pCmd->CommandText = "pTest";
pRst = pCmd->Execute(NULL,NULL,adCmdStoredProc);
//pRst = pConn->Execute("pTest",NULL,adCmdText);
//both of above return adStateClosed
}
catch (_com_error &e)
{
....
}

thanks

SET NOBROWSETABLE

Axsys19-Jan-05 15:50

Axsys

19-Jan-05 15:50

Can someone please explain what the SET NOBROWSETABLE off / on value does?

This setting doesn't appear in the Microsoft books online.

Thanks,

K

Kerry Johnson
Business Systems Consultant

Re: SET NOBROWSETABLE

Mike Dimmick20-Jan-05 1:44

Mike Dimmick

20-Jan-05 1:44

See KB 275483[^]. At the end of the article, it describes SET NO_BROWSETABLE ON.

There's some information for the SQL Server 2005 Beta in KB 885146[^].

Basically, the option is there to support client-side cursors. After a certain amount of experience my recommendation would be to avoid client-side cursors and indeed updateable recordsets in any form. Use static, forward-only recordsets and explicit INSERT, UPDATE and DELETE commands instead.

Be aware that SQL Server only supports a single forward-only resultset at a time on a given connection. ADO fakes multiple active resultsets by creating another connection under the covers - if you're not aware of this you can end up deadlocking with yourself. Also note that the second connection is not part of the same transaction.

Stability. What an interesting concept. -- Chris Maunder

Copy DataSet and change primary key in Relations

triton63019-Jan-05 12:56

triton630

19-Jan-05 12:56

Hi,

I am trying to copy a record from an existing record in the database and then save it as a new record. The dataset I am using has a couple of references to child tables which have primary key info as well. Does anyone know an easy way to do a copy, get a new primary key information and set the primary keys on the copy to the new one?

Thanks.

DateTime no Time transferred

cje19-Jan-05 9:43

cje

19-Jan-05 9:43

I have a simple C# app that uses an OleDBDataAdapter on an Access database. One of the fields is a date/time. I insert a new row into the table and the date portion of the date/time is there but the time is always "00:00:00" (the time fields are set properly in the DateTime object when I look at it in the debugger)

I tried setting the schema to us DBTimestamp and get an exception thrown during the database update 'type mismatch on criteria query'

any ideas on what could be causing or what I need to do to get both the date and time portions of a DateTime to trasfer to a database?

thanks!

Re: DateTime no Time transferred

Michael Potter20-Jan-05 9:51

Michael Potter

20-Jan-05 9:51

I had to set all my Access date formats (in the table designer) to 'General Date'. The format seems to dictate the output format. Other formats are not fully understood by the ODBC driver (guessing).

Query on 2 DB

Sebastien Lachance19-Jan-05 9:35

Sebastien Lachance

19-Jan-05 9:35

Is there a way to do a query on 2 differents DB ?
Please help !

Re: Query on 2 DB

David Salter19-Jan-05 11:16

David Salter

19-Jan-05 11:16

You can do this if you use linked servers.

Affordable and reliable hosting? Click here!

DataBind causing infinite loop?

Evan Schultz19-Jan-05 6:38

Evan Schultz

19-Jan-05 6:38

I am re-writing a control I was working on to use dynamically created template columns instead of using the designer for it. For one of my columns, I need a DropDown list, this worked in my old version fine. However, when writing the Databind event for the new control I am running into a problem where when I call the DataBind() on the listcontrol, it's causing an infinite loop.

It simply cuts out on the bind, and the method re-starts, I cant figure out why this is happening.

However, if I simply loop though all the items in the Dataset, and add them to the dropdown list that way, this works fine - however, much rather use databinding to do this.

private void TemplateControl_DataBindingDL(object sender,System.EventArgs e)
		{
			Debug.WriteLine("Starting DataBind:" + System.DateTime.Now);
			DropDownList lc;
			lc = (DropDownList) sender;
			string strSqlCommand;
			DataGridItem container = (DataGridItem) lc.NamingContainer;
			SqlConnection sqlConnection1 = new SqlConnection();
			DataSet dsTemp = new DataSet();
				strSqlCommand = "SELECT " + fieldName + " FROM V_T_TASKS GROUP BY " + fieldName;
			
			SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(strSqlCommand,sqlConnection1);
			sqlConnection1.ConnectionString = "...//connection string";

			sqlConnection1.Open();
			sqlDataAdapter1.Fill(dsTemp);
			
			Debug.WriteLine("assigning datasource...");
			lc.DataSource = dsTemp;
			lc.DataTextField = fieldName;
			lc.DataValueField = fieldName;
			Debug.WriteLine("assignedg datasource... binding");
			Debug.WriteLine("Rows count=" + dsTemp.Tables[0].Rows.Count);
			lc.DataBind();
			Debug.WriteLine("..bound");
			dsTemp.Clear();
			Debug.WriteLine("Cleared...");
			lc.Items.Add( DataBinder.Eval(container.DataItem,fieldName,"{0}"));
			Debug.WriteLine("Ending DataBind:" + System.DateTime.Now);
			lc.SelectedIndex = 0;
			sqlConnection1.Close();
			
		}

When I run this, it'll just get to the last two debug statements, then jump back upto the start and keep looping - not throwing any errors or exceptions either.

If I take out the databind and replace it with a normal loop to add each item in the dataset, it works fine.

any ideas?

SQL - Autonumber - Remove

Purple Monk19-Jan-05 1:50

Purple Monk

19-Jan-05 1:50

Alright everybody, hope you're all well. :->

Does anybody know how to remove the autonumber functionality from the id column in a table through a SQL Statement in SQL Server, it's obviously easy through Enterprise Manager Poke tongue | ;-P

.

I'm assuming it would be an ALTER TABLE statement but was wondering if anyone knows the syntax for this or if there is an easier method, like a stored procedure.

Help will be greatly appreciated.

Thanks Cry | :((

Re: SQL - Autonumber - Remove

David Salter19-Jan-05 7:57

David Salter

19-Jan-05 7:57

The following page has a some sample TSQL that does this.

Basically, it creates a new column of the same type as the identity, copies the data into the new column, drops the identity column and renames the new column to the name of the old identity column.

Affordable and reliable hosting? Click here!

Re: SQL - Autonumber - Remove

Purple Monk19-Jan-05 23:21

Purple Monk

19-Jan-05 23:21

Hi,
Thanks for the reply, however i think you may have forgotten to include the link.

Would be greatly appreciated if you could post it as i have found nothing in the way of doing this so far.

Thanks again,

Re: SQL - Autonumber - Remove

David Salter20-Jan-05 11:12

David Salter

20-Jan-05 11:12

Oops sorry. Blush | :O

http://www.windowsitpro.com/SQLServer/Article/ArticleID/22081/22081.html[^]

Affordable and reliable hosting? Click here!

Last Visit: 31-Dec-99 18:00 Last Update: 23-May-24 0:45

Refresh

ᐊ Prev 1...2155 2156 2157 215821592160 2161 2162 2163 2164 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.