Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Insert From Another Table

Im still fresh into the query language and was wondering if anyone could help me out

I am trying to insert one field from one table to another table, but the field has multiple records

Any ideas?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem? 

View Replies !   View Related
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.

View Replies !   View Related
Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From C# Webpage
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62

View Replies !   View Related
How To Write Query To Insert 10,000 Rows In A Table Using Insert Statement One Time
create table and inserting 10,000 row values at a time using single insert statment

ex: I want to create table employee having two coloumns
like employeeid , name

note : name can be static i.e : same name for 10,000 rows

employeeid is unique

can any one write a query for me?

View Replies !   View Related
OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   

View Replies !   View Related
Insert Command Fails When I Want To Insert Records In Data Table
On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer  form that logged in users have access to.
it has 2 questions including a text box for Q1 and  dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
 Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<asp:DataList ID="SideBarList" runat="server">
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
<SelectedItemStyle Font-Bold="True" />
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<asp:WizardStep runat="server">
<table class="style1">
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
  Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
 Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
 SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());

View Replies !   View Related
How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

i have a file which consists data as below,
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?

View Replies !   View Related
Wants To Insert Into Multiple Table By A Single Insert Query
I wants to insert data into multiple table within a single insert query

View Replies !   View Related
Multiple Insert Call For A Table Having Insert Trigger

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1


View Replies !   View Related
Insert To Recordset Gives Different Date Format To Table Insert ????????
Help please!


I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?????


if request("dob") is "01/11/2007"       (1st november 2007)



set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset") "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")



11 jan 2007 stored in table




set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc


results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.


This is on a windows2003 server, sql2005,iisv6, asp.netv2


I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.





View Replies !   View Related
INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

Hi...  I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
Problem: When I run the package in my local machine and update  local SS DB/table - new records writes OK in the table.  BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs.  AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
What I am trying to do is very simple -  Add new records in a SS table using SSIS .  I only care for new rows and  not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER -  using SELECT stmt
2.  I have LoopUp component that will look for NEW records -  Directs all rows that don't find match and redirect rows (error output).
3.  Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4.  I send the error rows (NEW rows) into OleDB destination
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
I am not sure what I am missing.  And I do not see error in my package either.  It is not failing.
Thanks in advance!

View Replies !   View Related
SSMS Express: Create TABLE &&amp; INSERT Data Into Table - Error Msgs 102 &&amp; 156
Hi all,

I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC.  I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got  Error Messages 102 and 156 when I did "Parse" or "Execute".  This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"?  Please help and advise.

Thanks in advance,

Scott Chang

USE MyDatabase
CREATE TABLE dbo.LabResults
    SampleName varchar(25) NOT NULL,
    AnalyteName varchar(25) NOT NULL,
    Concentration decimal(6.2) NULL)
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
           VALUES (1, 'MW2', 'Acetone', 1.00)
           VALUES (2, 'MW2', 'Dichloroethene', 1.00)
           VALUES (3, 'MW2', 'Trichloroethene', 20.00)
           VALUES (4, 'MW2', 'Chloroform', 1.00)
           VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
           VALUES (6, 'MW6S', 'Acetone', 1.00)
           VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
           VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
           VALUES (9, 'MW6S', 'Chloroform', 1.00)
           VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
           VALUES (11, 'MW7', 'Acetone', 1.00)
           VALUES (12, 'MW7', 'Dichloroethene', 1.00)
           VALUES (13, 'MW7', 'Trichloroethene', 1.00)
           VALUES (14, 'MW7', 'Chloroform', 1.00)
           VALUES (15, 'MW7', 'Methylene Chloride', 1.00
           VALUES (16, 'TripBlank', 'Acetone', 1.00)
           VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
           VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
           VALUES (19, 'TripBlank', 'Chloroform', 0.76)
           VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.


View Replies !   View Related
INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO

View Replies !   View Related
Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table
I have the following query in sql 2005:

PROCEDURE [dbo].[uspInsert_Blob] (

@fName varchar(60),

@fType char(5),

@fID numeric(18, 0),

@bID char(3),

@fPath nvarchar(60)





SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)

SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)

AS Document"

This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters.  The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath)  so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?

View Replies !   View Related
Is It Possible To Insert Data Into A Table From A Temporary Table That Is Inner Join?
Is it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?

View Replies !   View Related
Stored Procedure For Insert Data From One Table To Another Table
     I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.

View Replies !   View Related
How To Insert Rows To Table B Based On Values In Table A.

I need Insert rows in the OrderDetails Table  based on values in the Orders Table
In the Orders table i have a columns called  OrderID and ISale. 
In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the  Orders Table, anytime the  ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the  SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD

Also for each value in the OrderID Column of the  Orders Table, anytime the  ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the  SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.

I need a SQL Script to accomplish this. thanks

View Replies !   View Related
How Do I Copy / Insert A Primary Key Value Of One Table Into Another Table?

Hi i have set up  two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile  table with Id set as the Primery Key as Identity) I
want the user to be able to be able to return to their account at a later date
and then post multiple reviews of different bands they have seen at a later date.
I kept the tables in my example very simple so I could get my head
around the concept, but generally, I want to connect the Id (PK) value in
User_Profile table to the User_Id filed in the User_Review table,
so every review that user writes, will be connected directly to their Id.  

Any help you could give would be fantastic a i have no idea where to start!!!



Id   int,  ( as primary Identity Key)





I have a second table called User Reviews


Revew_Id   int ,  ( as primary Identity Key)

User_Id  int, ( I want this to contain the Id value in
the User profile Table)







View Replies !   View Related
Trouble With Basic Table To Table Insert
I'm a horrible noob everyone, so my apologies come up front.

I know Oracle allows me to just do an

SELECT column1, column2
FROM table2

But I cannot get this to work in MS SQL 2000. (See question #3)

Here's my script that I'm trying to execute from Enterprise Manager:

From this table:


Where this is a sample row of data:

A123456789 THOMAS, CHARLES B. 009/11/01 M05/04/511G

I want to run that data through some substrings etc. and dump it into another table like this:


Using this script so far:


I keep getting this error:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

QUESTION #1 - Is there an equivalent in MS SQL 2000 to Oracle's TONUM function?

So, to get around this I change all the fields in the receiving table (ISIS_DATA_PREP) to a varchar so there is no conversion necessary at this time.

Now I get this message:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

I look up this error on MS's knowledge base and get something along these lines:

...Because the truncated string is shorter than the maximum length, the char column that does not allow a null value and the char variable is padded with trailing blanks while the varchar column will not store trailing blanks....


What am I doing wrong on the field structure on the receiving table of the input command? I experimented by setting all fields to VARCHAR(25) so it would accept more data and not truncate, but still get the same message.

My main question was for the syntax for INSERTing into a table from another table the rows in MS SQL. Basically copying the data. I have it for Oracle, but MS SQL doesn't seem to take it in that format.

Sorry for the absolutely massive post. : ) And thanks for any feedback.


View Replies !   View Related
HT Insert Table A Record Fields Into Table B
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.

How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)



View Replies !   View Related
Insert And Reformat Data - Table A To Table B
I have a table called #table1 which is populated as in the example below.
I would like to write a selectinsert statement based on #table1 that populates #table2 like in the #table2 example.
Note #table2 is a fixed table that follows the structure below.

Can any of you T-SQL gurus help me with my problem?
Any help will be most appreciated.

Please paste T-SQL into query window
--max of 5 orders
custID nvarchar(6),
dateorder [datetime] NULL,
order1 nvarchar(2),
order2 nvarchar(2),
order3 nvarchar(2),
order4 nvarchar(2),
order5 nvarchar(2)


Insert into #table1
select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null UNION all
select '012345','2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03' UNION all
select '012345','2008-04-21 00:00:00.000' , '01', '06', null, null, null UNION all
select '012345','2008-04-22 00:00:00.000' , '01', '02', '05', '07', null UNION all
select '012345','2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all
select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null UNION all
select '987654','2008-04-22 00:00:00.000' , '01', '02', '05', '16', null UNION all
select '987654','2008-04-23 00:00:00.000' , '03', '06', null, null, null

select * from #table1

--This is the table i would like to insert my data into
--max of 5 orders in 1 day
--it does not matter what date the date order was made the 1st date would appear in dateorder1 and so on...
custID nvarchar(6),
dateorder1 [datetime] NULL,
order1_1 nvarchar(2),
order1_2 nvarchar(2),
order1_3 nvarchar(2),
order1_4 nvarchar(2),
order1_5 nvarchar(2),
dateorder2 [datetime] NULL,
order2_1 nvarchar(2),
order2_2 nvarchar(2),
order2_3 nvarchar(2),
order2_4 nvarchar(2),
order2_5 nvarchar(2),
dateorder3 [datetime] NULL,
order3_1 nvarchar(2),
order3_2 nvarchar(2),
order3_3 nvarchar(2),
order3_4 nvarchar(2),
order3_5 nvarchar(2),
dateorder4 [datetime] NULL,
order4_1 nvarchar(2),
order4_2 nvarchar(2),
order4_3 nvarchar(2),
order4_4 nvarchar(2),
order4_5 nvarchar(2),
dateorder5 [datetime] NULL,
order5_1 nvarchar(2),
order5_2 nvarchar(2),
order5_3 nvarchar(2),
order5_4 nvarchar(2),
order5_5 nvarchar(2)

Insert into #table2
select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null, '2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03','2008-04-21 00:00:00.000' , '01', '06', null, null, null,'2008-04-22 00:00:00.000' , '01', '02', '05', '07', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all
select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null ,'2008-04-22 00:00:00.000' , '01', '02', '05', '16', null,'2008-04-23 00:00:00.000' , '03', '06', null, null, null , null, null, null, null, null, null, null, null, null, null, null, null

select * from #table2

drop table #table1
drop table #table2


View Replies !   View Related
How To Insert Derived Table To Temp Table
I am executing some statment as below in this.

exec('with cart as (SELECT Row_number() over(order by '+@SortCondition +') as rowindex, c.ContentID,convert(varchar(100),c.ModifiedOn,101) as ModifiedOn, c.Name, UnitCost as SalePrice,c.Currency,isnull(DiscountedPrice,0) as DiscountedPrice,
isnull(SubscriptionOnly,0) as SubscriptionOnly
from tbl_Content c ' + @JoinCondition + ')'
+ ' select * from cart where rowindex between cast('+ @StartIndex + ' as int) and cast('+ @StartIndex + ' as int) + cast(' + @NoOfRecords + ' as int) ' + '-1 order by '+@SortCondition)

in the above statment(which is part of SP) passing @SortCondition ,which may come as title,SalePrice,DatePublish from dotnet code.
In the sp there are multiple statment depending upon some condition we are creating @JoinCondition.So in One case the Joining conidition will not have SalePrice as Origionl colum but derived column.I have to use the above statment and handle in Sale Price condition becuase in saleprice case it is alias column name.

How can i do that.
or the last statment I but in one more table if @SortCondition='salePrice asc/Sale price desc' with ordering.
Plz help me out

View Replies !   View Related
Insert Data From A Table Into A Table Of A Different Database

I have two databases in SQL Server and they both have almost the same tables. I want to insert the data of a table from the first database into a table with the same name, but from the second database. How can i do this? 

View Replies !   View Related
SQL - Insert Into Table Where Not Exists In Another Table
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.CalibrationMaster.EquipmentID
Then take CalibrationMaster.TestTechnology and read tblTestTechnology.testTechnology and get
tblEquipments.testTechnology = OR 1 if not foundWHERE dbo.CalibrationReview.EquipmentNumber = dbo.CalibrationMaster.EquipmentID and CalibrationMaster.TestTechnology = tblTestTechnology.testTechnology
And similar for CalibrationReview.CalLabName.
tblEquipments.calLab = tblLabs.ID where tblLabs.LabName = CalibrationReview.CalLabName
I hope this is clear as I can write this in code behind, but it'smuch better using sql simply because it's faster and only needs tobe run once.
Inheriting databases and combining all of them to develop a large.Net management system is fun, huh?
Thanks for any input,

View Replies !   View Related
Insert Rows From Table A Not In Table B ??
I need help with the following query. How do I construct the following SQL query:

I need to be able to get all rows from Table A where the ID key is not in Table B

columns for both tables:
ID (Key)

Many thanks !!

View Replies !   View Related
Insert Values From One Table To Another Table
hi friends,

I need query to insert all values from one table to another table.

View Replies !   View Related
Insert Record Into Table From Another Table
Greeting All, Again I stuck from foxpro into sql.
could use some help.

I have 2 tables, Contact and Service.
In the contact table I have an ID number
that I would to insert into the Service table
based on the Service table user name.

I cant use a Foxpro replace all command with some
function attached to the script. I cant seem to find
a SQL version of what I would like to do.

So here is my layout. If someone might be able to point
me in a direction, I would be greatful

Contact Table "grid is the auto id field"
grid, Anumber, Name, Address

