April 6, 2005 at 11:25 am
What I'm trying to do is Insert into a TestTableA some Rows from another table "TestTableB" that do not exist in the first table, I could do it one by one but on a bigger table this would be tedious at least. So i'm trying to build a querry that will do a bulk INSERT base on Rows that don't exists in TestTableA.
These are what my little test tables look like:
TestTableA
| CustName | Price1 | Price2 | Price3 |
| Joe | 50 | NULL | NULL |
| Betty | 60 | NULL | NULL |
| Michael | 30 | NULL | NULL |
TestTableB
| CustName | Price |
| Joe | 40 |
| Betty | 20 |
| Michael | 100 |
| Brian | 20 |
| Tiffanie | 50 |
| Amanda | 10 |
Trying to INSERT "Just" Brian, Tiffanie, and Amanda in one Bulk statement INTO TestTableA.
Here are some querries that I've already tried with no such luck.
--This Don't Work and get error message: Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'TestTableA' does not match with a table name or alias name used in the query.
INSERT INTO TestTableA
(CustName, Price2)
SELECT CustName, Price
FROM TestTableB
WHERE TestTableA.CustName <> TestTableB.CustName
--This Don't Work
INSERT INTO TestTableA
(CustName, Price2)
SELECT CustName, Price
FROM TestTableB
WHERE NOT EXISTS (SELECT CustName FROM TestTableA)
--And This Don't Work
INSERT INTO TestTableA
(CustName, Price2)
SELECT B.CustName, B.Price
FROM TestTableB B
LEFT OUTER JOIN TestTableA A
ON B.CustName = A.CustName
WHERE NOT EXISTS (SELECT CustName FROM TestTableA)
Any Help would be much appreciated, thanks.
April 6, 2005 at 11:30 am
This is the one that is the closest :
INSERT INTO TestTableA
(CustName, Price2)
SELECT CustName, Price
FROM TestTableB B
WHERE NOT EXISTS (SELECT CustName FROM TestTableA A where A.CustName = B.CustName)
you could also do a left join in the select part by joining on B.Custname = A.Custname and A.Custname is null
April 6, 2005 at 1:14 pm
Great thanks, that worked perfectly. So I put it together with this query and it updates my tables just the way I like it:
BEGIN
UPDATE TestTableA
SET TestTableA.Price2 = TestTableB.Price
FROM TestTableB
WHERE TestTableA.CustName = TestTableB.CustName
END
BEGIN
INSERT INTO TestTableA
(CustName, Price2)
SELECT CustName, Price
FROM TestTableB B
WHERE NOT EXISTS (SELECT CustName FROM TestTableA A WHERE A.CustName = B.CustName)
END
Now, to make this process run even smoother, I tried to stick this into a Stored Procedure and tried to make the Price2 Column on TestTableA a parameter so that when I run the sproc I could set the parameter to any Price Column I want in TestTableA, Price3 for example. This turned out to be alittle harder then expected, this is what my sproc looks like right now:
CREATE PROC spTestTable
@Price money
AS
BEGIN
UPDATE TestTableA
SET @Price = TestTableB.Price
FROM TestTableB
WHERE TestTableA.CustName = TestTableB.CustName
INSERT INTO TestTableA
(CustName, @Price) <-- Dosen't Like this, had to change back to Price2
SELECT CustName, Price
FROM TestTableB B
WHERE NOT EXISTS (SELECT CustName FROM TestTableA A WHERE A.CustName = B.CustName)
END
Then I tried EXEC the SPOC saying @Price = TestTableA.Price2 and Fails
Tried modifying the EXEC process alitte by writting it like this:
DECLARE @PriceColumn money
SELECT @PriceColumn = (SELECT Price2 FROM TestTableA)
EXEC spTestTable
@Price = @PriceColumn
Which Runs the INSERT, but doesn't do the UPDATE right:
Anymore help would be great, I could just not be understanding SQL all that well, but i'm trying dang it, thanks.
April 6, 2005 at 1:17 pm
You would need dynamic sql to do something like this. There's no point in creating of SP like this one if you simply want to do dynamic sql.
April 6, 2005 at 2:30 pm
not sure i'm following you. First I guess i'm going to have to research into dynamic SQL to understand what that is first. And second, this is just a TestTable to see if it works. I already applied the update & insert on to the real table and works fine. The reason why I would want to make it a SPROC is because i'm going to have to do this UPDATE & INSERT everymonth with four different tables, would make things a heck of alot faster just to say EXEC <SPROC>
But from what i'm reading is that would be alittle trickier, Have any advice on what else I might use other then a SPROC?
April 6, 2005 at 2:41 pm
INSERT INTO TestTableA
(CustName, @Price)
I assume @Price2 is a dynamic column name. This is forbidden in sql server.
read this on dynamic sql :
April 7, 2005 at 1:23 am
Here is an exampl eusing dynamic SQL:
You can use the proc like
exec dbo.spTestTable -- will use Price2 column
exec dbo.spTestTable 'Price3' -- will use Price3 column ...
create proc dbo.spTestTable
(
@PriceCol sysname = 'Price2'
)
as
begin
set nocount on
declare @sql varchar(1024)
set @sql = 'update dbo.TestTableA '
+ 'set [' + @PriceCol + '] = dbo.TestTableB.Price '
+ 'from dbo.TestTableB '
+ 'where dbo.TestTableA.CustName = dbo.TestTableB.CustName'
exec (@SQL)
set @sql = 'insert into dbo.TestTableA '
+ '(CustName, [' + @PriceCol+ ']) '
+ 'select CustName, Price '
+ 'from dbo.TestTableB B '
+ 'where not exists (select CustName from dbo.TestTableA A where A.CustName = B.CustName)'
exec (@SQL)
end
go
April 7, 2005 at 1:25 am
You almost had it:
INSERT INTO TestTableA(CustName, Price2)
SELECT B.CustName, B.Price
FROM TestTableB B
LEFT JOIN TestTable A ON B.CustName = A.CustName
WHERE A.CustName IS NULL
To Use a column name variable:
DECLARE @PriceCol varchar(50), @ExecStr varchar(8000)
SET @PriceCol='Price3'
SET @ExecStr='INSERT INTO TestTableA(CustName, '+@PriceCol+') '
SET @ExecStr=@ExecStr+'SELECT B.CustName, B.Price '
SET @ExecStr=@ExecStr+'FROM TestTableB B '
SET @ExecStr=@ExecStr+'LEFT JOIN TestTable A ON B.CustName = A.CustName '
SET @ExecStr=@ExecStr+'WHERE A.CustName IS NULL '
EXEC @ExecStr
Andy
April 7, 2005 at 6:56 am
Please make sure you read my link before going down that path. You must make sure nobody besides you can access this proc (or at least only trusted developpers).
April 7, 2005 at 9:08 am
wow, looks like i'm going to study this programming for alittle while before I use it. More complex then what i've delt with so far.
Yep, I read that article Remi, thanks for sending that to me but i'm just going to be useing this sproc for my own procedures and if I'm going run it from a web site it would be internal and password protected most likely
Thanks alot for all your help
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply