T-Sql insert from table to table

Ok, need a little input on how to aproach this problem.

Transfering data from one table to another.

The first statement was easy...

INSERT INTO myTable(ID)
     SELECT ID
      FROM someTable

But now, need to get the rest of the data - row by row.

Selecting table columns:

userID, objID0, objID1, objID2........ objID19 Also status 0 - 19, max 0 - 19

Inserting table columns......

userID, objID, courseID = "something not in selecting table",  status, max

As you can see I am improving the database. (not my creation  )

So, the selecting table row (0) needs to be read 19 times (?) and the inserting table will have 19 inserts or 19 new rows.

If anyone needs more info, please ask.

And anyone can point me to a sample that this can be done, I would appreciate it.

Zath





0
Zath
9/8/2005 3:12:14 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
855 Views

Similar Articles

[PageSpeed] 35
Get it on Google Play
Get it on Apple App Store

Ok, I have come up with something I can do with code behind, but this is for T-sql.

So, I need correcting on the syntax here....

FOR(int i = 0; i <= 19; i++){
INSERT INTO objScores (studentId, objId, objStatus, objScoreRaw, objScoreMin,
   objScoreMax, courseId)
     SELECT studentId, objId + i , objStatus + i, objScoreRaw + i, objScoreMin + i,
    objScoreMax + i, "101"
      FROM cmiDataModel   
}  

I can do this if I rewrite the statement 19 times, but no way, never going there again!
But you can see what I am trying to achieve here.

So, a little input?

Thanks,

Zath
0
Zath
9/8/2005 3:51:37 PM
How many records are in your table?

Is this a once-and-done effort?  Honestly, if the record count is relatively low, the simplest soluton is a simple copy-and-paste of the INSERT statement, 18 times, winding up with 19 statements, and adjusting field names as needed.  There'd be very little debugging required.

If there are a lot of records (i.e. millions), there are a few solutions.  One would be looping through a cursor, then looping 19 times, building a dynamic SQL statement for each INSERT, and executing each one.  A similar approach would be the use of a #temp table instead of a cursor, selecting the top record out of it.


Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
9/9/2005 1:56:13 AM
Actually, since it is only going to be run once, I did repeate it.  Didn't like doing it that way, but no time to get it right.
But, should be done with a while statement.

Thanks,

Zath
0
Zath
9/9/2005 2:25:53 PM

Zath,

You werent far off with your script code. You can pretty much do the same thing in SQL

declare @i int,
 @sql varchar(2000)

set @i = 0

while @i <= 19
 begin
  set @sql = 'INSERT INTO objScores (studentId, objId, objStatus, objScoreRaw, objScoreMin, objScoreMax, courseId)
    SELECT studentId, objId ' + cast(@i as varchar) + ', objStatus' + cast(@i as varchar) +
    ', objScoreRaw' + cast(@i as varchar) + ', objScoreMin' + cast(@i as varchar) +
    ', objScoreMax' + cast(@i as varchar) + ', "101" FROM cmiDataModel'
  EXEC(@sql)
 
  set @i = @i + 1
 end


Nick

0
nick
9/9/2005 7:16:05 PM
Thanks!  Even though the script will only be run once (maybe), I just don't like repeating code when not needed.

I'll give it a try.

Zath
0
Zath
9/10/2005 6:21:07 PM
Reply:

Similar Artilces:

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

line break problem, I used the DetailsView to save an article to sql table, then I use label to read out from sql table, but couldn't break line.
Here is my code: <asp:Label ID="Label" runat="server" Text='<%# Eval("ServiceBody") %>'></asp:Label>Life is not easy but beautiful! use <asp:Label ID="Label" style="word-wrap:normal" runat="server" Text='<%# Eval("ServiceBody") %>'></asp:Label> and fix the widht of the colum in which you have placed this lable. like width=150px    If this post helps you, please mark it as Answer.Cheers,Muhammad Rizwan JavedSr .Net DeveloperAim 168, Dubai, UAE. Use ...

SQL
I am getting info from one table, CalibrationReview, that is not inanother table, tblEquipments. SELECT EquipmentNumber, Model, SerialNumber, Make, CalLabName, CalDateFROM CalibrationReviewWHERE NOT EXISTS (SELECT AssignedID FROM tblEquipments WHERE AssignedID = CalibrationReview.EquipmentNumber) Now, I need to take these rows and INSERT them into tblEquipments,but with some conditions. tblEquipments has some contraints, so, the following needs to be done: Using dbo.CalibrationReview.EquipmentNumber, get CalibrationMaster.TestTechnology where dbo.CalibrationReview.EquipmentNumber = dbo.C...

Submit Form to insert data into SQL and display with a gridview (edit as well) below how do i insert data into sql table?
I have an asp.net page that has a simple form where a user can submit customers information. Below that I have a gridview which displays customer information submited by the user who is logged in. I have the gridview displaying, sorting and updating as I want however I can't figure out how to bind a button click to insert data into the customer table and then have it refresh the gridview when they submit. Here is the basic code; (I am using AJAX for a calendar when a user wants to edit an appointment date) &lt;div class="leftBar"&gt; <div align=center&...

SQL Server insert query
When I am inserting into a table, I have some values which I am finding are "N/A" which obviously come from a source which allows it ie nvarchar column)  I need to check the values before entering into the column, and if it is not an integer, converting it to the value '00000000' ie a similar thing would be isnull(columnname,00000000), but in this case, not checking for if it is null, but rather if it is an integer. Does anyone have any ideas? Take a look at http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-awareBeware of ...