Service Table
grid, Anumber, Name, Address = add 'anumber' from contact table to service table where lives.

I will keep looking on my own. any help would be great

View Replies !   View Related
Insert Into Table From Another Table RESOLVED
I want to step through each record in one table and extract a School number and concatenate it to a date and insert it in another table. I get an error abour duplicate keys when I run my sp though. each school number is unique. I want to end up with the key of my new table like this

01/03/07*2253 etc

@dteWeekEnding nvarchar
select @dteWeekEnding +"'*'"SchoolNumber from tblSM_Schools

View Replies !   View Related
Insert Data Of Table 2 To Table 1,how?
Table 1:
cityId ,cityname,state
Table 2:

There are a large amount of data (Cityname and State)in table 2 is valueable,I want insert them into table1.

But I don't know how,anybody can help?what I should do and what I should notice?
Thank you!

View Replies !   View Related
Insert Data From One Table Into Another Table
I need to include data from one table to another. What I need is to take the info from a field in Table A and insert that into a field in Table B based on matching info in another field.

Basicly I ahve the following scenario.

Table ProductVariant with fields ProductID and DisplayOrder and Table ProductCategory with fields ProductID and DisplayOrder

I need to include the DisplayOrder info from Table ProductVariant into field DisplayOrder in Table ProductCategory based on a match in field ProductID

so If ProductVariant has the following records
ProductID  DisplayOrder
1000               12
1001               25
1002               42

And ProductCategory has
ProductID  DisplayOrder
1000               1
1001               1
1002               1

The DisplayOrder field in ProductCategory should be updated to look like

ProductID  DisplayOrder
1000               12
1001               25
1002               42

I have used SET to change values in field such as changing prices by using

UPDATE ProductVariant
SET Price = Price*1.05

to increase prices by 5% but cannot figure and have also used it to include text from one field to another within the same table but I cannot figure out how to do it when data needs to come from a different table and needs to be qualified based on yet another field

Any suggestions or directions to info that will help me. Everything I have come across so far is for more complex needs and doesn't suit my level of experience with SQL. I am using SQL 2005 Enterprise Edition



View Replies !   View Related
Insert From Another Table
 hi, this is my procedure and i want to insert into T2 if Kid is different
how can i get it
please give me some idea
CREATE PROCEDURE [dbo].[TTT]@id int ASinsert into T2 (ID,Kid,f1,f2)
select ID,Kid,f1,f2
from T1
where ID=@idGO

View Replies !   View Related
Insert Into New Table
I've successfully queried an existing table and loaded the rows into a dataset.  After some modifications I want to insert those rows into a different table.  But all the documentation seems to deal only with inserting data back into the original table.  Any suggestions?

View Replies !   View Related
Cannot Insert Row Into Table
Although I am able to delete a row from CatA table, I am unable to insert a row into the same table.
The rows are from 1 to 241,  I accidentally deleted row 41, together with row 42 which I wanted to delete.  But I can't reinsert row 41.
I see this message: 
INSERT INTO [dbo].[CatA]([CatA_ID], [LName], [Auto], [Advertising], [Antiques], [Art], [Astrology])VALUES(41, N'JSmith', '(null)', '(null)', '(null)', '(null)', '(null)')
Error: Query(1/2) Cannot insert explicit value for identity column in table 'CatA' when IDENTITY_INSERT is set to OFF.
Below are the settings for this table, I have tried to change the settings but I am prevented.
CatA_ID   =  Primary Key
 Parameter  and  Value: 
Identity  =     yes 
Identity Seed  =     1
Identity Increment  =   1
IsRowGuidCol  =   No
What do I need to do, how do I reinsert the row 41 or insert a new row in the future?
Please advise.

View Replies !   View Related
Can Not INSERT To The Second Table
Hi, everyone, I write a SP and the first table can be inserted succesfully, but the second fails.
However there are not any error message to occur, just passes it. Anyone can give me a tip?
I am a begineer of SQL.

CREATE PROCEDURE sp_FinalizePurchase
@Inv_shipAddress as varchar(50),
@Inv_shipCity as VarChar(50),
@Inv_shipProvince as VarChar(20),
@Inv_shipPostalCode as Char(6),
@Inv_shipPhone as VarChar(10),
@INV_billAddress as VarChar(50),
@INV_billCity as VarChar(50),
@INV_billProvince as VarChar(20),
@INV_billPostalCode as Char(6),
@INV_billPhone as Char(10),
@CUS_customerID as Int,
@Inv_invoiceID as Int Output

-- STEP 1
--Do the Insert

INSERT tblInvoice

SELECT @Inv_invoiceid = @@identity

-- STEP 2:
--Do the Insert
INSERT Into tblInvoiceLine
FROM tblBasket, tblProduct, tblMovie
WHERE tblBasket.productID=tblProduct.PRD_ProductID

View Replies !   View Related
Insert Into Table
I would like to populate a column in a table. I need a proc/query that will remove the first characters if they are "Microsoft". Here is a sample list of data (book_names):

Adobe Illustrator
Microsoft Visual Basic
Microsoft SQL Server Introduction
Transact SQL Reference
Microsoft Web Database Development

The end result should be as follows:

Adobe Illustrator
SQL Server Introduction
Transact SQL Reference
Visual Basic
Web Database Development


View Replies !   View Related
Table Insert
Why does this work in 6.5 but not in 7.0?
I keep receving "Insert Error: Column name or number of supplied values does not match table definition.", I'm loging on as 'SA'

CREATE TABLE facilities_elig (
Iden int IDENTITY (1, 1) not null,
ipia_audit_report_number_id int not NULL ,
ipia_audit_report_number varchar (12) NULL ,
ipia_id smallint NULL ,
audit_code char (2) NULL ,
proximity_group_id int NULL,
dapia_id smallint NULL ,
plant_id int NULL ,
plant_line_id int NULL ,
preliminary_schedule_flag bit not NULL ,
ipia_audit_report_sequence smallint NULL ,
audit_duration_type_id int NULL ,
priority_status_code_id smallint NULL ,
schedule_audit_date datetime NULL ,
block_out_start_date datetime NULL ,
block_out_end_date datetime NULL ,
number_of_audit_day int NULL ,
date_hud_authorized_audit datetime NULL ,
schedule_record_entry_date datetime NULL ,
plant_certification_start_date datetime NULL ,
schedule_comment text NULL ,
schedule_report_comment text NULL ,
last_update_date datetime NULL ,
last_update_user varchar (10) NULL

insert into facilities_elig
select ipia_audit_report_number_id,
from schedule_plan LEFT JOIN plant_proximity_group ON
schedule_plan.plant_id = plant_proximity_group.plant_id
where preliminary_schedule_flag = 1
AND schedule_audit_date Is Null
AND priority_status_code_id <> 7

View Replies !   View Related
Insert In A Table
I made this stored proc who receive the name of the table that I want to make a copy.

DECLARE @NoAutonumeric(18,0)
DECLARE @NoUserint
DECLARE @Namevarchar(64)
DECLARE @textevarchar(500)
DECLARE @Ajoutvarchar(8000)
/*déclaration d'un curseur pour les enregistrements de la table du log*/
SELECT id,nousager,nom,description FROM activite
OPEN cActivite

DECLARE @commandvarchar(8000)
select @command = 'CREATE TABLE dbo.'+@Nom+'(id numeric(18,0), nousager int, nom varchar(64), description varchar(500))'
execute (@command)

FETCH NEXT FROM cActivite INTO @NoAuto,@NoUser,@Name,@texte

while (@@fetch_status <> -1)
select @Ajout = 'insert into '+@Nom+' select '+@NoAuto+','+@NoUser+','+@Name+','+@texte
execute (@Ajout)
FETCH NEXT FROM cActivite INTO @NoAuto,@NoUser,@Name,@texte

My problem is in the insert it tell me that Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.

How can i do my isert correctly


View Replies !   View Related
Insert Value In Two Table

I want to insert record in two table at one time in diff databases.
i used insert for pubs t1 i want that when i press f5 the record should saved on both tables

View Replies !   View Related
Insert Into Table
I have a table with two columns. I have to insert data in one column from another table and in another column a general scalar value. As like the following..
insert into <table1>(column1,column2)values(select columnX from <table2>,XYZ)
But I couldn't do this because values clause doesn't support select statement. So,please suggest me how can I do this.....

View Replies !   View Related
Insert Into Table From Another
I need to insert data from one table into another table that have different column names and I only need to insert certain fields.
Should I use Insert into? And how do I reference the from table when the column names are different?

Insert into Clients (Clientname, address, phone)
from contacts (contactname, contactaddress, contactphone)
where contactname = 'DOS'

View Replies !   View Related
How To Insert Table
I have 2 table

One ---> tbCustomer with Columns CustId, CustName, Address
Two ---> tbOrders with Columns OrderId, CustId, Qty, ProductId

In my Application (Textbox) I was show CustName. Know I wan't To Insert tbOrders (OrderID, CustId, Qty, ProductId) with Parameter CustName In my Textbox. Please Help How To do it in MS Access.



View Replies !   View Related
Insert From A Table To Another Where
Need som help.

I have a table TEMP111 with columns CustNo,A,B,CustPhone

For each row in that table I want to insert A+B into a specific column in the table CUST IF CustNo OR CustPhone is in that table.

Madly happy about any help.

View Replies !   View Related
How To Insert More Than One Row In A Table
I have two table. Employee(EmpID, EmpArea, EmpCity,  EmpType) and Manager(EmpID, EmpType, Description).

I am trying to write a SP. Input of the SP are EmpArea, EmpCity, Description.

In this SP from the Employee Table I am getting more than one row of EmpID, EmpType  by passing the EmpArea, EmpCity as input.(suppose 5 rows).

Now I want to insert these 5 EmpID, EmpType in Manager tabel. Manager Table is already having some data. Description will same for all these records, coming as input for Sp.

How can I do this?


View Replies !   View Related
Insert Into More Than One Table
I have this select command

SELECT intUserId FROM tblUser


I want to insert every intUserId into tblUserBla,tblUserBla2

I know that i can do this

INSERT INTO tblUserBla (intUserId) SELECT intUserId FROM TblUser

But, how can i perform a insert into tow tables, in one statement?

View Replies !   View Related
How To INSERT SUM Into Another Table
Table T1 has columns Date, Company, Sales where Sales is a decimal.
Table T2 Has Company, SalesTotal
How do I SUM up from T1 the Sales of all of Company='C1' and INSERT the value into T2?
The following didn't work:

insert into T2

 values( 'C1', Select Sales=sum(Sales) from T1 where Company='C1')
PS: Is someone maintaining a page somewhere listing many examples.

View Replies !   View Related
Insert Into A Table
I made this Query to insert a row into a table :

INSERT INTO [AGUA2005].[dbo].[Autenticacoes]

([Data_Quitacao] --smalldatetime

,[Tipo] --nvarchar(1)

,[Inscricao] --int

,[Mes_Ref] --nvarchar(6)

,[Data_Venc] --smalldatetime

,[Valor_Dev] --money

,[Valor_Pago]) --money









with this erro :

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'VALOR_PAGO'.

What's wrong ? Another problem that I can't understand is that when I open the table the fields Smalldatetime appear with the format : "DD/MM/YYYY",  but I want to INSERT INTO a table, I have to write in the format MM/DD/YYYY, if not I receive this error :
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

View Replies !   View Related
How To Insert The Characeter ' In Table
 Hai I want to insert a value into a sqlserver 200 table the value is 10'/3.5m I tried this query insert into GateBoatLengthMaster (Id,lengthValue) values('10','10'/3.5m') but return error due to the character 'Thanks in advanceAnu Palavila

