SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Store procedure insert into function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eddietwal
Starting Member

5 Posts

Posted - 08/10/2014 :  01:26:32  Show Profile  Reply with Quote
I have 2 tables

First Table is CUSTOMERS with the columns (CustomerId, CustomerName) Second Table is LICENSES with the columns (LicenseId, Customer) The column customer from the second table is the CustomerId from the First table

I wanted to create a store procedure that insert values into table 2

Insert into Licenses (Customer) Values( Same As CustomerId)
HOW i can get this data from the other table?
Thanks For the Help in Advance

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/10/2014 :  08:03:15  Show Profile  Reply with Quote
Use a select clause
Go to Top of Page

eddietwal
Starting Member

5 Posts

Posted - 08/10/2014 :  11:31:46  Show Profile  Reply with Quote
I don't think thats what I need here.
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/10/2014 :  18:00:26  Show Profile  Reply with Quote
Hi Eddie,

Maybe


--*******************
-- CREATE TEMP TABLES
--*******************
CREATE TABLE #CUSTOMERS (CUSTOMERID INT, CUSTOMERNAME VARCHAR (100))
CREATE TABLE #LICENSES (LICENSEID varchar(100), CUSTOMER VARCHAR(100))

--******************
--test data for temp tables
--******************
INSERT INTO #CUSTOMERS (CUSTOMERID, CUSTOMERNAME)
VALUES (1,'EDDIE')
INSERT INTO #LICENSES (LICENSEID, CUSTOMER)
VALUES (1,'WAL')

-------------
--data
-------------
#licenses
----------------------
LICENSEID CUSTOMER
1 WAL

#customers
-----------------------------
CUSTOMERID CUSTOMERNAME
1 EDDIE


--*******************
-- UPDATE statement
--*******************
UPDATE #LICENSES
SET LICENSEID = (SELECT CUSTOMERNAME FROM #CUSTOMERS)


----------------------
--SELECT * FROM #LICENSES
---------------
LICENSEID CUSTOMER
EDDIE WAL



Thanks,
Chris
Jr Programmer
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/10/2014 :  18:05:05  Show Profile  Reply with Quote
You could add a JOIN also depending on your needs

UPDATE #LICENSES
SET LICENSEID = (SELECT CUSTOMERNAME FROM #CUSTOMERS AS C JOIN #LICENSES AS L ON L.LICENSEID = C.CUSTOMERID )

Thanks,
Chris
Jr Programmer
Go to Top of Page

b8538230
Starting Member

16 Posts

Posted - 08/10/2014 :  18:38:06  Show Profile  Reply with Quote
quote:
Originally posted by eddietwal

I don't think thats what I need here.



e.g.

insert into sometable(col1, col2, col2)
select col1, col2, col3
from someothertable

Go to Top of Page

eddietwal
Starting Member

5 Posts

Posted - 08/10/2014 :  21:03:54  Show Profile  Reply with Quote
Chris,
nice idea but it only works one time because the second time you run the sp you get an error (Subquery returned more than 1 value. )

Edited by - eddietwal on 08/10/2014 21:07:20
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/10/2014 :  21:11:43  Show Profile  Reply with Quote
Eddie,

Sorry about that

Thanks,
Chris
Jr Programmer

Edited by - Chris_Kelley on 08/11/2014 09:25:07
Go to Top of Page

eddietwal
Starting Member

5 Posts

Posted - 08/10/2014 :  21:27:47  Show Profile  Reply with Quote
I'm sure its going to be a simple solution. I will find out when I get to work tomorrow morning. I will ask one of the seniors. I will post back the solution later on tomorrow.
Go to Top of Page

Chris_Kelley
Posting Yak Master

106 Posts

Posted - 08/10/2014 :  22:08:46  Show Profile  Reply with Quote
GOT IT --

----------------------
--TEST DATA
-----------------------
INSERT INTO #CUSTOMERS (CUSTOMERID, CUSTOMERNAME)
VALUES (1,'EDDIE')
INSERT INTO #CUSTOMERS (CUSTOMERID, CUSTOMERNAME)
VALUES (2,'JOHN')
INSERT INTO #LICENSES (LICENSEID, CUSTOMER)
VALUES (1,'WAL')
INSERT INTO #LICENSES (LICENSEID, CUSTOMER)
VALUES (2,'WAYNE')

-------------------------------
--SELECT * FROM #LICENSES

LICENSEID CUSTOMER
1 WAL
2 WAYNE

-------------------------------
SELECT * FROM #CUSTOMERS

CUSTOMERID CUSTOMERNAME
1 EDDIE
2 JOHN


------------------------------------
--MAGIC

BEGIN TRAN
UPDATE #LICENSES
SET LICENSEID = CUSTOMERNAME
FROM #LICENSES INNER JOIN #CUSTOMERS ON #LICENSES.LICENSEID = #CUSTOMERS.CUSTOMERID

-------------------------------
--SELECT * FROM #LICENSES

LICENSEID CUSTOMER
EDDIE WAL
JOHN WAYNE



Thanks,
Chris
Jr Programmer
Go to Top of Page

eddietwal
Starting Member

5 Posts

Posted - 08/10/2014 :  23:35:26  Show Profile  Reply with Quote
I will try this now. By the way Customer
from the Licenses table is the CustomerId from the Customers table just FYI

Meaning that you can't insert any values in the customer field. It takes its data from the cutomerId column

INSERT INTO #LICENSES (LICENSEID, CUSTOMER)
VALUES (1,'WAL') you can't fill the customer column it takes it from the other table.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/11/2014 :  07:40:17  Show Profile  Reply with Quote
quote:
Originally posted by eddietwal

I will try this now. By the way Customer
from the Licenses table is the CustomerId from the Customers table just FYI

Meaning that you can't insert any values in the customer field. It takes its data from the cutomerId column




but Licenses.Customer is a varchar and Customer.CustomerId is an int. How can they be the same?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000