Insert To Two Tables With Select
Oct 30, 2013
I have those 3 tables (sales,source, sales, sales_details),
Sales_source (table)
id, name
1 Office
2 Post
3 Web
Sales (table)
id, saleid, customer, source, date
01, 230, bill din, 1, 2013-10-22
Sales_details (table)
id, saleid, object, delivery, date
01, 230, card, no, 2013-10-23
02, 230, box, yes, 2013-10-24
03, 230, car, no, 2013-10-31
And I want to create if is possible one insert command
And make the user complete the details of Sales_details (that they have inner join for the sales_source table so he can see only the sales_source_name and behind the sales_source_id to be stored)
And after to be able to add in as many [sale_details] he need for the select sale…
Is this possible to be done with one command ???
I think is not possible as i have made a small research on the web, and i will need to make one insert for the sales (table) independent and after to programming i guess a way to open the sale_details and add the rest….
View 3 Replies
ADVERTISEMENT
Apr 7, 2008
Hi,
I have a 'charges' table that records charges for an invoice. There are several different types of charges, each with its own unique set of additional data fields that need to be recorded.
I maintain separate tables for each charge type and these tables participate in an "ISA" relationship with the main charges table.
Here is a simplified version of my schema. Hourly charges are one type of charge:
charges table
=============
id int (autoincremented primary key)
date datetime
amount money
hourly_charges table
====================
charge_id int (primary key, also a foreign key to charges table)
start_time datetime
end_time datetime
I need to write a query that will duplicate all charges meeting a certain criteria by inserting new records into both the charges table and the hourly_charges table.
Here is some non-working pseudo-code that hopefully will get across what I would like to accomplish:
INSERT INTO charges JOIN hourly_charges
(
charges.date,
charges.amount,
hourly_charges.charge_id,
hourly_charges.start_time,
hourly_charges.end_time
)
SELECT
date,
amount,
SCOPE_IDENTITY(),
start_time,
end_time
FROM charges
JOIN hourly_charges
ON charges.id = hourly_charges.charge_id
WHERE some condition is true
Now I realize this code is invalid and I'll have to go about this an entirely different way but I'm wondering if someone can tell me what the proper way is.
Thanks,
Adam Soltys
http://adamsoltys.com/
View 3 Replies
View Related
Feb 18, 2004
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?
Any ideas?
Thanks in advance.
View 6 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Apr 22, 2004
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
View 2 Replies
View Related
Dec 4, 2007
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
View 7 Replies
View Related
Aug 15, 2006
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
View 3 Replies
View Related
Mar 18, 2007
I currently insert into one table with: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable (UserName, Password, Email) VALUES (@person, @pass, @email)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text);but I realized that there's another table related to this table and I need to have something go in it so that the users data will be recorded at the same pace. So I tried: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable, FatherHistTable (UserName, Password, Email), (Father) VALUES (@person, @pass, @email), (@father)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text); comm.Parameters.AddWithValue("@father", fthrsNmeLbl.Text);Not working, so I am thinking I must do two inserts: SqlCommand comm = new SqlCommand("INSERT INTO
UsersTable (UserName, Password, Email) VALUES (@person, @pass,
@email)", sqlConnection);
comm.Parameters.AddWithValue("@person", usrnmeLbl.Text);
comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text);
comm.Parameters.AddWithValue("@email", hemailLbl.Text); SqlCommand comm2 = new SqlCommand("INSERT INTO
FatherHistTable (Father) VALUES (@father)", sqlConnection);
comm2.Parameters.AddWithValue("@father", fthrsNmeLbl.Text); Is that the only way to go about it then? Thanks in advance for any explanations.
View 26 Replies
View Related
Jul 20, 2005
I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?
View 2 Replies
View Related
Mar 12, 2007
Here is the situation i am stuck with, see the example first and below explained the problem:
-- 'SESSION A
create table foo (
id integer,
pid integer,
data varchar(10)
);
begin transaction
insert into foo values ( 1, 1, 'foo' )
insert into foo values ( 2, 1, 'bar' )
insert into foo values ( 3, 1, 'bozo' )
insert into foo values ( 4, 2, 'snafu' )
insert into foo values ( 5, 2, 'rimrom' )
insert into foo values ( 6, 2, 'blark' )
insert into foo values ( 7, 3, 'smeg' )
commit transaction
create index foo_id_idx on foo ( id )
create index foo_pid_idx on foo ( pid )
begin transaction
insert into foo values ( 9, 3, 'blamo' )
-- 'SESSION B
begin transaction
select id, data from foo with ( updlock, rowlock ) where id = 5;
-- Problem:
-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.
-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?
Appreciate your help,
Rajesh.
View 5 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
Mar 15, 2008
hi need help how to do this
INSERT INTO SELECT FROM table + add one row for evry employee (on insert)
this is table tb_test1
empid fname unit fld1 fld2 fld3 fld4 fld5
----------------------------------------------------------------------------------------
111 aaa 1 a b c d d
222 bbb 3 a c e g g
333 cccc 5 s h t u j
Code Snippet
INSERT INTO [nili].[dbo].[tb_test2]
([empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5])
SELECT [empid]
,[fname]
,[unit]
,[fld1]
,[fld2]
,[fld4]
,[fld5]
FROM [nili].[dbo].[tb_test1]
i need to insert into tb_test2
and ADD one row for evry employee
val_orginal=1 (the orginal ROW)
val_orginal=2 (the extra ROW)
this is table tb_test2
after the insert
HOW TO THIS
empid fname unit fld1 fld2 fld3 fld4 fld5 val_orginal
-------------------------------------------------------------------------------------------------
111 aaa 1 a b c d d 1
111 aaa 1 - - - - - 2
222 bbb 3 a c e g g 1
222 bbb 3 - - - - - 2
333 cccc 5 s h t u j 1
333 cccc 5 - - - - - 2
and tnx for the help
View 12 Replies
View Related
Dec 19, 2006
Please i need to insert two tables at a time. One is Delivaries and the other is DelivaryDetails. This is the Structure:
DelivariesDelivaryId...........Int Primary Key IdentityDelivaryNo.........nvarcharDelivaryDate......datetime
DelivaryDetailsDelivaryId............Int Primary Key Foreign KeyProductId............Int Foreign KeyQty....................Int
I have created a StoredProcedure named InsertDelivaries
CREATE PROCEDURE InsertDelivaries@DelivaryId int output,@DelivaryNo nvarchar(20),@DelivaryDate datetime
AS
insert into Delivaries values(@DelivaryNo,@DelivaryDate)
but this does not insert the records as expected. Please help
View 5 Replies
View Related
Apr 10, 2007
hello..
I think this is common question, but i still dont get answer from all past topic and thread.
My problems is:
I have two tables,
1) company_info (com_ID(Primary key with increment int), name, add, contact_no)
2) company_detail(com_ID(Primarykey without increment), product, quantity)
I want capture the data com_ID from 1st table and at the same time I want to insert into 2nd tables. How the way??
currently I do in table adapter in data set.
I also looking how to use store procedures? my knowledge about store proceduree is zero and really need articles or tutorial from any website. anylink?
One more things, I also which part i should do the relationship, in dataset or at database diagram?
I really lack in this database knowledge
Thank you
View 3 Replies
View Related
Dec 18, 2007
hello my friends
i have a database with 5 tables : 1-Company 2-Activity 3-Product 4-Project 5-Telephone
each table have a one to many relation with Company Table ! i mean a Company can has many Activities,Products,Projects,Telephones i want to insert in tables at a same time insert into Company table ! i know that i should first insert into company Table and get CompanyID that need to insert in other tables. i hear that i can do this with define a VIEW of joining these tables and use trigger!i dont know how !?
I'll appreciate you to help me :O)
View 4 Replies
View Related
Feb 22, 2008
I am using Framework 3.5 with VS2008 writing VB code to put and get data in a SQL 2005 Server database.
How to fill out a form, and on one submit button click, send some information into one database table and different information into a different table?
Those tables of mine have a common key, but one is about where and when and what work needs to be done and the other is about authorization, departments and funds.
I’d just think that I should be able to do that in one InsertCommand="INSERT INTO …� statement and not have to code a whole new <asp:SqlDataSource ID="SqldsDOIT" runat="server" interface for the second table … which is the work-around that I can do … it just seems ignorant to me, when we are told to keep like-things in separate tables (normalization).
In other words, the following fails to run ... if I only INSERT INTO Job( or only INSERT INTO Signatory( ... it's all good.A little help please. I've scanned through the forums, but not found exactly this addressed. <asp:SqlDataSource ID="SqldsDOIT" runat="server" ConnectionString="<%$ ConnectionStrings:DOITWorkOrderConnectionString %>" SelectCommand="SELECT FROM [Job] JOIN [Signatory] ON Job.Jobnumber = Signatory.Jobnumber" InsertCommand="INSERT INTO Job(Datereq, ..., Jobcomm) VALUES(@Datereq, ..., @Jobcomm) INTO Signatory(BillProgram, ..., Sigemail) Values(@BillProgram, ..., @Sigemail)"> <InsertParameters>
View 4 Replies
View Related
Sep 5, 2001
I want to insert records from two tables into one table as the record is concatenated from both the tables and then put it into the one table.
How can we do that so that i can take the records from the one table..?
CREATE PROCEDURE SP_P31
AS
select 'CA'+'|'+COMPANY+'|'+COMPANYNAME+'|'+CUSTOMERNUMBE R+'|'+COMPANYPHONE
+'|'+COMPANYFAX+'|'+FEDTAXID+'|'+DUNSNUMBER+'|'+S1 099+'|'+DUNSSUFFIX+'|'+convert(char,ADDRESSSEQ)
+'|'+COUNTRY+'|'+ADDRESSTYPE+'|'+ADDRESS1+'|'+ADDR ESS2+'|'+ADDRESS3+'|'+ADDRESS4
+'|'+CITY+'|'+STATE+'|'+ZIP+'|'+FAXNUMBER hello1 into temp32
from v4
select 'CT'+'|'+COMPANY+'|'+CONTACTNAME+'|'+CONTACTTYPE+' |'+CONVERT(CHAR(10),CONTACTADDRESS)
+'|'+PHONE+'|'+FAX+'|'+TITLE hello INTO temp32
from v3
View 3 Replies
View Related
Mar 8, 1999
Does SQL allow you to insert data into 2 related tables simultaneously?
I have a form where the user enters in Name, Age, SocialSecurity,
Address. The Address table is a separate table than the Individual
table. How do I insert into both ??? Both tables are tied with a unique Individual_ID which is system generated.
I am using a stored procedure
Thanks alot!!
Joyce
View 1 Replies
View Related
Oct 19, 2007
I posted this question previously but am now getting another error and fixing it (months ago). I am trying insert The ID2 column (pk) from table 2 into ID2 column (fk) of table 1. What the T-SQL is trying to do is check to see if the record in table 2 already exists in then just pass the value to the insert query for table 1. If the record does not exist then insert data into table 2first then perform the table 1 insert.
This SP works fine if the user does not exist in table 2. However, if the user does exist in table2 then instead of passing the ID, a null value is passed.
Can anyone tell me why the first query does not work? If there is an easier way of doing this then I am all ears.
Thank you
DECLARE @IdentityHolder int
BEGIN TRANSACTION
IF EXISTS (SELECT ID2 FROM [tbl2] WHERE ID2 = @ID2)
BEGIN
(SELECT ID2 FROM [tbl2] WHERE ID2 = @ID2)
END
ELSE
BEGIN
INSERT INTO [tbl2] ([ID2], [FN], [LN], emailAdd])
VALUES ( @ID2, @fName, @lName, @emailAdd)
END
COMMIT
SET @IdentityHolder = (ID2)
INSERT INTO [tbl1]([ID2], [event])
VALUES (@ID2, @event)
View 14 Replies
View Related
Dec 12, 2007
Hi i have a stored prodedure which inserts into a local table then inserts into a remote table this works fine, what i need to do is.
INSERT ONTO TABLE A
THEN GET THE MAX(ID) OF THE RECORD INSERTED
THEN INSERT THAT MAX(ID) INTO TABLE B
i have created a parameter called @WebID but how do i give it the value of the MAX(ID) before i insert into tableB
Many thanks
View 2 Replies
View Related
May 18, 2008
Good evening every body.
I have 3 tables ( customer, product and order).
Customer table contain customer_id as a primary key
Product table contain product_id as a primary key
Order table contain both customer_id and product_id
I want to insert the values from customer and product to order.
Any help?
View 7 Replies
View Related
Jul 7, 2006
Hallo, I am trying to insert into a table HFacility a FacilityID, HotelID FROM Facility. Using Select.Both HFacility and Facility have columns FacilityID, HotelID with same DataType.Insert INTO HFacility
Select FacilityID, HotelID
FROM FacilityI am getting this error:Server: Msg 213, Level 16, State 4, Line 1Insert Error: Column name or number of supplied values does not match table definition.Thank you for your help
View 4 Replies
View Related
Nov 28, 2007
Hi guys,I need help with my query for my web application. The situation is, once i select an item in the dropdownlist and click generate, it will populate the gridview with the query results. Now, what i wanted to do is get all the Document Owner ID results and save it into another table. How will i able to get all the Document ID result and store it in another table? Thanks in advance.
View 2 Replies
View Related
Apr 5, 2001
Hello all,
I'm trying to copy the contence from one table to another with the following statement:
insert tmembers
select cardno,voornaam,achternaam,straat,nummer,toevoegin g, postcode,woonplaats,regio,land,geboortedatum,gesla cht,leeftijd,telefoon,mobiel,homepage,email,id_bew ijs,id_nummer,uitgiftedatum,expiratiedatum,delict, hobbies,soort_bezoeker,muziek_voorkeur,mailing,ema il_lijst,reactie,notitie,haarkleur,oogkleur,kenmer ken,fingerprint,faceprint,cam_pos from members
I get the following error on execution
Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated. The statement has been terminated.
Can anyone tell me what this means or suggest a better way on how to copy the contence between two tables.
Kind regards,
Rob
View 1 Replies
View Related
Jun 29, 2000
Hi,
Does anybody know how to query a table to get the distinct rows, and insert
these rows into another table in another database
Thanks in advance
View 3 Replies
View Related
Aug 15, 2000
Which is more efficient SELECT INTO or INSERT INTO?
Thanks
View 1 Replies
View Related
Sep 15, 1999
I am attempting to use a SQL statement that I shameless stole from "Using Microsoft SQL Server 6.5 Second Edition" and am failing miserably. I want to use the statement
INSERT INTO CallEntries
(CallNum, Entry)
VALUES (SELECT CallNum, Notes
FROM CallsJunk)
and am getting 2 errors. The first is a syntax error near the keyword 'SELECT', and the second is a syntax error near ')'
These are both tables in the same database. Anyone know what I'm screwing up here?
The example I was given is
Insert into addresses
View 3 Replies
View Related
Mar 11, 2002
what's the difference between SELECT INTO and INSERT INTO.
because when I insert value into a temp table using this two function, Select into runa 3 times faster compare to insert into.
does anyone know what's the reason for this.
Thank You,
John
View 1 Replies
View Related
Jul 20, 2007
Hi,
I'm trying to use a select statement to retrieve a value and then use this value in an insert. I've written the entire code inside a stored proc. Darn thing refuses to work. Please help
Here is a call to this stored proc
Set_NewUserName 'mm', 'mm', 'student', 'email@em.com', 'mm', 'mm', 'mm', 'add1', 'add2', '600041', 'hybad', '93805', 'city', 'chennai', '13/10/1972', '6'
the code above sends third param 'student' to retrieve UserType_ID from tblUserType table. Also the last parameter is Class ID in the insert statement, for which I am passing the class value which will in turn retrieve the class ID from the tblClass table.
STORED PROCEDURE CODE BELOW HERE
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Set_NewUserName]
-- Following parameters will be sent to this proc by a webpage
-- We need to use the value in UserType_Name to identify if this is a
-- teacher or a student and insert into tblUser the appropriate value
-- similarly for class Id, retrive from class table using the value user
-- selects from drop down
@Login_IDnvarchar(20),@Password nvarchar(30),@UserType_Name nvarchar(30),
@Email nvarchar(30),@FirstName nvarchar(10),@LastName nvarchar(10),
@FullName nvarchar(20),
@add1 nvarchar(10),@add2 nvarchar(10),@pin nvarchar(10),
@city nvarchar(10),@phone nvarchar(10),@HintQuestion nvarchar(MAX),
@HintAnswer nvarchar(50),@DOB nvarchar(10),@Class_Name nvarchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Class_IDuniqueidentifier
Declare @UserType_IDuniqueidentifier
-- retrieve the class id first from the tblClass Table
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
IF NOT EXISTS
(select Class_ID from tblClass where Class_Name = @Class_Name)
RAISERROR('select Class_ID from tblClass where Class_Name',11,1)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('You must provide a valid Class ID',11,1)
END
-- retrieve the usertype id first from the UserType Table
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
IF NOT EXISTS
(select UserType_ID from tblUserType where UserType_Name = 'student')
RAISERROR('select UserType_ID from tblUserType where UserType_Name',11,1)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('You must provide a valid Status ID',11,1)
RETURN
END
-- At last the Insert statements for the procedure
-- is created below here
insert into tblUser
values (
newid(),
@Login_ID,@Password,
@UserType_ID,
@Email,
@FirstName, @LastName, @FullName,
@add1, @add2,@pin, @city,
@phone,
'True',
@HintQuestion,@HintAnswer,
@DOB,
@Class_ID
)
END
View 4 Replies
View Related
Jan 4, 2005
can somebody tell me the difference between select into and insert into statements in sql server.
for example: How
SELECT LastName,FirstName INTO Student_bk FROM Student
is different from
INSERT INTO Student_bk(LastName,FirstName) Select (LastName,FirstName) from Student
Thanks.
View 4 Replies
View Related
Oct 19, 2004
I'm a beginner and I've been searching most of the day for the right syntax.
Table UserData has 3 fields
UserID TypeID SomeNullField
I want to insert into TypeID the value 207 if it doesn't already exits.
=============================================
I thought this would work, but doesn't
INSERT INTO UserData
(TypeID)
VALUES ( 207)
SELECT UserID
FROM UserData
WHERE (NOT exists(SELECT FROM UserData WHERE UserData.TypeID = 207));
++++++++++++++++++++++++++++++++++++++++++++++
Am I even close???? Like I said I am new at this game. Appreciate any help anyone can reach down and lend.
View 10 Replies
View Related
Apr 17, 2007
Quick question....
I have an insert into query that uses a select to get the valuse form other tbls. however on of the column in the tbl i am inseting into requers a values so i can not just leave it out. however I cant get that value for another tbl ither so long and short of it I need to know how to us the insert into with the select and also beable to set that odd column to 0.
This is what I have so far:
Code:
INSERT INTO CheckDetail(FK,Leaseid,BU,LOC,DuplicateVerification)
Select distinct t1.ID, t2.site_id,t3.CompanyCode,t3.CostCenter
From PMTK_tbl as t1, Leaseinfo as t2
left join CostCenters as t3
on t2.market = t3.market and t2.market_region = t3.RegionCode
where t2.site_id = '9SA0998A'
order by t1.ID ASC
The DuplicationVerification needs to be set to 0.. How do I do that
View 2 Replies
View Related