View Replies !   View Related
Error In Insert To Sql Table
hi all when i try to insert a record to sql table i have this error
ncorrect syntax near 'japan'.
the code part iscmdInsert = New SqlCommand("insert into empbill values(" & Val(eno.Text) & ","  & contry1.Text , db)
the value i entered is 123.japan
i have 2 field in  empbill (emp,cont)
what happend

View Replies !   View Related
How To Insert Into A Table Using A Select From Xml
Good Day,
I am passing some XML into a stored procedure:
<answers>  <answer id="60" text="" />   <answer id="65" text="A moderate form of learning disability" />   <answer id="68" text="We will keep ASD checked" />   <answer id="70" text="" /> </answers>
Along with a memberid and questionid.
I was wondering how I can get this into a table CREATE TABLE [dbo].[Answers](
[PrimaryKeyID] [int] NOT NULL,
[MemberID] [int] NOT NULL,
[QuestionID] [int] NOT NULL,
[AnswerID] [int] NOT NULL,
[FreText] [varchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]What I would also like to do is if the text attribute is empty then put a NULL in the FreText field.I think I am looking for  Insert into MyTable (
'somexpathfortext' -- if empty then NULL
@MyXML )  Any ideas - places to to look - thoughts AprreciatedKal

View Replies !   View Related

Copyright © 2005-08, All rights reserved