May 12, 2011 at 9:21 am
Hi:
I am trying to insert data in to a database. Most is static, but some is data driven. I cannot find anything about a for each loop, but surely there must be a way.
Thanks in advance, for helping me. I understand .NET well, but am trying to do more with stored procedures.
DECLARE @PN NVARCHAR
i need a for each or some equivalent here...
SET @PN = (Select RIGHT('000000' + convert(varchar(6), PartNo), 6) FROM dbo.Purchased)
INSERT INTO dbo.Purchasing(PartNumber, IsHardware, KitAlways, ChinaPN, KitChina, InstallTime, LaborClass, UnitCost)
VALUES
(
@PN,
0,
0,
@PN,
0,
10,
1,
null
)
May 12, 2011 at 9:28 am
you do not need the variable
INSERT INTO dbo.Purchasing(PartNumber, IsHardware, KitAlways, ChinaPN, KitChina, InstallTime, LaborClass, UnitCost)
select
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
0,
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
10,
1,
null
FROM
dbo.Purchased
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 12, 2011 at 9:34 am
Produces Incorrect Syntax near 0. This is bit data - perhaps I need to use true / false??
INSERT INTO dbo.Purchasing(PartNumber, IsHardware, KitAlways, ChinaPN, KitChina, InstallTime, LaborClass, UnitCost)
SELECT
RIGHT('000000' + convert(varchar(6), PartNo), 6) FROM dbo.Purchased,
0,
0,
RIGHT('000000' + convert(varchar(6), PartNo), 6) FROM dbo.Purchased,
0,
10,
1,
null
FROM
dbo.Purchased
May 12, 2011 at 9:41 am
Nope. SQL's data type is bit, not boolean. 0, 1 or null.
Your code is not the same as what Dan posted. Use the code Dan posted, the modifications you made is what's causing the syntax error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2011 at 9:43 am
Dan's posted code produces:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '0'.
May 12, 2011 at 9:45 am
NOW IT WORKS.
Thank you...
May 12, 2011 at 9:47 am
steve.anderson 7639 (5/12/2011)
Dan's posted code produces:Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '0'.
The code you posted would cause that exact error because of the 2 extra FROM clauses you have.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2011 at 9:51 am
So here's my question...
SELECT
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
0,
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
10,
1,
null
FROM
dbo.Purchased
produces 17,212 records
After running
INSERT INTO dbo.Purchasing(PartNumber, IsHardware, KitAlways, ChinaPN, KitChina, InstallTime, LaborClass, UnitCost)
SELECT
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
0,
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
10,
1,
null
FROM
dbo.Purchased
it ran fine but I only had a few more records. How can I tell this thing to skip duplicate data?
Thanks again for your help on this!
May 12, 2011 at 9:57 am
Define duplicate data.
What's it not doing that you want it to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2011 at 9:58 am
I added TRY/CATCHES around the query, but 0 records were added. I have 17,000+ records in the originating table, and 533 in the resulting table.
May 12, 2011 at 10:01 am
An insert ... select will insert every single row that the select returns, or it will insert 0 (in the case of an error). SQL doesn't selectively insert rows, it won't ignore duplicates unless you tell it to.
Is that destination maybe not a view?
Is there a trigger?
Do you have a rowcount set?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2011 at 10:16 am
None of this
This is 100% of what I am trying to do:
BEGIN TRY
INSERT INTO dbo.Purchasing(PartNumber, IsHardware, KitAlways, ChinaPN, KitChina, InstallTime, LaborClass, UnitCost)
SELECT
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
0,
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
10,
1,
null
FROM
dbo.Purchased
END TRY
BEGIN CATCH
END CATCH
Without the try/catch, I get this error:
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK__Purchasi__025D30D822751F6C'. Cannot insert duplicate key in object 'dbo.Purchasing'.
The statement has been terminated.
The purchased table contains 17,212 records
The purchasing table contains 533 records
I would like all the records from purchased (that are not duplicates) to be copied over to purchasing, adding the static fileds as shown in the select.
Thanks again!
May 12, 2011 at 10:24 am
Ok, now you're clear...
What's the primary key of the Purchasing table?
p.s. If you're going to use Try Catch, do something with the error, otherwise just leave the error handling out. Catching an error and ignoring it is a terrible practice in any language.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2011 at 10:30 am
PartNumber
May 12, 2011 at 10:40 am
Perhaps this...
INSERT INTO dbo.Purchasing(PartNumber, IsHardware, KitAlways, ChinaPN, KitChina, InstallTime, LaborClass, UnitCost)
SELECT
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
0,
RIGHT('000000' + convert(varchar(6), PartNo), 6),
0,
10,
1,
null
FROM
dbo.Purchased
WHERE RIGHT('000000' + convert(varchar(6), PartNo), 6) NOT IN
(
SELECT PartNumber FROM dbo.Purchasing
)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply