I am trying to automate multiple inserts into multiple tables.
Basically, I have one temp table, with Customer codes and I added a row number. I also have a source tables, which contains the customer codes and personal info. Now I would like to insert alle the Person ID's from customer 1 into #1, all the PersonID's from customer 2 into #2 and so on.
I used this code, but it generates an error:
DECLARE @Customer_Code INT
DECLARE @Row INT = 1
DECLARE @SQL NVARCHAR (MAX)
WHILE @Row = SELECT MAX(Rij) FROM #M
SELECT @Customer_Code = Customer_Code FROM #M WHERE RowNr = @Row
SET @SQL =
WHERE YEAR (Date) = 2016
AND Customer_Code = @Customer_Code'
SET @Row = @Row + 1
SET @SQL = ''
1, 8 ,
1, 9 ,
2, 9 ,
3, 8 ,
4, 8 ,
You are using
WHILE @Row. This is not a boolean expression, you are probably looking for something like
WHILE @ROW <= SELECT MAX(Rij) FROM #M.
Furthermore, you are executing the
SELECT...INTO multiple times. The second time it gets executed, the table
#@Customer_Code already exists, so you can't do
SELECT...INTO but have to use
INSERT instead. It's best to define the table before hand. edit: I suppose you are trying to make a temporary table for every
Customer_Code, in which case you should append the variable correctly. The same goes for
AND Customer_Code = @Customer_Code', the variable is appended as the literal string, not as the value it holds.
The query that gets executed for every row in
SELECT PersonID INTO #@Customer_Code FROM T_Sourcetable WHERE YEAR (Date) = 2016 AND Customer_Code = @Customer_Code
This is obviously not your intention. What you need is:
'SELECT PersonID INTO #'+ @Customer_Code + 'FROM T_Sourcetable WHERE YEAR (Date) = 2016 AND Customer_Code = '+ @Customer_Code
In future, if you receive an error message, the least you can do is tell us what the error message actually says!