Wanting to take info from one SQL table and update to a seperate SQL table
Anyone have an idea of how I can take information from one table and edit it and insert into another SQL table.  I am using stored procedures and would like to continue to do so. Here is a copy of what I have so far:   <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" SelectCommand="mp_Customers" SelectCommandType="StoredProcedure"></asp:SqlDataSource> </div> <h2><strong><font color="#C00000">Please enter a valid account number:</font></strong><...

How can i import records from a sql server table in project1 to another sql server table in project2
Hi, i have a table with all employee bio-data in a completed project. Iam now working on another project with a table that needs the same data and here iam talking about 300 records that rarely change. Instead of re-entering this data in this new table, i want to import the data from the completed project into a table in this new project. Does any one have any idea how to achieve that or is there a better option to do the same. One more thing iam realising here is that iam going to use this same data in very many applications and some one from one department is going to ...

INSERT data into SQL table... TableAdapter Commands or SQL Commands? Which is best?
I've been working on some unbound data forms, and trying to decide what is best when inserting and/or updating a record into a SQL table.    I can successfully use SQL commands to insert data from VS2008 into a sql table on the server, doing something such as... Dim conn As New SqlConnection("server=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=YourDatabaseName")Using conn conn.Open() Dim cmd As New SqlCommand("UPDATE tableName SET folderName = @folder WHERE id = @id", conn) cmd.Parameters.AddWithValue("@folder", txtFolder.Te...

is there an sql statement to insert data to one table and getting the data from another table
Ok I have a table that have general categories with just name and CatNo. Now what I want to know is when I create a client to move all the data in general categories to another table called categories that have the clientid to associate it with the client. I would appreciate for any help what is your question here??Shravan Addaypally MCP...

Inserting data in batch mode in a SQL Table using .NET
Hi, I have an ASP.NET Web Service that accepts a DataSet object passed to it. This DataSet will contain a large number of records in it's table. What I want to do (if possible) is insert all records in a SQL table in a batch mode (one go). Is this doable? Thanks,--Mazen depends on the size of the dataset.Kay LeeMySpace.com - http://www.myspace.com/kragieInfrastructure GroupMySpace.com- Code to live, but Live to code....

Inserted records missing in sql table yet tables' primary key field has been incremented.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails. I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table.  Recovering these records is not abig deal because i can re-enter them but ia...

Sql Insert information from 1 table into another on the fly. VB.NET
This is probably a really simple question, but hey! this is what this forums about I have this code, which is inserting data into 2 different tables from textboxes. The problem is. the "login" table autoincrements but the "details" table needs to have an id the same as the matching row in the login table (im using it for sessions for user profiles)The example here is of a registration page and it inserts your details as stated above. How do i get the value "user_id" from the second insert statement (which is an autoincrement so its not in the code) and inse...

Inserting Into a Sql Table
VWD 2005 Express.  Visual Basic.  Sql Server 2005. How may I insert a record into a table from code behind (I am doing this without any entries to a form).  The connection name is GoodNews_Extranet.  The table is Login.  The fields I want to write are UserId and dtTime.  Thanks for any help.  The insert command is: INSERT INTO [Login] ([SystemUserId], [dtTime]) VALUES (@SystemUserId, @dtTime) I will provide the @ parameters programmatically.  I just need to know the commands I should use in Visual Basic to actually execute the INSERT command.  ...

How to : Select dataset from table - create excel file as binary stream
Hi. I am working on a project that select dataset from sql database table and create excel file stream from the output table and put the stream into binary column. The reason why I do this is because it takes about 30 minutes to run the query for the dataset. So instead of showing the result table right away, I want to store the result table in excel format and user can come back later and download it without waiting 30minutes. When I worked on the same project that deals with pdf, crystal report has function "export to stream" so I was able to store it in binary column in t...

Web resources about - T-Sql insert from table to table - asp.net.sql-datasource

Package insert - Wikipedia, the free encyclopedia
In the United States, the Food and Drug Administration (FDA) determines the requirements for patient package inserts. Other national or international ...

Inserts Wallpapers & Photos on the App Store on iTunes
Get Inserts Wallpapers & Photos on the App Store. See screenshots and ratings, and read customer reviews.

Please Insert Coin - Flickr - Photo Sharing!
Explore arsheffield's photos on Flickr. arsheffield has uploaded 6582 photos to Flickr.

Ice Cube-I'm Scared (Insert)-The Predator - YouTube
Artist:Ice Cube Title:I'm Scared (Insert) Album:The Predator Year:1992

Google halts public map edits after prankster inserts image of Android robot urinating on Apple logo
Google sidelines crowd-sourced map-making tool to prevent embarrassing edits.

Dear [insert name], Qantas takes your complaint very seriously, honest
What's more annoying than being delayed nine hours on a long-haul flight between Sydney and Los Angeles, while stuck in a seat with a broken ...

Skype to insert advertising into calls
Skype to insert advertising into calls

ISIS inserts disturbing images into N.L. vote Twitter chatter
Note: The CBC does not necessarily endorse any of the views posted. By submitting your comments, you acknowledge that CBC has the right to reproduce, ...

Samsung Galaxy Note 5 now ships with warning not to insert S-Pen the wrong way
... YouTube unboxing video reveals that Samsung has begun shipping its Galaxy Note 5 handsets with a warning label, telling users not to insert ...

Updates to Google Docs & Slides let you insert & quickly edit images on iOS
Google is today rolling out updates to its Google Docs and Slides mobile apps bringing the ability to insert images directly from the app on ...

Resources last updated: 12/29/2015 11:14:53 AM