Passing variable into SQL query

Help Activities
, ,
1

Hello,

I’m trying to create a Calendar Table in SQL to fill dates gaps. I’m basically trying to build a table for 108 zip codes for a specific date range.

Current workflow:

  1. Database connect

  2. Execute Query - Create a distinct list of 108 zip codes and store in Data table
    variable.

dt_Zip

  1. For Each row in Data Table dt_Zip

       Execute SQL Query - the query creates the date range with Zip code 
   variable

**dt_Out**

  2. Insert dt_Out data table variable into SQL table.

I’m struggling to set the @Zip variable in SQL query from the dt_Zip

Set @Zip = row(““Zipcode””).ToString

I keep getting SQL compile errors with sting needs double quotes.

Any help would be greatly appreciated.

Thanks,

Tim

2

The proper SQL query would be…

INSERT INTO [tablename]([columnname1],[columname2]) VALUES ('column1value','column2value')

So if you want to replace one of those with a variable…

"INSERT INTO [tablename]([columnname1],[columname2]) VALUES ('" + yourVariable + "','column2value')

The problem is you’re using double quotes incorrectly for SQL.

3

You could also just use…

4

Hi Paul,

I’m struggling to set the variable not the insert.

The variable is being set during a For Each Row of Data table loop. For Each Row in dt_zips

I thought the row function would set the variable from dt_zips. ZIPCODE is name of column in dt_zips

Set @Zip = ‘" +row(" ZIPCODE").ToString+"’

5

Hi @timothy.mullady ,

Can you try it like this

image
image885×405 6.21 KB

6

Hi still get compile errors

7

Set is for update queries. Why are you trying to set a SQL variable?

8

Hello @timothy.mullady

Try to pass it as a parameter instead

Thanks,
Prankur

9

I’m trying to change the query result for each run in the loop

10

You need to give more detail, this doesn’t really help us understand what your goal is.

11

thanks this worked

1 Like
12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.